Calculating results? 1

[1510982]

I have works in tbl_works with tbl_works.id as primary key. Then I have table named tbl_rating that has tbl_rating.rate and tbl_rating.work_id, that is linked with 1:M with tbl_works.id.

tbl_works.id ----1 : M---- tbl_rating.work
tbl_rating.rate

Is it possible, that I would with one SQL query (I could do this with storing data in array with PHP) output the works sorted DESCending to average rating?

Thank you very much in advance.

Rastko

 

[swampBoogie]

select w.id , avg(rate) as rateAvg
  from tbl_works w join tbl_rating r
  on w.id = r.work
  group by w.id
order by rateAvg desc

 

[1510982]

Dear swampBoogie, thank you for your quick response. However, the exact thing you proposed doesn't work. I've tried to pass it into phpMyAdmin debugger and it says: Not unique table/alias: 'tbl_public_work_ratings'. What could be the problem?

Here's the SQL:

SELECT tbl_submitted_works.id,
tbl_submitted_works.entry_headline,
AVG(tbl_public_work_ratings.rating) AS avg_rating
FROM tbl_submitted_works, tbl_public_work_ratings
JOIN tbl_public_work_ratings ON tbl_submitted_works.id = tbl_public_work_ratings.work_id
GROUP BY tbl_public_work_ratings.work_id
ORDER BY avg_rating DESC

 

[swampBoogie]

SELECT tbl_submitted_works.id,
       tbl_submitted_works.entry_headline,
       AVG(tbl_public_work_ratings.rating) AS avg_rating
FROM tbl_submitted_works 
JOIN tbl_public_work_ratings ON tbl_submitted_works.id = tbl_public_work_ratings.work_id
GROUP BY tbl_public_work_ratings.work_id,
tbl_submitted_works.entry_headline
ORDER BY avg_rating DESC

 

[1510982]

Regards,

one big problem that I cannot solve. When I paste the swampBoogie's SQL (which seems fine) to phpMyAdmin, it outputs what it should - winners by ratings.

But when I store this SQL into $SQL_winners and run following script:

$result = mysql_query ($SQL_winners);
while ($row = mysql_fetch_array ($result)) {
echo $row[1] . &quot; &quot; . $row[2] . &quot;<br>&quot;;
}
mysql_free_result($result);
unset($row);

I get these errors:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/memefest/public_html/admin/xyz/ratings.php on line 89

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/memefest/public_html/admin/xyz/ratings.php on line 101

What creates this differences between phpMyAdmin and ordinary .PHP script, which phpMyAdmin also is. I've also tried to copy regenerated phpMyAdmin SQL to my source and it didn't work neither.

Any help would be greatly appreciated!

Rastko

 

[swampBoogie]

The errors indicates that the call to mysql_query fails. Add some error handling to see what happens.

$result = mysql_query ($SQL_winners)
or die ('Error: ' . mysql_error() . ' when executing query ' .
$SQL_winners);