PHP_Idiot

Barand, firstly I owe you a massive apology, you're right you recently helped me with a query on a different post relating to this page, that looking back was a duplicate of the stuff I was doing in Jan/Feb this year that you also helped me with. I had completely forgotten about that, and bizarrely it didn't show up when I did a forum search! I'm not a programmer in any sense, in fact I sell stuff to hospitals, I only poke around under the bonnet of this website when I absolutely have too. The last time I was on the forum was back in Feb here where you had been helping me with exactly this problem, unfortunately it wasn't resolved at that time, and as a new season had started the league fell back to it's existing points system and it was forgotten about. Until now! I am always extremely grateful for all the help I have got from this forum and especially you, Barand, and I know that I am relying on the good will of others to help me in my ineptitude. I do try to learn as I go but, unfortunately, because I only dip into this 6-9 monthly I tend to have forgotten a lot of the stuff by the next time I need it. I am going to have a dig about in the old test files and find the things I'd been working on in Jan, at least that had cleaned up the numbers to index names! I've been comparing the data reported by my original array and the versions you kindly offered yesterday: The original data: < Array ( [0] => Array ( [1] => [2] => [3] => The Mother Redcap [4] => 0 [5] => 0 [6] => 2 [7] => 2014-07-09 [8] => 1 [9] => ) [1] => Array ( [1] => Jason [2] => King [3] => The Dolphin [4] => 1 [5] => 1 [6] => 32 [7] => 2014-08-27 [8] => 3 [9] => ) The new data set is slightly different: Array ( [0] => Array ( [MembershipNo] => [1] => [FirstName] => [2] => [LastName] => [3] => The Mother Redcap [Venue] => The Mother Redcap [4] => 0 [MemCard] => 0 [5] => 0 [EarlyReg] => 0 [6] => 2 [Venue_Points] => 2 [7] => 2014-07-09 [Date] => 2014-07-09 [8] => 1 [MembershipCount] => 1 ) [1] => Array ( [MembershipNo] => 0000000 [1] => Jason [FirstName] => Jason [2] => King [LastName] => King [3] => The Dolphin [Venue] => The Dolphin [4] => 1 [MemCard] => 1 [5] => 1 [EarlyReg] => 1 [6] => 32 [Venue_Points] => 32 [7] => 2014-08-27 [Date] => 2014-08-27 [8] => 3 [MembershipCount] => 3 ) I'm wondering if it's the difference in the way the data is stored in the array that is causing the issues later in the page! I will understand if you've run out of patience and don't want to offer any more advice and apologise for asking the same questions as previously.

Ok quick update, the two queries both produce the same results (As you knew they would) from: print_r(array_values($arr)); So they are both holding the same data, but for some reason the rest of my page doesn't like the data from query two! The question is why? more investigating....

Ok I think I understand whats happening with this one and it works as expected when I swap it out for the old one. This I don't understand so much! And if I replace the previous with this one everything breaks!! No errors as such, just wildly incorrect results (from those I would expect to see). I think this could well be caused by the later calculations though, so I'm working through it all to see if I can figure it out

