JJBlaha Posted January 24, 2007 Share Posted January 24, 2007 I have a database that is set up with username, articleid, rating. the rating is a number. i want to list the username with the highest total ratings for all articles with their name and the totaled number. How do I accomplish this? Quote Link to comment Share on other sites More sharing options...
trq Posted January 24, 2007 Share Posted January 24, 2007 [code]SELECT username, SUM(rating) AS total FROM tbl GROUP BY username ORDER BY total ASC LIMIT 1;[/code] Quote Link to comment Share on other sites More sharing options...
JJBlaha Posted January 24, 2007 Author Share Posted January 24, 2007 i get Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in ...[code]$s = "SELECT user, SUM(rating) AS total FROM table GROUP BY user ORDER BY total DESC LIMIT 10";$r = mysql_query($s, $conn);while ($Array = @mysql_fetch_array($r)) { $total = $Array['total']; $rating = $Array['rating']; $user = $Array['user']; echo "$user ($total)<br />";}[/code] Quote Link to comment Share on other sites More sharing options...
trq Posted January 25, 2007 Share Posted January 25, 2007 Surely the name of your table is not [i]table[/i]? If it really is (and that bad practice), then [i]table[/i] is a reserved word in sql. You'll need to surround it with backticks `. eg;[code]$s = "SELECT user, SUM(rating) AS total FROM `table` GROUP BY user ORDER BY total DESC LIMIT 10";[/code]Oh yeah... PS, my original query should have been ordered by DESC as I see you've discovered. :) Quote Link to comment Share on other sites More sharing options...
JJBlaha Posted January 25, 2007 Author Share Posted January 25, 2007 it isnt table, it is articles something else is causing the problem Quote Link to comment Share on other sites More sharing options...
trq Posted January 25, 2007 Share Posted January 25, 2007 Change this...[code=php:0]$r = mysql_query($s, $conn);[/code]to....[code=php:0]$r = mysql_query($s, $conn) or die(mysql_error()."<br />$s");[/code]What do you get? Quote Link to comment Share on other sites More sharing options...
JJBlaha Posted January 25, 2007 Author Share Posted January 25, 2007 Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in ....(path)SELECT user, SUM(rating) AS total FROM articles GROUP BY user ORDER BY total DESC LIMIT 10 Quote Link to comment Share on other sites More sharing options...
trq Posted January 25, 2007 Share Posted January 25, 2007 Where do you define $conn? Your connection is failing. Quote Link to comment Share on other sites More sharing options...
JJBlaha Posted January 25, 2007 Author Share Posted January 25, 2007 i forgot to include my database file! doh! thanks alot, it works perfectly now. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.