Sorting by an array within array!

PHP_Idiot

Hi I have an array that is populated with other arrays that in turn are populated by the results of a mysql query and displayed in a table as follows:

$arr = array("tablearray" => array(1 => $r['FirstName'], 2 => $r['LastName'], 3 => $r['Venue'], 4 => $r['Venue_Points'], 5 => $r['Venue_Play_Count'], 6 => $CHIPS));
	echo <<<html
	<tr><td align="center">$pos</td>
		<td align="center">{$arr["tablearray"][1]}</td>
		<td align="center">{$arr["tablearray"][2]}</td>
		<td align="center">{$arr["tablearray"][3]}</td>
		<td align="center">{$arr["tablearray"][4]}</td>
		<td align="center">{$arr["tablearray"][5]}</td>
		<td align="center">{$arr["tablearray"][6]}</td>

I need to be able to sort the table in descending order by $CHIPS which is stored in $arr["tablearray"][6] but can't work out how to do it, can anyone offer any suggestions?

Weedpacket

You'll need to put all the rows ($CHIPS value included) into an array so that you can sort them; you have to sort the rows before you display them (for obvious reasons). As it stands, $arr only contains one row, and there's not much to do to sort that many.

Once you have an array with all of the rows in it, [man]usort[/man] is easy enough to use. [man]array_multisort[/man] would do the job, too, and its syntax is supposed to be like SQL's, but [man]usort[/man] tends to be more straightforward.

PHP_Idiot

Hi Weedpacket

Thanks for your advice, I've put the whole code below to put it more into context as the $arr actually contains 32 lines of data at the moment not just one, this is information drawn down by two different mysql queries.

Each of the values held in the $arr array already has 32 seperate pieces of info in it, and I thought that by creating the $arr I had ultimately already put everything into an array! (you can view the test page HERE)

I have already tried usort, asort and arsort but haven't got the results I need, in hindsight thats probably because I don't have a proper array yet though!

I'm still a total noob with php and mysql (this is my first project) so would you mind explaining how I combine all those bits of information into a single array?

(As I mentioned I thought this is what I had done with $arr but clearly thats not the case!)

Heres the code...

//mySQL queries
$top40 = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points AS Venue_Points, SubSelect.count_results AS Venue_Play_Count, SubSelect3.sum_points3 AS Total_Points
FROM Player, ( 

SELECT Player1.MembershipNo, Venue1.VenueName, SUM( Position1.Points ) AS sum_points, COUNT( Player1.MembershipNo ) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
AND Results1.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player1.MembershipNo, Venue1.VenueName)SubSelect, ( 

SELECT Player3.MembershipNo, SUM( Position3.Points ) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
AND Results3.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player3.MembershipNo)SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points = ( 
SELECT MAX( SubSelect1.sum_points2 ) 
FROM ( 

SELECT Player2.MembershipNo, Venue2.VenueName, SUM( Position2.Points ) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player2.MembershipNo, Venue2.VenueName ) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >=25
ORDER BY SubSelect.sum_points DESC 
LIMIT 0 , 40"; 
$result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error());

$AvePlayers = "SELECT Venue.VenueName, COUNT( Results.MembershipNo ) , COUNT( DISTINCT Results.Date ) , cast( coalesce( COUNT( Results.MembershipNo ) / COUNT( DISTINCT Results.Date ) ) AS decimal( 10, 1 ) ) AS 'Average'
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Venue.VenueName
ORDER BY Average DESC 
LIMIT 0 , 40";
$result2 = mysql_query($AvePlayers) or die("Couldn't execute query because: ".mysql_error());
$pos=1;

$average_by_venue = array(); 

while ($r2 = mysql_fetch_array($result2) ) { 
  $average_by_venue[ $r2['VenueName'] ] = $r2['Average']; 
} 


echo <<<html
<table  border="1" width="480" cellpadding="1" cellspacing="1">
	<tr><td align="center"><strong>Pos.</strong></td>
		<td align="center"><strong>First</strong></td>
		<td align="center"><strong>Last</strong></td>
		<td align="center"><strong>Venue</strong></td>
		<td align="center"><strong>Points</strong></td>
		<td align="center"><strong>Played</strong></td>
		<td align="center"><strong>Chip Stack</strong></td>

</tr>
html;

//Now start the loop.




while($r = mysql_fetch_array($result)){
	//and echo each new row


$GBPCPoints = $r['Venue_Points'] * $average_by_venue[$r['Venue']] / 10; 
$CHIPS = ceil($GBPCPoints / 25) * 25;

$arr = array("tablearray" => array(1 => $r['FirstName'], 2 => $r['LastName'], 3 => $r['Venue'], 4 => $r['Venue_Points'], 5 => $r['Venue_Play_Count'], 6 => $CHIPS));
echo <<<html
<tr><td align="center">$pos</td>
	<td align="center">{$arr["tablearray"][1]}</td>
	<td align="center">{$arr["tablearray"][2]}</td>
	<td align="center">{$arr["tablearray"][3]}</td>
	<td align="center">{$arr["tablearray"][4]}</td>
	<td align="center">{$arr["tablearray"][5]}</td>
	<td align="center">{$arr["tablearray"][6]}</td>

</tr>
html;
$pos++;	}

Weedpacket

Well, like I said; you need to get all the records you want to sort and put them into an array first, then sort them, then output them.

You can't output the rows while you're still retrieving them - like I said, for obvious reasons. So that's the first thing you'll need to change. Once you've turned that loop into two (one for retrieving, one for displaying), you'll have a place (in between) to slot the usort() call into.

PHP_Idiot

Sorry for being stupid but I'm still not getting it!
You say get all the records and put them in an array first. The mysql query results are stored in $r and $r2 arrays, I then have to perform a calculation that results in another array $average_by_venue, then following some more maths calculations and store those results is $arr.

So I now have (as far as I understand) four arrays - How the heck do I combine all those into a single array?

I can manage the sorting and outputting, but I don't understand how I combine all the previous information into a single array.

When I searched google on combining arrays I found this...

<?php

function double_array_combine($arr1, $arr2, $arr3) {
$out = array();

$arr1 = array_values($arr1);
$arr2 = array_values($arr2);
$arr3 = array_values($arr3);

foreach($arr1 as $key1 => $value1) {
$out[(string)$value1][0] = $arr2[$key1];
$out[(string)$value1][1] = $arr3[$key1];
}

return $out;
}?>

But I couldn't get it to work as it keeps giving undefined variables errors!
and everything else I found was based on merging arrays, which I don't think is what I need.

If you could expand on this part I'd really really appreciate it.

Thanks