How can I group this array and subtotal?

PHP_Idiot · January 29, 2014

hi Freaks

I've inherited some script I don't fully understand, and I need to make a change, but I really live up to my screen name!

In this snippet (part of a much larger section):

$arrGROUPED[$row[1].$row[2].$row[3]] =  array(1 => $row[1], 2 => $row[2], 3 => $row[3], 4 => $bonusGROUPED[$row[1].$row[2].$row[3]], 5 =>$pointsGROUPED[$row[1].$row[2].$row[3]], 6 => $totalpointsGROUPED[$row[1].$row[2].$row[3]], 7 => $playedGROUPED[$row[1].$row[2].$row[3]], 8 =>$CHIPS); //creates new array combining all the above data

print("$arrGROUPED");
// array values are [1]Player.FirstName , [2]Player.LastName,  [3]Venue.VenueName as Venue, [4]Results.MemCard, [5]Results.EarlyReg, [6]Position.Points as Venue_Points, [7]Results.Date

This groups players and counts the number of times played at each venue.

The print statement displays this:

- Victor Robinson 3 -

- Nick White 3 -

- Geoff Steele 1 -

- Geoff Steele 2 -

- Jamie Lane 3 -

- Jamie Lane 2 -

- Jamie Lane 3 -

- Phillip Emmerson 3 -

What I'd like to do is group them so it would display their total games played at all venues, as follows:

- Victor Robinson 3 -

- Nick White 3 -

- Geoff Steele 3 -

- Jamie Lane 8 -

- Phillip Emmerson 3 -

I know I could write yet another query, but I'd ideally like to use the existing information and just total it!

Any ideas?

requinix · January 29, 2014

"Query" you say? So this is coming from a database query already? That's actually the best place to do this: change the query so that it groups and totals for you.

What's the rest of the code?

Edited January 29, 2014 by requinix

PHP_Idiot · January 29, 2014

Before you get bogged down with the code I need to point out that I actually want to the total games played as well as the existing data this already provides. Hence my trepidation at messing with the Query!

The initial query is this:


$top40 = "SELECT Player.MembershipNo , Player.FirstName , Player.LastName,  Venue.VenueName as Venue, Results.MemCard, Results.EarlyReg, Position.Points as Venue_Points, Results.Date
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' 
ORDER BY MembershipNo, Venue"; //query pulls all info required to calculate final chip stack over the current season

$result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error()); //creates variable holding query results

and the full php is a bit hefty!! but here it is...:

$LastSameVenue =0; //create variable and give value 0

while($r = mysql_fetch_array($result)){									// THIS FIRST LOOP CALLCULATES THE CHIP STACK FOR EACH RESULTw
																		//AND BUILDS ARRAY.

			$arr[] =  array(1 => $r[1], 2 => $r[2], 3 => $r[3], 4 => $r[4], 5 => $r[5], 6 => $r[6], 7 => $r[7], 8 => $r[8]);
// array values are [1]Player.FirstName , [2]Player.LastName,  [3]Venue.VenueName as Venue, [4]Results.MemCard, [5]Results.EarlyReg, [6]Position.Points as Venue_Points, [7]Results.Date
}

foreach ($arr as $key => $row) { 										// THIS SECOND LOOP BUILD AN ARRAY FOR EACH PRSON AT EACH VENUE
																		// ADDING THE POINTS FOR EACH VENUE AND ADDING THE NUMBER OF TIMES
																		// PLAYED AT EACH VENUE. BEAUTY

	$bonusGROUPED[$row[1].$row[2].$row[3]] = $bonusGROUPED[$row[1].$row[2].$row[3]] + $row[4] + $row[5]; //grouped data with bonus chips
	$pointsGROUPED[$row[1].$row[2].$row[3]] = $pointsGROUPED[$row[1].$row[2].$row[3]] + $row[6]; //creates grouped data for each player with points
	$totalpointsGROUPED[$row[1].$row[2].$row[3]] = $totalpointsGROUPED[$row[1].$row[2].$row[3]] + $row[4] + $row[5] + $row[6];//creates grouped data for each player with points
	$playedGROUPED[$row[1].$row[2].$row[3]] = $playedGROUPED[$row[1].$row[2].$row[3]] +1; //creates grouped data for number of times played
	$GBPCPoints = $totalpointsGROUPED[$row[1].$row[2].$row[3]] * ($average_by_venue[$row[3]]+ $AVE); // calculates number of chips
	$CHIPS = ceil($GBPCPoints / 1) * 1; //rounds up chipstack to nearest 25		

$arrGROUPED[$row[1].$row[2].$row[3]] =  array(1 => $row[1], 2 => $row[2], 3 => $row[3], 4 => $bonusGROUPED[$row[1].$row[2].$row[3]], 5 =>$pointsGROUPED[$row[1].$row[2].$row[3]], 6 => $totalpointsGROUPED[$row[1].$row[2].$row[3]], 7 => $playedGROUPED[$row[1].$row[2].$row[3]], 8 =>$CHIPS); //creates new array combining all the above data

//print("$arrGROUPED");
}

