Page 1 of 1

newsletter subscription

Posted: Sun Feb 06, 2011 2:45 am
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/>";
                        ...
                }

Re: newsletter subscription

Posted: Sun Feb 06, 2011 4:28 am
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?

Re: newsletter subscription

Posted: Sun Feb 06, 2011 4:32 am
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

Re: newsletter subscription

Posted: Sun Feb 06, 2011 5:27 am
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

Re: newsletter subscription

Posted: Sun Feb 06, 2011 8:21 am
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.