Combine 2 elements from 2 different arrays to same record

PHP_Idiot · September 24, 2014

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

ginerjm · September 24, 2014

I have no idea what you are trying to do here but your methods seem bizarre. You take one array and make another out of it - what was wrong with using the first array in both cases?

PHP_Idiot · September 25, 2014

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!

ginerjm · September 25, 2014

good luck

Barand · September 25, 2014

The creation of your first array can be reduced to

while($r = mysql_fetch_array($result)){
    unset($r[0]);    // you don't use that field
    $arr[] =  $r;
}

However, as you later want to match on a the value $r[1].$r[2].$r[3] then use that as the array key, so you would have

while($r = mysql_fetch_array($result)){
    unset($r[0]);    // you don't use that field
    $key = $r[1].$r[2].$r[3];
    $arr[$key] =  $r;
}

You can now easily get the required matcthing $r[9] value using

$val = $arr[$row3[1].$row3[2].$row3[3]][9];

PS I don't know the rest of your code but all this would probably be better achieved by using a JOIN in your db query i the first place

Edited September 25, 2014 by Barand

PHP_Idiot · September 25, 2014

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    |
+--------------+--+----------+--+--------------+--+------------------+

PHP_Idiot · September 25, 2014

The creation of your first array can be reduced to
while($r = mysql_fetch_array($result)){
    unset($r[0]);    // you don't use that field
    $arr[] =  $r;
}

Ok I think I understand whats happening with this one and it works as expected when I swap it out for the old one.

However, as you later want to match on a the value $r[1].$r[2].$r[3] then use that as the array key, so you would have
while($r = mysql_fetch_array($result)){
unset($r[0]); // you don't use that field
$key = $r[1].$r[2].$r[3];
$arr[$key] = $r;
}

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

PHP_Idiot · September 25, 2014

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....

Barand · September 25, 2014

... 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!!

I experienced a feeling of deja vu when looking at your code.

If the questions you are asking in this post were not the same ones that you asked (and I and others answered) at the end of January this year I might be inclined to help further, but I fear I would be wasting my time, again

PHP_Idiot · September 26, 2014

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.

Sign In

Combine 2 elements from 2 different arrays to same record

Recommended Posts

PHP_Idiot

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information