foreach ($arrGROUPED as $key => $row2) {								// THIS LOOP NOW TAKES THE ARRAY AND SORTS IT IN ORDER OF NAME
																		// AND THEN CHIPS. 
    $FirstName[$key]  = $row2['1'];
    $LastName[$key] = $row2['2'];
    $Venue[$key] = $row2['3'];
	$bonus[$key] = $row2['4'];
    $Venue_Points[$key] = $row2['5'];
	$Total_Points[$key] = $row2['6'];
    $Venue_Play_Count[$key] = $row2['7'];
    $chipstack[$key] = 	$row2['8'];
 

//print("-  $row2[1] $row2[2] $row2[7] - <br/>");

}
array_multisort($FirstName, SORT_DESC, $LastName, SORT_DESC,  $chipstack, SORT_DESC, $arrGROUPED);





$printedFirst = "NULL";
																					
foreach ($arrGROUPED as $key => $row3) {								// THIS LOOP MAKES A NEW ARRAY WITH ONLY THE FIRST OF EACH PESONS RESULT
																		// WHICH IS THERE BEST. WOOHOOO

	   if($row3[1].$row3[2] !=  $printedFirst){
		 $arrHIGHEST_RESULT[] =  array(1 => $row3[1], 2 => $row3[2], 3 => $row3[3], 4 => $row3[4], 5 => $row3[5], 6 => $row3[6], 7 => $row3[7], 8 => $row3[8]);
		//print("--  $row3[1] $row3[2] $row3[3] $row3[4] <B>$row3[5]</B>  $row3[6] $row3[7] $row3[8] -- <br/>");
		 }
      $printedFirst =  $row3[1].$row3[2];
	
}

foreach ($arrHIGHEST_RESULT as $key => $row4) {							// AND THEN THIS LOOP REORDERS THE NEW ARRAY TO PUT IN ORDER
																		// OF CHIPS. FANTASTIC
    $FirstName4[$key]  = $row4['1'];
    $LastName4[$key] = $row4['2'];
    $Venue4[$key] = $row4['3'];
	$bonus4[$key] = $row4['4'];
    $Venue_Points4[$key] = $row4['5'];
	$Total_Points4[$key] = $row4['6'];
    $Venue_Play_Count4[$key] = $row4['7'];
    $chipstack4[$key] = $row4['8'];

//print("#  $row4[1] $row4[2] $row4[3] $row4[4] <B>$row4[5]</B> $row4[6] $row4[7] # <br/>");

}
	
array_multisort($chipstack4, SORT_DESC, $arrHIGHEST_RESULT);

