Jump to content

PHP_Idiot

Members
  • Content Count

    131
  • Joined

  • Last visited

Community Reputation

1 Neutral

About PHP_Idiot

  • Rank
    Advanced Member

Profile Information

  • Gender
    Not Telling
  1. 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.
  2. 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....
  3. 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
  4. 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 | +--------------+--+----------+--+--------------+--+------------------+
  5. 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!
  6. 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
  7. 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!!
  8. 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
  9. 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?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.