[SOLVED] mysql help/php help

[JJBlaha]

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?

[trq]

[code]
SELECT username, SUM(rating) AS total FROM tbl GROUP BY username ORDER BY total ASC LIMIT 1;
[/code]

[JJBlaha]

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]

[trq]

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. :)

[JJBlaha]

it isnt table, it is articles something else is causing the problem

[trq]

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?

[JJBlaha]

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

[trq]

Where do you define $conn? Your connection is failing.

[JJBlaha]

i forgot to include my database file! doh! thanks alot, it works perfectly now.