foreach ($arrHIGHEST_RESULT as $key => $row3) {								// THIS LAST LOOP THEN PRINTS THE ARRAY. FIN

if($pos < 100000){
if($pos == 61){
$style = "style=\"border-top: solid #fff 4px;\"";	
}
else{ 
$style = "";	
}
	
	$stack = ceil($row3[8] / 25) * 25; //rounds up chipstack to nearest 25
	$PlayedChips = $Venue_Play_Count4[$key]*100;
	$TotalStack = $stack + $PlayedChips;
	echo"
	<tr ><td align=\"center\" $style>$pos</td>
		<td align=\"center\" $style><a href=\"playerDates.php?FName={$row3['1']}&LName={$row3['2']}\">" . $row3[1] .' '. $row3[2] .  "</a></td>
		<td align=\"center\" $style><a href=\"venueDates2.php?VenueName={$row3[3]}\">" . $row3[3] . "</a></td>
		<td align=\"center\" $style>" . $row3[6] . "</td>
		<td align=\"center\" $style>" . $stack . "</td>
		<td align=\"center\" $style>" . $Venue_Play_Count4[$key] . "</td> 
		<td align=\"center\" $style>" . $TotalStack . "</td>
	</tr>";
	$pos++;
      $printedFirst =  $row3[1].$row3[2];
	
	



}
}
//And close the table.

echo "</table>";

Edited January 29, 2014 by PHP_Idiot

PHP_Idiot · January 30, 2014

Any ideas?

I'm pretty sure it should be fairly simple, there must be a way to extract the data from the array in my initial comment and just add them together, isn't there?

mac_gyver · January 30, 2014

this suggestion has nothing to do with your question, but you and those you are asking to help with this would be much better able to follow what the code is doing, if you use a mysql_fetch_assoc() statement to retrieve the values from the query and use the associative array index names everywhere so that you don't need to find and keep track of what each numerical offset means. you can also just use $arr[] = $r; when loading the data into the $arr array.

PHP_Idiot · January 30, 2014

I found this piece of code which I think should do the job I need, but I can't get it to work perhaps some one could let me know how I can apply it to my array?

$sum = array_reduce($someArray, function($result, $item) {
    if (!isset($result[$item['id']])) $result[$item['id']] = 0;
    $result[$item['id']] += $item['subtotal'];
    return $result;
}, array());

var_dump($sum); // array(5) { [1]=> int(10) [2]=> int(18) [3]=> int(14) [4]=> int(4) [5]=> int(3) }

My array is this:

foreach ($arrGROUPED as $key => $row2) {							
    $FirstName[$key]  = $row2['1'];
    $LastName[$key] = $row2['2'];
    $Venue[$key] = $row2['3'];
    $bonus[$key] = $row2['4'];
    $Venue_Points[$key] = $row2['5'];
    $Total_Points[$key] = $row2['6'];
    $Venue_Play_Count[$key] = $row2['7'];
    $chipstack[$key] = 	$row2['8'];
}

I want to sub total $Venue_Play_Count wherever $FirstName & $LastName are the same .

So rather than this:

- Victor Robinson 3 -

- Nick White 3 -

- Geoff Steele 1 -

- Geoff Steele 2 -

- Jamie Lane 3 -

- Jamie Lane 2 -

- Jamie Lane 3 -

- Phillip Emmerson 3 -

I get this:

- Victor Robinson 3 -

- Nick White 3 -

- Geoff Steele 3 -

- Jamie Lane 8 -

- Phillip Emmerson 3 -

I'm sure if I switch out the variable names it should work, but everything I try just gives me array(0) { } array(0) { } over and over.

var_dump($arrGROUPED) gives the following result:


