4 replies to this topic

[webcodez]

Hi there,

I'm currently working on a news database website and Im trying to display the 5 hottest news articles. However, I have 2 tables which are used for this: one containing all news posts (called 'news') and one containing all ratings (called 'rates'). My current query looks like this but as I already expected it did not work:

$get_hot_news  = mysql_query("SELECT * FROM news WHERE id IN("SELECT news_id, AVG(rating) AS average FROM rates GROUP BY news_id ORDER BY average DESC LIMIT ".$conf_vars['amt_posts_on_home']) ");

(it wouldn't work in the first place because the sub-query selects 2 fields while the id of the news post should only, obviously, match with a value of the news_id field only , of the news posts selected in the sub-query)
I hope anyone can help me out on how to get this to work correctly. I'm probbably overseeing some easy way to do this but I just cannot think up a working query for it right now.

Thanks in advanced,
Skyfe.

[WingedPanther]

It's going to be a little messier, because you need either a join, or a subselect. If you do a join, you'll also need a group by clause.

[webcodez]

Hi,

Thanks for your response. I already got somewhat further with the mysql query but I still don't really understand how to select the highes 5 averages for example ( e.g. make a query that selects the news_id and AVG(rating) for each news_id and order it by the average rating to show only the 5 highest ones ). What I got now is:

//trying to select the 5 news_id's with the highest average rating

mysql_query("SELECT news_id FROM (SELECT news_id AS news_id, AVG(rating) AS average FROM rates GROUP BY news_id ORDER BY average DESC LIMIT 5) AS news ")or die(mysql_error());

Any help would be appreciated again :)

Cheers.

[WingedPanther]

You may be able to "order by (SELECT AVG(rating) FROM rates)"

[webcodez]

it seemed multiple solutions worked, I just figured that I should just make one subquery in the FROM clause, which selects all news_id's and corresponding averages, and then in the main query select all news_ids from that subquery and order it by the averages selected in the subquery:

mysql_query("SELECT news_id FROM (SELECT news_id AS news_id, AVG(rating) AS average FROM rates GROUP BY news_id) AS news ORDER BY average DESC LIMIT 5 ")or die(mysql_error()); 

Also when I tested my earlier code it seemed to work just fine as well:

mysql_query("SELECT news_id FROM (SELECT news_id AS news_id, AVG(rating) AS average FROM rates GROUP BY news_id ORDER BY average DESC LIMIT 5) AS news ")or die(mysql_error());  

Thanks for the help, I think I can make it to work now :) I'll just let this be the subquery in the where clause containing all news_ids to select, e.g.:

mysql_query("SELECT * FROM news WHERE news_id IN(SELECT news_id FROM (SELECT news_id AS news_id, AVG(rating) AS average FROM rates GROUP BY news_id) AS news ORDER BY average DESC LIMIT 5 ) ")or die(mysql_error()); 

I guess that should do the job!

Cheers.

EDIT1: when I ran my last query, apperantly my MySql version did not support the LIMIT statement inside an IN-subquery. Not much later I realized I could just put the LIMIT part inside the main query and it would result into the same, so I did:

mysql_query("SELECT * FROM news WHERE id IN(SELECT id FROM (SELECT news_id AS id, AVG(rating) AS average FROM rates GROUP BY id) AS news_rates ORDER BY average DESC) LIMIT 5")or die(mysql_error()); 

EDIT2: it did not give the desired output =/... My redenation in EDIT1 was wrong ><

EDIT3: lol, I got it to work using 2 queries instead of 1 with the 2nd query as sub-query inside the WHERE IN part of the main query. Still wondering why it doens't work when I put that 2nd query as sub-query inside the WHERE-IN part. Now I first executed the sub-query that selects all IDs, and then made 2nd query which selects the news with those ids. But I'm sure this could be done in one query ( also with my MySql version ). But anyway, this is my code now:

	$get_hot_news_ids = mysql_query("SELECT news_id FROM (SELECT news_id AS news_id, AVG(rating) AS average FROM rates GROUP BY news_id) AS news ORDER BY average DESC LIMIT 5 ");

	$first = 1;

	while($news_id     = mysql_fetch_assoc($get_hot_news_ids)) {

		if(!$first) {

			$hot_news_ids .= " ,";

		}else{

			$first = 0;

		}

		$hot_news_ids .= $news_id['news_id'];

	}

	$get_hot_news	= mysql_query("SELECT * FROM news WHERE id IN($hot_news_ids) ORDER BY FIELD(id, $hot_news_ids)");

fixed the order of the selection

Edited by webcodez, 06 March 2011 - 06:11 AM.