Hi Barand, you've been incredibly helpful already, but I suspect that with your knowledge this whole thing could be much more easily and accurately constructed. I've attached the full page script as it is currently for you to cast your expert eye over, if you fancy taking a look and offering some more of your expert advice I'd be extremely greatful. I don't expect you to but it's attached if you fancy a challenge!! This is the entire file, it is an include that I drop into the final page. <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 // Make a MySQL Connection include 'includes/open_db.php'; include 'includes/DateSet.php'; include 'venue_averages2.php'; //mySQL queries $top40 = "SELECT Player.MembershipNo , Player.FirstName , Player.LastName , Venue.VenueName as Venue , Results.MemCard , Results.EarlyReg , Position.Points as Venue_Points , Results.Date , num.MembershipCount FROM Player INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo INNER JOIN Position ON Results.Position = Position.Position INNER JOIN Venue ON Venue.VenueID = Results.VenueID INNER JOIN ( SELECT MembershipNo , COUNT(*) as MembershipCount FROM Results WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30' GROUP BY MembershipNo ) num ON Player.MembershipNo = num.MembershipNo WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30' ORDER BY Player.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 $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>Played</strong></td> <td height="35" width="65" align="center" ><strong>Total Played</strong></td> <td height="35" width="65" align="center" ><strong>Weighted<br/>Qualifying<br/>Points</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 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], 9 => $r[9]); //print("- $r[1] $r[2] $r[3] $r[4] $r[5] $r[6] $r[7] **$r[8]**- <br/>"); // 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, [8]TotalGamesPlayed } 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 } 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[3] $row2[4] $row2[5] $row2[6] $row2[7] $row2[8] **$row2[9]**- <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] -- <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] # <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 = ""; } 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>" . $row3[7] . "</td> <td align=\"center\" $style>" . $r[9] . "</td> <td align=\"center\" $style>" . $row3[8] . "</td> </tr>"; $pos++; $printedFirst = $row3[1].$row3[2]; } } //And close the table. echo "</table>"; //And close the table. echo "</table>"; ?> <p> </p> </div></div> </div> And if your interested, the database tables used have the following structure: +--------------+--+----------+--+--------------+--+------------------+ | Player | | Position | | Results | | Venue | +--------------+--+----------+--+--------------+--+------------------+ | MembershipNo | | Position | | ResultID | | VenueID | | FirstName | | Points | | VenueID | | active | | LastName | | | | MembershipNo | | MinCharge | | OnlineAlias | | | | Date | | PerPlayer | | Address1 | | | | Position | | VenueName | | Address2 | | | | MemCard | | Address1 | | Town | | | | EarlyReg | | Address2 | | County | | | | | | Town | | Postcode | | | | | | County | | Email | | | | | | Postcode | | | | | | | | TelNo | | | | | | | | FaxNo | | | | | | | | Email | | | | | | | | Web | | | | | | | | GameNight1 | | | | | | | | GameTime1 | | | | | | | | GameNight2 | | | | | | | | GameTime2 | | | | | | | | ContactFirstName | | | | | | | | ContactLastName | | | | | | | | ContactMobile | +--------------+--+----------+--+--------------+--+------------------+

The honest answer... I've no idea! I didn't create this script I'm just trying to bend it to make it for new requirements. I'm not very good at php but I'm trying! The first query is broken up, sorted has calculations performed on it in lots of ways before becoming the second array. The $r[9] value is something that's just been added, it doesn't get messed with in the same way as the rest of the values, which is why I want to access it directly, I'm certain the whole thing could be much more efficiently calculated but I'm not sure I'm willing to take that leap just yet!

I have 2 arrays, this is the first: while($r = mysql_fetch_array($result)){ $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], 9 => $r[9]); } And this is the second: foreach ($arrGROUPED as $key => $row3) { 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]); } $printedFirst = $row3[1].$row3[2]; } I'm trying to print a results table using the following: 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>" . $row3[7] . "</td> <td align=\"center\" $style>" . $r[9] . "</td> <td align=\"center\" $style>" . $row3[8] . "</td> </tr>"; And all of the data shows perfectly EXCEPT the second last line where I try to print $r[9]. I think this is because I need to associate $r[9] from the first array with the appropriate record in the second array. But I can't figure out how to do that! In plain English I want to print $r[9] where $r[1], $r[2], $r[3] from the first array are equal to $row3[1], $row3[2], $row3[3] from the second array. I've got no idea how to go about it, so any help would be great