array(140) { 
["VictorRobinsonThe Globe (Weds)"]=> array( { [1]=> string(6) "Victor" 
[2]=> string( "Robinson" 
[3]=> string(16) "The Globe (Weds)" 
[4]=> int(6) 
[5]=> int(6) 
[6]=> int(12) 
[7]=> int(3) 
[8]=> float(435) } 
"NickWhiteThe Globe (Weds)"]=> array( { [1]=> string(4) "Nick" 
[2]=> string(5) "White" 
[3]=> string(16) "The Globe (Weds)" 
[4]=> int(6) 
[5]=> int(6) 
[6]=> int(12) 
[7]=> int(3) 
[8]=> float(435) } 
["GeoffSteeleThe Globe (Mon)"]=> array( { [1]=> string(5) "Geoff" 
[2]=> string(6) "Steele" 
[3]=> string(15) "The Globe (Mon)" 
[4]=> int(2) 
[5]=> int(2) 
[6]=> int(4) 
[7]=> int(1) 
[8]=> float(108) } 
["GeoffSteeleThe Globe (Weds)"]=> array( { [1]=> string(5) "Geoff" 
[2]=> string(6) "Steele" 
[3]=> string(16) "The Globe (Weds)" 
[4]=> int(4) 
[5]=> int(26) 
[6]=> int(30) 
[7]=> int(2) 
[8]=> float(1086) } 
["JamieLaneThe Crown"]=> array( { [1]=> string(5) "Jamie" 
[2]=> string(4) "Lane" 
[3]=> string(9) "The Crown" 
[4]=> int(5) 
[5]=> int(88) 
[6]=> int(93) 
[7]=> int(3) 
[8]=> float(2019) } 
["JamieLaneThe Globe (Mon)"]=> array( { [1]=> string(5) "Jamie" 
[2]=> string(4) "Lane" 
[3]=> string(15) "The Globe (Mon)" 
[4]=> int(4) 
[5]=> int(36) 
[6]=> int(40) 
[7]=> int(2)
[8]=> float(1080) } 
["JamieLaneThe Globe (Weds)"]=> array( { [1]=> string(5) "Jamie" 
[2]=> string(4) "Lane" 
[3]=> string(16) "The Globe (Weds)" 
[4]=> int(6) 
[5]=> int(64) 
[6]=> int(70) 
[7]=> int(3)
[8]=> float(2534) } 
["PhillipEmmersonThe Crown"]=> array( { [1]=> string(7) "Phillip" 
[2]=> string( "Emmerson" 
[3]=> string(9) "The Crown" 
[4]=> int(6) 
[5]=> int(36) 
[6]=> int(42) 
[7]=> int(3) 
[8]=> float(912) }

I hope that helps explain things a bit better!

Edited January 30, 2014 by PHP_Idiot

PHP_Idiot · January 30, 2014

this suggestion has nothing to do with your question, but you and those you are asking to help with this would be much better able to follow what the code is doing, if you use a mysql_fetch_assoc() statement to retrieve the values from the query and use the associative array index names everywhere so that you don't need to find and keep track of what each numerical offset means. you can also just use $arr[] = $r; when loading the data into the $arr array.

Trouble is I didn't write this, and as this does so much I don't yet want to risk breaking it!!!

But I think I understand what you mean

PHP_Idiot · January 30, 2014

Surely someone knows how I can do this...pleease!

I just need to understand how to sub total / total a multidimensional array, I've googled the hell out of this, but not found anything that I can make work yet.

Any help would be freakingawesomelyfantastic and muchly appreciated

:hail_freaks:

Barand · January 30, 2014

this suggestion has nothing to do with your question, but you and those you are asking to help with this would be much better able to follow what the code is doing, if you use a mysql_fetch_assoc() statement to retrieve the values from the query and use the associative array index names everywhere so that you don't need to find and keep track of what each numerical offset means. you can also just use $arr[] = $r; when loading the data into the $arr array.

+1

As long as you stick with this

$bonusGROUPED[$row[1].$row[2].$row[3]] = $bonusGROUPED[$row[1].$row[2].$row[3]] + $row[4] + $row[5]; //grouped data with bonus chips
    $pointsGROUPED[$row[1].$row[2].$row[3]] = $pointsGROUPED[$row[1].$row[2].$row[3]] + $row[6]; //creates grouped data for each player with points
    $totalpointsGROUPED[$row[1].$row[2].$row[3]] = $totalpointsGROUPED[$row[1].$row[2].$row[3]] + $row[4] + $row[5] + $row[6];//creates grouped data for each player with points
    $playedGROUPED[$row[1].$row[2].$row[3]] = $playedGROUPED[$row[1].$row[2].$row[3]] +1; //creates grouped data for number of times played
    $GBPCPoints = $totalpointsGROUPED[$row[1].$row[2].$row[3]] * ($average_by_venue[$row[3]]+ $AVE); // calculates number of chips
    $CHIPS = ceil($GBPCPoints / 1) * 1; //rounds up chipstack to nearest 25

PHP_Idiot · January 31, 2014

OK tomorrow I will try to replace all the numbers with index names.

See if that helps ;-)

PHP_Idiot · January 31, 2014

Thanks to Barand on this thread I now have a Query that gives me all the info I need from the database.

The following php puts the query result into an array then groups chunks of that data together so it can be presented in a table later.

If I print the contents of the array everything is correct and as expected.

However, I'm struggling to group the values in PlayerTotalGames => $r[8].

I've been following the format of the previous grouped data and I've tried to create a new $PlayerTotalGamesGROUPED value as can be seen in bold below, but the values are way out from those stored in PlayerTotalGames => $r[8].

Any idea how I can get the PlayerTotalGames => $r[8] into the final $arrGROUPED[] array?

while($r = mysql_fetch_array($result)){									
	$arr[] =  array(MembershipNo => $r[0], FirstName => $r[1], LastName => $r[2], Venue => $r[3], MemCard => $r[4], EarlyReg => $r[5], Points => $r[6], Date => $r[7], PlayerTotalGames => $r[8]);
//print("-- $r[0] $r[1] $r[2] $r[3] $r[4] <B>$r[5]</B>  $r[6] $r[7] $r[8] -- <br/>");
	}

foreach ($arr as $key => $row) { 					
	$bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[MemCard] + $row[EarlyReg]; //grouped data with bonus chips
	
	$pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[Points]; //creates grouped data for each player with points
	
	$totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[MemCard] + $row[EarlyReg] + $row[Points];//creates grouped data for each player with points
	
	$playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] +1; //creates grouped data for number of times played
	
$PlayerTotalGamesGROUPED[$row[FirstName].$row[LastName].$row[Venue]] = $PlayerTotalGamesGROUPED[$row[FirstName].$row[LastName].$row[Venue]] + $row[PlayerTotalGames];
	
	$GBPCPoints = $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] * ($average_by_venue[$row[Venue]]+ $AVE); // calculates number of chips
	
	$CHIPS = ceil($GBPCPoints / 1) * 1; //rounds up chipstack to nearest 1		

$arrGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] =  array(
0 => $row[MembershipNo], 
1 => $row[FirstName],
2 => $row[LastName], 
3 => $row[Venue], 
4 => $bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
5 => $pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
6 => $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
7 => $playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
8 => $CHIPS, 
9 => $PlayerTotalGamesGROUPED[$row[FirstName].$row[LastName].$row[Venue]]); //creates new array combining all the above data

Barand · January 31, 2014

$PlayerTotalGamesGROUPED[$row[FirstName].$row[LastName].$row[Venue]] = $PlayerTotalGamesGROUPED[$row[FirstName].$row[LastName].$row[Venue]] + $row[PlayerTotalGames];

The above is keyed by player.venue.

The totals in the query are by player

Is that the reason for your discrepancy?

PS I mentioned in the other thread how to change it to get totals by player/venue

Edited January 31, 2014 by Barand

PHP_Idiot · February 1, 2014

The above is keyed by player.venue.

The totals in the query are by player

Is that the reason for your discrepancy?

PS I mentioned in the other thread how to change it to get totals by player/venue

It would seem that the code is actually correct at this stage, but that it's getting messed up further down the page! (I don't actually want it by player/venue so that bits ok).

When I do the var_dump on line 114 it gives me the correct information which is as follows:

array(1) {
    ["0101001VictorRobinsonGB Poker Club Online"]=> array(10) {
        [0]=> string(7) "0101001" [1]=> string(6) "Victor"
        [2]=> string( "Robinson"
        [3]=> string(20) "GB Poker Club Online"
        [4]=> int(2)
        [5]=> int(2)
        [6]=> int(4)
        [7]=> int(1)
        [8]=> float(132)
        [9]=> int(4) } }
array(2) {
    ["0101001VictorRobinsonGB Poker Club Online"]=> array(10) {
        [0]=> string(7) "0101001"
        [1]=> string(6) "Victor"
        [2]=> string( "Robinson" [
        3]=> string(20) "GB Poker Club Online"
        [4]=> int(2)
        [5]=> int(2)
        [6]=> int(4)
        [7]=> int(1)
        [8]=> float(132)
        [9]=> int(4) } }

you will see that whats happening is that for every array (1), (2)...etc it is recording a players game result. So every player in every game (between the dates in the query) appears.

[9]=> int is the number of games in total across all venues that person has played. It is constant across all of the array's for that person. Other elements like [3], [4], [5], [6], [7], [8], will change depending on the venue and their finishing position.

However, when I do a Var_dump on line 133 I get a different result!

< array(179) { 
["0101001VictorRobinsonGB Poker Club Online"]=> array(10) { 
[0]=> string(7) "0101001" 
[1]=> string(6) "Victor" 
[2]=> string( "Robinson" 
[3]=> string(20) "GB Poker Club Online" 
[4]=> int(2) 
[5]=> int(2) 
[6]=> int(4) 
[7]=> int(1) 
[8]=> float(132) 
[9]=> int(4) 
} 
["0101001VictorRobinsonThe Globe (Weds)"]=> array(10) { 
[0]=> string(7) "0101001" 
[1]=> string(6) "Victor" 
[2]=> string( "Robinson" 
[3]=> string(16) "The Globe (Weds)" 
[4]=> int(6) 
[5]=> int(6) 
[6]=> int(12) 
[7]=> int(3) 
[8]=> float(437) 
[9]=> int(12)
}

Between these two points it seems to be grouping by venue and changing the value in the 9th key. This is happening in lines 119 to 130. But I need the numbers in [9] to remain the same throughout the rest of the page and not grouped.

If i simply remove the line (130) it causes the column in the final table to remain blank.

I know this is probably straight forward but I've spent hours playing around and I can't figure it out at all!!!

Cheers

Code is below ....

<div class="newsbox">
<div class="nb_header">Season Finalists</div>
<div class="nb_date"></div>
<div class="nb_postedby"></div>
<div class="nb_content">

<div class="top40">
<?php
                  
include 'includes/open_db.php'; 
include 'includes/DateSet.php'; 
include 'venue_averages2.php';


$top40 = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, 
       Venue.VenueName AS Venue, Results.MemCard, Results.EarlyReg, 
       Position.Points AS Venue_Points, Results.Date, tot.PlayerTotalGames
FROM Player
       INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo
       INNER JOIN Venue ON Venue.VenueID = Results.VenueID
       INNER JOIN Position ON Results.Position = Position.Position
       INNER JOIN (
             SELECT MembershipNo, COUNT( DATE ) AS PlayerTotalGames
             FROM Results
			 WHERE Results.Date BETWEEN  '$BeginDate' AND  '$EndDate'
             GROUP BY MembershipNo
             ) AS tot ON Player.MembershipNo = tot.MembershipNo
WHERE Results.Date BETWEEN  '$BeginDate' AND  '$EndDate'
ORDER BY MembershipNo, venue"; //query pulls all info required to calculate final chip stack over the current season

$result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error()); 
//creates variable holding query results
//print_r(mysql_fetch_assoc($result));


$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"; //query pulls all data needed to calculate venue average over the duration of the season
$result2 = mysql_query($AvePlayers) or die("Couldn't execute query because: ".mysql_error());//creates variable holding query results

$pos=1; //creates variable pos with initial value of 1

$average_by_venue = array(); // creates an array 

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

echo <<<html
		<table align="center" class="table" border="0" cellspacing="0" cellpadding="2">

	<tr class="hr">
	<td height="35" align="center" ><strong>Pos.</strong></td>

		<td height="35" align="center" ><strong>Name</strong></td>

		<td height="35" align="center" ><strong>Venue</strong></td>
		
		<<td height="35" width="65" align="center" ><strong>Venue<br/>Points</strong></td>
		
		<td height="35" width="65" align="center" ><strong>Venue Chips*</strong></td>
		<td height="35" width="65" align="center" ><strong>Games Played</strong></td>

		<td height="35" width="65" align="center" ><strong>Weighted<br/>Starting Stack**</strong></td>
	</tr>
html;

// Above creates table and headers. Now start the loop.

$LastSameVenue =0; //create variable and give value 0

while($r = mysql_fetch_array($result)){
// THIS FIRST LOOP CALLCULATES THE CHIP STACK FOR EACH RESULT AND BUILDS ARRAY.

$arr[] =  array(MembershipNo => $r[0], FirstName => $r[1], LastName => $r[2], Venue => $r[3], MemCard => $r[4], EarlyReg => $r[5], Points => $r[6], Date => $r[7], PlayerTotalGames => $r[8]);
		
//print("-- $r[0] $r[1] $r[2] $r[3] $r[4] <B>$r[5]</B>  $r[6] $r[7] $r[8] -- <br/>");
// array values are [1]FirstName , [2]LastName,  [3]Venue, [4]MemCard, [5]EarlyReg, [6]Points as Venue_Points, [7]Date
}

foreach ($arr as $key => $row) { 
// THIS SECOND LOOP BUILD AN ARRAY FOR EACH PERSON AT EACH VENUE
// ADDING THE POINTS FOR EACH VENUE AND ADDING THE NUMBER OF TIMES
// PLAYED AT EACH VENUE. 

	$bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[MemCard] + $row[EarlyReg]; //grouped data with bonus chips
	$pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[Points]; //creates grouped data for each player with points
	$totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[MemCard] + $row[EarlyReg] + $row[Points];//creates grouped data for each player with points
	$playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] +1; //creates grouped data for number of times played
	
	$PlayerTotalGamesGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] = $PlayerTotalGamesGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] + $row[PlayerTotalGames];
	
	$GBPCPoints = $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] * ($average_by_venue[$row[Venue]]+ $AVE); // calculates number of chips
	$CHIPS = ceil($GBPCPoints / 1) * 1; //rounds up chipstack to nearest 1		

$arrGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]] =  array(
0 => $row[MembershipNo], 
1 => $row[FirstName], 
2 => $row[LastName], 
3 => $row[Venue], 
4 => $bonusGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
5 => $pointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
6 => $totalpointsGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
7 => $playedGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]], 
8 => $CHIPS, 
9 => $PlayerTotalGamesGROUPED[$row[MembershipNo].$row[FirstName].$row[LastName].$row[Venue]]); 
//creates new array combining all the above data
//var_dump($arrGROUPED);

}
 
foreach ($arrGROUPED as $key => $row2) {
// THIS LOOP NOW TAKES THE ARRAY AND SORTS IT IN ORDER OF NAME
    $MembershipNo[$key]  = $row2['0'];
    $FirstName[$key]  = $row2['1'];
    $LastName[$key] = $row2['2'];
    $Venue[$key] = $row2['3'];
    $bonus[$key] = $row2['4'];
    $Venue_Points[$key] = $row2['5'];
    $Total_Points[$key] = $row2['6'];
    $Venue_Play_Count[$key] = $row2['7'];
    $chipstack[$key] = 	$row2['8'];
    $PlayerTotalGames[$key] = 	$row2['9'];
 
//print("-  $row2[1] $row2[2] $row2[3] $row2[4] $row2[5] $row2[6] $row2[7] $row2[8] $row2[9] - <br/>");
//var_dump($arrGROUPED); // array(5) { [1]=> int(10) [2]=> int(18) [3]=> int(14) [4]=> int(4) [5]=> int(3) }
}

array_multisort($FirstName, SORT_DESC, $LastName, SORT_DESC,  $chipstack, SORT_DESC, $arrGROUPED);
$printedFirst = "NULL";
											
foreach ($arrGROUPED as $key => $row3) {
// THIS LOOP MAKES A NEW ARRAY WITH ONLY THE FIRST OF EACH PESONS RESULT WHICH IS THERE BEST. WOOHOOO

	   if($row3[1].$row3[2] !=  $printedFirst){
		 $arrHIGHEST_RESULT[] =  array(0 => $row3[0], 1 => $row3[1], 2 => $row3[2], 3 => $row3[3], 4 => $row3[4], 5 => $row3[5], 6 => $row3[6], 7 => $row3[7], 8 => $row3[8], 9 => $row3[9]);
		//print("-- $row3[0] $row3[1] $row3[2] $row3[3] $row3[4] <B>$row3[5]</B>  $row3[6] $row3[7] $row3[8] $row3[9]-- <br/>");
		 }
      $printedFirst =  $row3[1].$row3[2];
	
}

foreach ($arrHIGHEST_RESULT as $key => $row4) {	
// AND THEN THIS LOOP REORDERS THE NEW ARRAY TO PUT IN ORDER OF CHIPS. FANTASTIC
    $MembershipNo4[$key]  = $row4['0'];							
    $FirstName4[$key]  = $row4['1'];
    $LastName4[$key] = $row4['2'];
    $Venue4[$key] = $row4['3'];
    $bonus4[$key] = $row4['4'];
    $Venue_Points4[$key] = $row4['5'];
    $Total_Points4[$key] = $row4['6'];
    $Venue_Play_Count4[$key] = $row4['7'];
    $chipstack4[$key] = $row4['8'];
    $PlayerTotalGames4[$key] = 	$row4['9'];

//print("#  $row4[0] $row4[1] $row4[2] $row4[3] $row4[4] <B>$row4[5]</B> $row4[6] $row4[7] $row4[8]  $row4[9]# <br/>");

}
	
array_multisort($chipstack4, SORT_DESC, $arrHIGHEST_RESULT);

foreach ($arrHIGHEST_RESULT as $key => $row3) {								// THIS LAST LOOP THEN PRINTS THE ARRAY. FIN
	
	$stack = ceil($row3[8] / 25) * 25; //rounds up chipstack to nearest 25
	$PlayedChips = $PlayerTotalGames4[$key]*100;
	$TotalStack = $stack + $PlayedChips;
	echo"
	<tr ><td align=\"center\" $style>$pos</td>
		<td align=\"center\" $style><a href=\"playerDates.php?FName={$row3['1']}&LName={$row3['2']}\">" . $row3[1] .' '. $row3[2] .  "</a></td>
		<td align=\"center\" $style><a href=\"venueDates2.php?VenueName={$row3[3]}\">" . $row3[3] . "</a></td>
		<td align=\"center\" $style>" . $row3[6] . "</td>
		<td align=\"center\" $style>" . $stack . "</td>
		<td align=\"center\" $style>" . $PlayerTotalGames4[$key] . "</td> 
		<td align=\"center\" $style>" . $TotalStack . "</td>
	</tr>";
	$pos++;
      $printedFirst =  $row3[1].$row3[2];
}
//And close the table.

echo "</table>";

//And close the table.

echo "</table>";
?>
  
  <p align="LEFT" color="Black">*Venue Chips = Venue Points x (Venue Ave + League Ave) and are rounded up to the nearest 25.</p>
   <p align="LEFT" color="Black">**Starting Stack = Venue Points + (Games Played x 100).</p>

</div></div>
</div>

Barand · February 1, 2014

What is it that you are trying to produce from all these arrays? I haven't managed to work that out since your very first post.

PHP_Idiot · February 2, 2014

What is it that you are trying to produce from all these arrays? I haven't managed to work that out since your very first post.

Ah yes I probably should have explained that up front!!

These arrays were originally created to build a dynamic results table for a poker league, the original table is still in use here. But we are changing our scoring system and therefore need to adjust the table that is being displayed, the table I'm working on is here

CURRENTLY

The players can play in as many venues as they like, they score points for finishing positions (40 for 1st, 32 for 2nd etc) these points are turning into chips using the following calculation CHIPS = Point x (Venue average + league Average). So if out league is averaging 13.4 players a game, and their venue is averaging 23 players a game then their 40 points for a win would be worth: 40 x (13.4 + 23) = 1456 chips towards the season final, this figure is the rounded up to the nearest 25 (1475) and added to the rest of their results .

The arrays calculate the number of chips each player has earned in each venue, then selects their best venue (one with most chips) and adds that the the table that is displayed. Chip counts on the front page then add 5000 chips to everyone's stack to make sure every one has plenty for the final.

AIM

We want to change this so that each player is awarded 100 chips for every time they play in any venue (instead of adding the 5000 to everyone) this means the points to chips calculation needs to change a little. The main calculation stays the same but once the players points have been totaled into chips it then needs to add another 100 chips per venue to get the correct result.

This is why I wanted to do a full games played calculation for each player, and that is the figure currently stored in PlayerTotalGames => $r[8]. But I'm struggling to make it stay the same through the rest of the arrays.If I could just get that value to print into the table, it would all work perfectly!

Hope that clarifies things bit!

Sign In

How can I group this array and subtotal?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information