Problem Making a Top 10 Script

ATLien

Hey everyone, I am trying to make a top 10 script for my site. I am having problems getting it working. I have a database that has a table for ratings. That list each rating someone gives it, then it list the item it goes with. I need to AVG the ratings then I need to get the top 10, and match the rating id number with the id in the items table so it can pull the resat of the info about the item. I am having trouble getting this to work here is what I have.

		<table cellpadding=5 cellspacing=0 width="100%">
		<?PHP
		$querytutrate = "SELECT AVG(rating) as num, item_id as id FROM ratings WHERE item_type='tutorial' ORDER BY AVG(rating) DESC LIMIT 10";
		$tutrate = mysql_query($querytutrate);
		while ($tutrate) {
		$id = $tutrate['id'];
		$querytutinfo = "SELECT * FROM tutorials WHERE tutorial_id='$id'";
		$tutinfo = mysql_fetch_array(mysql_query($querytutinfo));
		$rate = round($tutrate[num],2);
		?>
		<tr>
			<td><a href="index.php?id=<?PHP echo $tutinfo['tutorial_id']; ?>"><?PHP echo $tutinfo['tutorial_title']; ?></a> <?PHP echo $rating; ?></td>
		</tr>
		<?PHP
		}
		?>
		</table>

If anyone can help me get this script working, i would really appreciate it.

melody

i'd say it's better to explicitly pull the data from the table in one go.

try this for a go:

SELECT a.tutorial_id, a.column1, a.column2, avg(b.rating) as average
from tutorials a, ratings b
where a.tutorial_id=b.tutorial_id
group by a.tutorial_id, a.column1, a.column2
order by average desc;

If this works for you, tell me how you managed to create those lovely graphics on your webpages 🙂

ATLien

that did work, here is what I had:

		<?PHP
		$querytut = "SELECT a.tutorial_id, a.tutorial_title, b.item_id, AVG(b.rating) as average FROM tutorials as a, ratings as b";
		$querytut .= "WHERE a.tutorial_id='b.item_id' ORDER BY average desc LIMIT 10";
		$tutinfo = mysql_query($querytut);
		while ($tutinfo) {
		$id = $tutinfo['a.tutorial_id'];
		$rate = round($tutinfo['average'],2);
		?>
		<tr>
			<td><a href="index.php?id=<?PHP echo $tutinfo['a.tutorial_id']; ?>"><?PHP echo $tutinfo['a.tutorial_title']; ?></a> <?PHP echo $rate; ?></td>
		</tr>
		<?PHP
		}
		?>

Anyone know what i am doing wrong?

melody

When you say it doesn't work, how do you mean? Do you get error messages or just no rows returned? I'm assuming it's the SQL you're having the problem with and not the php?

Looking at that last piece of SQL, you might need to use GROUP BY clauses on the "a." columns. Also where you concatenate to add the "WHERE..." you need to include a space so that it's " WHERE...". MY instinct is that it's because you're not grouping by the columns.

Try it out and let me know what error messages it throws up.

HTH
Melody