newsletter subscription

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Rahul Dev
Forum Newbie
Posts: 18
Joined: Thu Dec 09, 2010 4:54 am

newsletter subscription

Post by Rahul Dev »

hello guys, i need some help for a newsletter subscription. i have 3 tables: subscription(user_id, category_id), article(article_id, category_id, article), and newsletter(user_id, article_id, newsletter). what i want to do is select the article_id from article table where the category_id is the same as in subscription table so that i get all the articles that a user has subscribed and then check whether that article is already in the newsletter table for the user that has subscribed to it. or not. if it is then echo no new update else echo new updates available. what i have been able to do up to now is:

Code: Select all

$query = "SELECT article.article_id, article.category_id, subscription.user_id FROM subscription, article WHERE  subscription.category_id = article.category_id";
$query_result =mysql_query($query);
while ($row = mysql_fetch_array($query_result))
{
        $query2 = "SELECT * FROM newsletter WHERE article_id = '".$row['article_id']."' AND user_id = '".$row['user_id']."'";
	$query_result2 =mysql_query($query2);
	while ($row1 = mysql_fetch_array($query_result2))
	{
              foreach ($row1 as $rows)
		{
			if ($rows['article_id']==$row['article_id'])
			echo "no new update!<br/>";
                         ...
			else if ($rows['article_id']!=$row['article_id'])
                        echo "new updates available!<br/>";
                        ...
                }
        }
}
but i don't get the expected results!
can any1 tell me how i can achieve what i want. i believe this part should be modified:

Code: Select all

foreach ($row1 as $rows)
		{
			if ($rows['article_id']==$row['article_id'])
			echo "no new update!<br/>";
                         ...
			else if ($rows['article_id']!=$row['article_id'])
                        echo "new updates available!<br/>";
                        ...
                }
Last edited by Rahul Dev on Sun Feb 06, 2011 4:31 am, edited 1 time in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: newsletter subscription

Post by social_experiment »

You mention 3 tables (article, subscription, newsletter) but your query is pulling data from another table 'summarization', is that intentional or maybe a typo?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rahul Dev
Forum Newbie
Posts: 18
Joined: Thu Dec 09, 2010 4:54 am

Re: newsletter subscription

Post by Rahul Dev »

social_experiment wrote:You mention 3 tables (article, subscription, newsletter) but your query is pulling data from another table 'summarization', is that intentional or maybe a typo?
sorry it was a typo, its article
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: newsletter subscription

Post by social_experiment »

Try using JOIN

Code: Select all

<?php
 $query = "SELECT article_id FROM article JOIN subscription ON article.category_id = subscription.category_id";
?>
Hth
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Rahul Dev
Forum Newbie
Posts: 18
Joined: Thu Dec 09, 2010 4:54 am

Re: newsletter subscription

Post by Rahul Dev »

social_experiment wrote:Try using JOIN

Code: Select all

<?php
 $query = "SELECT article_id FROM article JOIN subscription ON article.category_id = subscription.category_id";
?>
Hth
i've tried but what i want to do is check whether every article in every category which a user has subscribed is inserted in the table newsletter or no!
e.g step 1: select all articles in articles table for every category that are in subscription.
step 2: for every article in that category check whether there is an entry for that article in newsletter for the category that the user subscribed to.
Post Reply