Sorting results

I got some help a while ago retrieving data from some databases and then adding the data together.
At the moment I have 2 DB's. One contains people, the other contains their scores from competitions. The script I am writing finds each user and then finds all their scores and adds their scores together.
This works, however the result is sorted by username. I need to be able to take the results and sort by the total score for each person. I've been trying to make an associative array to store
name => score, name2 => score2
etc and then sort it, however it just doesn't work. It looks like it makes and array for each record.
Any ideas?

Try using sum() when you run your query.

select sum(competition.score) as total from competition, people where competition.id = people.id order by total

OR

If you rather run two queries get use sum() from the the scores but try a GROUP BY or maybe DISTINCT.

That should get you going in right direction.

sorry that doesn't make any sense to me (still learning php) here is my code


$ma = array();
while($row_ID = mysql_fetch_array($result_ID))
{
$score = $total+=$row_ID['score'];
$shooter = $row_Shooters['name'];
$ma [$shooter] = $score;
}
asort($ma);
foreach ($ma as $key => $val)
{
echo "<tr><td>";
echo "".$key."";
echo " ";
echo "".$val."</td>
</tr>";
}


I have it going into the array OK but it just doesn't sort tham at all. Any ideas?

i think you would sort it when you get it from the database wouldnt you?

ie.


sorry if i misunderstood your post

No here is the issue. Lets say in shooters DB I have Joe
In scores DB I have 5 scores Joe has shot, say
1200
1300
1250
1200

I do a search on all the shooters so it is displayed

Joe 4950

Then the next shooter hets the same, say their's is

Jill 5125

It displays in that order, how do I get the data from the DB's, add them together and then resort?

The site is here
http://comps.dva.asn.au/champsphp/index.php

thanks

ok, this should work hopefully. Its presuming that there is a linking id in the shooters and scores table.



it finds the name from the shooters table, and then adds all the scores with the same id...

Yeah that's right the ID links the 2. That code you sent though just returns errors at line 2 or 3. I think I know what you are trying to do, however don't know enough to see what the result it. Been over it 10 times and can't see the code error. Sometimes the PHP error reporting is very frustrating.
Can you see the error? Thanks

I can't see the error either.

If you copy/pasted the code, the problem might be that it uses &nbsp; for spaces, which causes problems with some browsers/OSes.

Otherwise, it would help if you posted what exactly the error is.