[RESOLVED] foreach? list?

robkir

I just don't get arrays. Its as simple as that. And not for want of trying--I've read like a million pages but I still can't figure it out.

My database contains userID, division, position. Division would be something like 10B or 14G. Position would be one of two possibilities "center ref" or "line ref". I am trying to count up each referees total games and the subset of how many games were centered and how many games were on the line by division. So the output would eventually be:

robkir 10B 2 centers 4 lines
robkir 14G 3 centers 7 lines

I put the records into $totGames[$k]{$j] and through print_r can see that it is accumulating the information correctly My problem is trying to get the data out of the array. I can't get a foreach or list to work to save my life.

$k represents the user ID
$j represents the position (center or line)

Thanks

while($row = mysql_fetch_array($result))
  {

if ($row['userID'] == $old_userID)
  { 
    //echo "same name " . $row['userID'] . "<br />";
    $k=  $row['userID'];

    if($row['refC']==$row['userID'] AND $row['division'] =='8B')
    {$j ='8B Center'; $totGames[$k][$j]++;}
    else if($row['refC']==$row['userID'] AND $row['division'] =='10B')
    {$j ='10B Center'; $totGames[$k][$j]++;}
    else if($row['refC']==$row['userID'] AND $row['division'] =='12B')
    {$j ='12B Center'; $totGames[$k][$j]++;}
    else if($row['refC']==$row['userID'] AND $row['division'] =='12B')
    {$j ='14B Center'; $totGames[$k][$j]++;}

    else if($row['refL1']==$row['userID'] 
      OR $row['refL2']==$row['userID'] 
        AND $row['division'] =='10B')
    {$j ='10B Line'; $totGames[$k][$j]++;}
    else if($row['refL1']==$row['userID'] 
      OR $row['refL2']==$row['userID'] 
        AND $row['division'] =='10G')
    {$j ='10G Line'; $totGames[$k][$j]++;}
   }

I've tried a number of variations of the foreach--obviously the one below doesn't work...

 foreach ($totGames as $key => $val) {
    print "$key = $val\n";

}

nawrik

Hi,

Is it possible for you to shae the results of the print_r ? you say that this is correctly displaying the data, in the correct format - this will help find a way to pull out that data.

I think there may also be an easier way to create the array in the first place, so any data before the while loop would be good - SQL query?

I've only just started to wrap my head around arrays and one of the biggest achievements was gained by calling the 'key' and values' by relevant names.

I look forward to seeing the output.

robkir

Hi nawrick,

I appreciate any thoughts you might have. Here's the print_r . The number [9] for example is the user ID, 10G Line is the position and the number =>3 represents the quantity of games completed.

[9] => Array
(
[10G Line] => 1
)

[13] => Array
    (
        [12B Center] => 2
        [10B Line] => 3
    )

[19] => Array
    (
        [10B Line] => 3
        [12B Center] => 3
    )

[24] => Array
    (
        [10B Line] => 2
    )

[27] => Array
    (
        [12B Center] => 2
        [10B Line] => 1
    )

nawrik

Looking at what you are trying to achieve:

robkir 10B 2 centers 4 lines

I'm not 100% on exactly what you are trying to 'print':
Using the above line, is this correct? Can you confirm?

robkir has refereed 6 games in division 10B, 2 of which were center referee and 4 games as a linesman.

Is that what you are after? (not necessarily presented that way!)

If so, I think there will be a better way to fetch and store that initial array. Please provide the SQL query.

I look forward to helping.

robkir

You are correct:

robkir has refereed 6 games in division 10B, 2 of which were center referee and 4 games as a linesman.

The SQL query is

$result= mysql_query("SELECT ref_directory.userID, ref_directory.nameL, ref_directory.nameF, games.gameID, games.division, games.refC, games.refL1, games.refL2 
	FROM ref_directory
	JOIN games
	ON ref_directory.userID= games.refC OR  ref_directory.userID= games.refL1 OR  ref_directory.userID= games.refL2
order by userID, division, refC");

$totGames=array();

I am really curious to see what you come up with. I did fool around with the SQL, hoping I could sum and count...but wasn't able to figure it out.

nawrik

Some more questions!

Am I right in thinking that refL1 and refL2 in the games table are the two linesmen?

So if robkir was linesman 1 in a game and in the next linesman 2 your UserID would be stored in the correct column?

If this is the case, can I suggest separating your data even further and creating a 'division' table:

The table field (or columns):
division_id | division_name

...and the content:
1 | 10B

Separating the referee directory, the games, referee types and divisions will make it easier to manage and subsequently fetch and control that data.

So, if you do add the above fields the remaining two tables would like like this:

The ref_directory uses these fields:?

ref_id | ref_name | ref_surname
9 | rob | kir

and the games table:?

game_id | division | ref_center | ref_line1 | ref_line2
1 | 1 | 9 | 19 | 24

Using this database layout the games table becomes the 'heavy lifter' - as you can see each game gets a unique id, the unique division id from the new table is stored in division and for each type of referee the user id is stored - in this instance you were the center ref.
You could also add much more info to the games table - as this will be the one changing the most - maybe game_date, game_location, game_results etc...

Once you have this data, how you pull it out can also change. I would use the games table as the 'base' for any further queries - so simply pull everything out of this and store as an array:

$strSQL = 'SELECT * FROM games';
$rsData = mysql_query($strSQL);
while($row = mysql_fetch_assoc($rsData))
{
     $arrData[] = $row;
}

print_r($arrData);

Having access to all of this data is invaluable for creating as many data combinations as you can dream up.

I know this still doesn't answer your question of the foreach, but producing i've found that pumping out clean data is half the battle when building data sets.

bradgrafelman

First off, are you just displaying this summary information? In other words, do you need to retrieve all of the rows individually for any other purpose? If not, then you can easily let the SQL take care of the summing, e.g.:

SELECT userID, division,
       SUM(IF(position = 'center ref',1,0)) AS num_centers,
       SUM(IF(position = 'line ref',1,0)) AS num_lines
FROM myTable
GROUP BY userID, division

which will give you a result set with rows such as:

userID	division	num_centers	num_lines
-------------------------------------------------
robkir	10B		2			4
robkir	14G		3			7

nawrik

That previous reply has solidified the fact that I need start putting more effort into my SQL.

I had to tottle off and look some of it up, but I like it a lot.

Cheers!

robkir

Hi nawrik,

You really put some thought it into this. Thank you. Since so much of the working part of the program is tied up with tables as they are, I am a bit hesitant to rework them as per your suggestions. I still think I can get what I need out of the existing structure...and it is a once a year activity so probably not worth a major overhaul. I'm going to keep tinkering and any other suggestions would be most welcome.

robkir

Hi bradgrafelman,

You always come up the best stuff! I didn't know you could do an IF statement in a SELECT statement (but then...there's so much I don't know).

I like the approach, but I need to give you a bit more information.

There are two tables: games and ref_directory. Games has all the game oriented information, and ref_directory has an ID and the referees name. The specific fields in the games table needed to solve this question are 'division' refC, refL1 and refL2. Division contains a string such as 10B or 14G, refC, refL1 and refL2 contain the ID number that relates to the userID in the table ref_directory.

So...I'm not sure if your idea will work. I tried a variation..but, well. No dice. I also don't know how or if one can join on mulitiple targets (refC, refL1 and refL2's ID number all relate back to the userID in ref_directory).

SELECT ref_directory.userID, games.division,
SUM(IF(games.refC = 'ref_directory.userID',1,0)) AS num_centers,
SUM(IF(games.refL1= 'ref_directory.userID',1,0)) AS num_lines,
SUM(IF(games.refL2= 'ref_directory.userID',1,0)) AS num_lines2,
FROM games
JOIN ref_directory
GROUP BY userID, division

As always, thanks for the suggestions. And, if you have time, I'd really appreciate some help in printing the array in my original questions--even it if isn't the solution we use here.

nawrik

Hi robkir,

I completely know where you are coming from with arrays. I myself have only just started to grasp them, the below code is how I would print out the existing array:

Because the $arrTotalGames is a multidimensional array you need to use a foreach for every array you want access to.

The first foreach ($arrTotalGames) will execute the code first value in that array - so in my case below, it will loop three times, one for each of the userID's.

To gain a quick overview I always find it handy to 'echo' out the $key and $value - this helps me confirm what data is been used:

foreach($arrTotalGames as $key => $value)
{
echo $key;
echo $value;
}

this will echo each user id followed by 'Array'.

I then almost always change these to a name thats more useful $key becomes $userID and $value just $arrValue (so I remember that it is an array).

Now we know that every 'iUserId' is another array, we need to use another foreach loop - this time using $arrTotalGames[$iUseriD] as the array to loop through and of course processing the $key and $value of each 'row' in the $arrTotalGames[$iUseriD] array.

Then of course you can echo, printf, build new arrays or whatever with the data that gets outputted through each iteration of the foreach loops.

Hope this helps!

$arrTotalGames = array(
		'9' => array('10G Line' => 1),
		'13' => array('12B Center' => 2,'10B Line' => 3),
		'19' => array('10B Line' => 3,'12B Center' => 3)
		);

//echo '<pre>';
//print_r($arrTotalGames);
//echo '</pre>';

foreach($arrTotalGames as $iUserID => $arrValue)
{
	foreach($arrTotalGames[$iUserID] as $strDivision_Position => $iNumGames)
	{
		if($iNumGames == 0 || $iNumGames > 1)
		{
			$strNumGames = $iNumGames.' games';
		}
		else
		{
			$strNumGames = $iNumGames.' game';
		}
		echo 'Referee '.$iUserID.' has played '.$strNumGames.' in Division/Position '.$strDivision_Position.'<br />---------------<br />';
	}
}

robkir

Hi nawrik

Excellent. I built a couple of models to be sure I grasped the concept. Giving variables clear names was good advice. I liked your explanations--clear and easy to follow.

Thanks for staying with me on this. I really appreciate it.

I'm going to leave the thread open to see if bradgrafelman circles back around. I am really curious to see his solution, too.

Cheers,

robkir

bradgrafelman

robkir wrote:
I'm going to leave the thread open to see if bradgrafelman circles back around. I am really curious to see his solution, too.

My solution would be to normalize the database (something that should have been done long ago before any code was ever written 🙂) so that it's in a usable form - the SQL query would be quite simple in that case. As it stands now, there's no easy way to get the information into a usable form without a huge, messy SQL query.

robkir

I was pretty sure that was the answer, too.

Thanks for the confirmation.

robkir