sum and sort data help?

array sum sort mysql php

[djsimple007]

so i have data that looks like this:
jim 10
ric 12
jim 4
jon 21

// Im using this code to group and sum:
$query = "SELECT firstname, SUM(points) FROM people GROUP BY firstname";

$result = mysql_query($query) or die(mysql_error());

$newarray = array();
// Print out result
while($row = mysql_fetch_array($result)){
$newarray[] = $row['firstname'].$row['SUM(points)'];
}

What I get is something like:
jim 14
ric 12
jon 21

when what I really want is a sorted list:
jon 21
jim 14
ric 12

but doing a sort doesnt work? Any ideas how else I can tackle the issue?

[dreamcatcher]

Have you tried an order by clause?

$query = "SELECT firstname, SUM(points) FROM people GROUP BY firstname ORDER BY 2 DESC";

And welcome to WebmasterWorld djsimple007. :)

dc

[tbarbedo]

You have to alias your sum column to use it...

SELECT firstname, SUM(points) AS points
FROM people
GROUP BY firstname

you can then put it in your array like this..

$newarray[] = $row['firstname'] . ' ' . $row['points'];

[djsimple007]

perfect! now it sorts by name (case sensitive). But I am trying to sort by the number. Thanks for that tip! Can I put the values in a multidimensional array like so:
$newarray[][] = ($row['firstname'],$row['points']); (syntax?)
then sort by the points 'column'?

Im glad to see this is a thriving forum!

cheers.bo

[djsimple007]

so i can get it to sort by putting the number first (ex: 12 jon) but it sorts by the first character, can I sor by something equivelent to substr($newarray,0,2)? Or sort by the first 2 digits as opposed to the first one only?

[coopster]

You haven't put it all together yet, if you do you will have the result set all ready for you:

SELECT firstname, SUM(points) AS points 
FROM people 
GROUP BY firstname 
ORDER BY points DESC

[djsimple007]

thanks, earlier it wasnt working, but that was a result of garbage in garbage out, thanks all for your help! but that was the same solution I ended up using.