Sorting an array during 2 loops?

[FedExin]

First I'd like to say thank you to all the wonderful folks who post here, I've learned much from you comments! Second, I am going to give in and ask a question myself:

I have 2 loops working right now: The first gets my simple golfer data, the second searches for all the pledges that are associated with the golfer at that part of the loop, and adds up the total for each golfer. All that works fine. What I'd like to do is be able to sort the list from the second loop Ascending/Descending by the total generated in the second loop. I can get it to display as an array, but to be honest, I have noobish tendancies with arrays. Here is the code:

<?php

//get all the golfers rounded up in no particular order
$result2 = mysql_query("SELECT * FROM golfers ORDER by Lgname $srtby") or die("Query failed : " . mysql_error());
$num2=mysql_numrows($result2);

$i=0;
while ($i < $num2) {
$holesplayed2=mysql_result($result2,$i,"holesplayed");
$golfercomp2=mysql_result($result2,$i,"golfercomp");
$golferL2=mysql_result($result2,$i,"Lgname");
$golferF2=mysql_result($result2,$i,"Fgname");
$golferID=mysql_result($result2,$i,"IDg");

//get all the results for each golfer
$result = mysql_query("SELECT * FROM golfers, contacts, monies WHERE golfers.IDg = monies.MgolferID AND contacts.ID = monies.McontactID AND MgolferID='$golferID'") or die("Query failed : " . mysql_error());
$num=mysql_numrows($result);

$l=0;
while ($l < $num) {
$holesplayed=mysql_result($result,$l,"holesplayed");
$golferL=mysql_result($result,$l,"Lgname");
$golferF=mysql_result($result,$l,"Fgname");
$mID=mysql_result($result,$l,"IDm");
$jrsr=mysql_result($result,$l,"jrsr");
$mrmrs=mysql_result($result,$l,"mrmrs");
$Fname=mysql_result($result,$l,"Fname");
$Lname=mysql_result($result,$l,"Lname");
$paid=mysql_result($result,$l,"Mpaid");
$onetime=mysql_result($result,$l,"Monetime");
$pledge=mysql_result($result,$l,"Mpledge");
$golferID2=mysql_result($result,$l,"MgolferID");
$conID=mysql_result($result,$l,"ID");
$totalpledge = mysql_result($result,$l,"Mtotalpledge");
$total += $totalpledge;
$l++;

}

//here is where we would like to sort our data, I think..
//put a zero in for no data
if($total == ""){
$total = '0';
}

$gname = "$golferF2 $golferL2";
$array = array($total, $gname);
unset($total);

//displays the results nice, but not how I want it
/*echo "$golferF2 $golferL2";
setlocale(LC_MONETARY, 'en_US');
echo money_format('%(#10n', $total) . "<br><br>";
unset($total);*/

$i++;

arsort($array);
while (list($key, $val) = each($array)) {
echo "$val <br><br>";
}
}
?>

Thanks in advance for any help!

- Pete

[dmmh]

so you want to sort by the golfer with the most/least cash right? or do you mean something else?

and whats:
//get all the golfers rounded up in no particular order
$result2 = mysql_query("SELECT * FROM golfers ORDER by Lgname $srtby") or die("Query failed : " . mysql_error());

[FedExin]

$srtby is a variable that got copied from another page, sorry about the confusion. That sort doesn't actually need to happen. I know there are a few things I can trim!

I have 3 tables to get results from, here is how I get what I want:

1. the page goes and gets golfer info from 'golfers' (Loop 1)

2. WHILE in the loop it grabs all the info from 'contacts' and 'monies' WHERE it sees a pledge from a golfer AND adds all the data from a column and saves it as a variable:

$totalpledge = mysql_result($result,$l,"Mtotalpledge");
$total += $totalpledge;

3. OK, so now we have the total pledged money amount for my golfer, and after the loops complete, a list of golfers with the total money pledged to each golfer next to them. I want to sort the list from highest to lowest based on the variable $total, before the page is displayed.

-Pete

[FedExin]

<Wondering>

Am I asking a SQL question, or is this still a PHP problem I'm facing.... /wondering

[dmmh]

if $total is an variable, which I think it is in your case, I think you need to look into a way to sum the values inside the query and ORDER BY at the same time.

[dev.mysql.com...]

in this case its GROUP BY, which I havent used myself yet, so I cant be much of help here, but I do think this is an MySQL question more then a PHP question. All depends on how you decide to handle things. But the MySQL way is easier in your case I think

[FedExin]

$total is generated in the second loop, it comes from adding up the pulled variables $totalpledge:

$total += $totalpledge;

Would the query take place after that, and can I run a query for the 'virtual variable'?

[dmmh]

I think the query should go before this. Because you query the DB for a certain golfer and calculate the totals for each golfer inside the query and you can later order it, even inside the query if you want. But I havent used GROUP BY myself in conjunction with SUM, which seems like what you need and quit frankly I dont have the time to look into it all, but perhaps if you asked this in the MySQL forum they could point you in the right direction?