Hi Barand, Thank you so much! I see now where I was going wrong, I wasn't using the correct JOIN Syntax (as you correctly suspected), I've added a slight amendment to limit the MembershipCount to the same date range, your version counts all games over all time, but I need it limited to the same time period as the reported games, I suspect that is far more likely to be bad explanation on my part than anything else though This is the one line update: SELECT Player.MembershipNo , num.MembershipCount , Player.FirstName , Player.LastName , Venue.VenueName as Venue , Results.MemCard , Results.EarlyReg , Position.Points as Venue_Points , Results.Date FROM Player INNER JOIN Results ON Player.MembershipNo = Results.MembershipNo INNER JOIN Position ON Results.Position = Position.Position INNER JOIN Venue ON Venue.VenueID = Results.VenueID INNER JOIN ( SELECT MembershipNo , COUNT(*) as MembershipCount FROM Results WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30' GROUP BY MembershipNo ) num ON Player.MembershipNo = num.MembershipNo WHERE Results.Date BETWEEN '2014-07-01' AND '2014-09-30' ORDER BY Player.MembershipNo, Venue Thanks you so much, I've been battling with this for over a week now. It always looks so simple when it's finally working!!

Hi Barand, Thanks for the link, I've had a close look at it and tried to make it work for my situation, but I'm just getting errors around the field names, I was hoping this would be and easy additional to the existing query, but it's looking like that wont be the case! Any chance you could expand a bit on how I can get that to work in my situation please, feel free to over simplify because I'm not really sure what I'm doing at all here!!! Thanks a lot

First up quick thanks in advance, any and all help is greatfully recieved I have this query: 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 '2014-07-01' AND '2014-09-30' ORDER BY MembershipNo, Venue which returns these results: +--------------+-----------+----------+-------------------+---------+----------+--------------+------------+ | MembershipNo | FirstName | LastName | Venue | MemCard | EarlyReg | Venue_Points | Date | +--------------+-----------+----------+-------------------+---------+----------+--------------+------------+ | 0 | Bob | Stevens | The Dolphin | 1 | 1 | 32 | 27/08/2014 | | 0 | Bob | Stevens | The Enigma Tavern | 1 | 1 | 40 | 08/07/2014 | | 0 | Bob | Stevens | The Enigma Tavern | 1 | 1 | 16 | 15/07/2014 | | 1 | Dave | Green | The Dolphin | 1 | 1 | 20 | 13/08/2014 | | 1 | Dave | Green | The Dolphin | 1 | 1 | 2 | 20/08/2014 | +--------------+-----------+----------+-------------------+---------+----------+--------------+------------+ I would like to ALSO see the total number of times a membership number appears in the results table, not a COUNT DISTINCT because that would give me this: +--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+ | MembershipNo | MembershipCount | FirstName | LastName | Venue | MemCard | EarlyReg | Venue_Points | Date | +--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+ | 0 | 3 | Bob | Stevens | The Dolphin | 1 | 1 | 32 | 27/08/2014 | | 1 | 2 | Dave | Green | The Dolphin | 1 | 1 | 20 | 13/08/2014 | +--------------+-----------------+-----------+----------+-------------+---------+----------+--------------+------------+ What I want is the original data with the Membership Count on EVERY line like this: +--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+ | MembershipNo | MembershipCount | FirstName | LastName | Venue | MemCard | EarlyReg | Venue_Points | Date | +--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+ | 0 | 3 | Bob | Stevens | The Dolphin | 1 | 1 | 32 | 27/08/2014 | | 0 | 3 | Bob | Stevens | The Enigma Tavern | 1 | 1 | 40 | 08/07/2014 | | 0 | 3 | Bob | Stevens | The Enigma Tavern | 1 | 1 | 16 | 15/07/2014 | | 1 | 2 | Dave | Green | The Dolphin | 1 | 1 | 20 | 13/08/2014 | | 1 | 2 | Dave | Green | The Dolphin | 1 | 1 | 2 | 20/08/2014 | +--------------+-----------------+-----------+----------+-------------------+---------+----------+--------------+------------+ Any ideas how I can do this?

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!

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>

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

Ah got it (I think) thanks for the link

Wow - that does exactly what I asked! However, I wasn't clear in that the total number of games should only count those games played between the given date. So I've modified it like so: SELECT Player.MembershipNo, Player.FirstName, Player.LastName, Venue.VenueName AS Venue, Results.MemCard, Results.EarlyReg, Position.Points AS Venue_Points, Results.Date, tot.PlayerTotal 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 PlayerTotal FROM Results WHERE Results.Date BETWEEN '2014-01-01' AND '2015-01-01' GROUP BY MembershipNo ) AS tot ON Player.MembershipNo = tot.MembershipNo WHERE Results.Date BETWEEN '2014-01-01' AND '2015-01-01' ORDER BY MembershipNo, venue So my next question is that, although this now gives me exactly the result I want, have I achieved it in the correct way by adding the WHERE clause where I have? and my final question is, what is the advantage of having the inner joins, I've never really understood them properly Thank you for your time and effort I really appreciate you help and am really happy to have it working properly

There are more tables in the database, but these three are the ones used for getting results data: Player Results Venue