Sorting/setting up an array during 2 loops?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Fed-Exin
Forum Newbie
Posts: 3
Joined: Mon Feb 28, 2005 5:42 pm

Sorting/setting up an array during 2 loops?

Post by Fed-Exin »

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:

Code: Select all

<?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>"; 
} 
} 
?>

$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


feyd | please use

Code: Select all

tags while

Code: Select all

is offline[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

this can all be performed by MySQL using GROUP BY and ORDER BY clauses with the SUM() function to add together the data for each golfer automatically.
Fed-Exin
Forum Newbie
Posts: 3
Joined: Mon Feb 28, 2005 5:42 pm

Post by Fed-Exin »

-Thanks for the code posting heads up feyd!-


Could you elaborate on all that? =) Would I be setting up one or multiple loops for what I'm trying to do, and does the ORDER BY come after the SUM() function as to put them in ASC or DESC order?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it appears to be solvable in a single query. If you read through the select syntax (SELECT) you'll see that ORDER BY appears after GROUP BY, which both appear after WHERE clauses.

I'd suggest using INNER JOIN rather than implicit, as the logic may be easier to deal with.

I'd also suggest to stop using mysql_result() in favor of mysql_fetch_assoc() or it's siblings, and they are faster in processing.
Fed-Exin
Forum Newbie
Posts: 3
Joined: Mon Feb 28, 2005 5:42 pm

Post by Fed-Exin »

Could you please connect a few of the dots for me? I've been searching and reading up, but there are so many ways to attack this problem.....

Thanks!
Post Reply