Array with Keys and Values from mysql results

[bschultz]

I'm trying to get an array with keys and values from a while loop in mysql.  However, this only returns the last result...not a completed array

 

<?php
$sql3 = "SELECT game_id, sport, visitor, home FROM games WHERE `game_id` IN ($all_games_worked_by_this_official_this_year)";
$rs3 = mysql_query($sql3,$dbc);  
while ($row3 = mysql_fetch_assoc($rs3))  
{
$games_array['sport'] = $row3['sport'];
$games_array['visitor'] = $row3['visitor'];
$games_array['home'] = $row3['home'];
}
print_r($games_array);
?>

 

The result is this:

Array ( [sport] => bb_varsity [visitor] => First Round [home] => 8A Playoffs )

 

If I remove the keys...I get the full results:

 

<?php
$sql3 = "SELECT game_id, sport, visitor, home FROM games WHERE `game_id` IN ($all_games_worked_by_this_official_this_year)";
$rs3 = mysql_query($sql3,$dbc);  
while ($row3 = mysql_fetch_assoc($rs3))  
{
//$games_array['sport'] = $row3['sport'];
//$games_array['visitor'] = $row3['visitor'];
//$games_array['home'] = $row3['home'];
$games_array[] = $row3['sport'];
$games_array[] = $row3['visitor'];
$games_array[] = $row3['home'];
}

print_r($games_array);
?>

 

Returns this:

 

Array ( [0] => bb_varsity [1] => Littlefork [2] => Remer [3] => bb_varsity [4] => Cherry [5] => Remer [6] => bb_varsity [7] => Deer River [8] => Remer [9] => bb_varsity [10] => Lake of the Woods [11] => Walker [12] => sb_varsity [13] => Northern Freeze [14] => Bagley [15] => bb_varsity [16] => Park Rapids [17] => Bagley [18] => bb_varsity [19] => Bagley [20] => Kelliher [21] => bb_varsity [22] => Pine River Backus [23] => Kelliher [24] => sb_varsity [25] => Lake of the Woods [26] => Kelliher [27] => sb_varsity [28] => Laporte [29] => Pine River Backus [30] => bb_varsity [31] => Badger Greenbush Middle River [32] => Lake of the Woods [33] => sb_varsity [34] => Warroad [35] => Lake of the Woods [36] => sb_varsity [37] => Ada Borup [38] => Fosston [39] => bb_varsity [40] => Badger Greenbush Middle River [41] => Fosston [42] => bb_varsity [43] => Norman County East [44] => Fosston [45] => bb_varsity [46] => Waubun [47] => Fosston [48] => bb_varsity [49] => Hibbing [50] => International Falls [51] => sb_varsity [52] => Northwoods [53] => International Falls [54] => bb_varsity [55] => Thief River Falls [56] => Bemidji [57] => sb_varsity [58] => Detroit Lakes and Thief River Falls [59] => Bemidji [60] => sb_varsity [61] => Brainerd [62] => Bemidji [63] => sb_varsity [64] => Kelliher [65] => Blackduck [66] => bb_varsity [67] => Kelliher [68] => Blackduck [69] => bb_amateur [70] => Wolf Lake [71] => Walker Walleyes [72] => bb_amateur [73] => Midway [74] => Walker Walleyes [75] => bb_varsity [76] => 8AA Playoffs [77] => Park Rapids [78] => sb_varsity [79] => McGregor [80] => Remer [81] => sb_varsity [82] => Subway Classic [83] => Fosston [84] => bb_amateur [85] => Cass Lake [86] => Nevis North Stars [87] => bb_varsity [88] => TBA [89] => 8A Playoffs [90] => bb_varsity [91] => TBA [92] => 8A Playoffs [93] => bb_varsity [94] => First Round [95] => 8A Playoffs )

 

The second result is what I want (but without the keys).  Who can I make this work WITH the keys?  Once I get this working, I will probably need some help to count and sort the array...but we'll start with this.

 

Thanks!

[mrMarcus]

You have defined keys.  You are overwriting them with each iteration of the loop.

 

The reason your second code returns expected results is because the keys are incremental (e.g. 1, 2, 3...), and are not being redefined.

[mrMarcus]

Why do you want them in this array to begin with?  Perhaps we can find a more efficient method of doing... whatever it is you're doing.

[bschultz]

I am scheduling sports officials. 

 

I want to see how many (for instance) times an umpire is working baseball as opposed to softball (count keys for sport=baseball or sport=softball). 

 

Also, I don't want to schedule the same umpire to work the same team too often.  So, count keys=sport, keys=visitor (for each team that comes up as the visitor) and keys=home (for each team that comes up as the home team) and then I'll know how many times a certain umpire has seen a certain team (both at home and on the road).

 

Make sense?

[Drummin]

mrMarcus is correct.  But as you asked about building the array with the defined keys, try it this way.

<?php
$sql3 = "SELECT game_id, sport, visitor, home FROM games WHERE `game_id` IN ($all_games_worked_by_this_official_this_year)";
$rs3 = mysql_query($sql3,$dbc);  
while ($row3 = mysql_fetch_assoc($rs3))  
{
$games_array[]['sport'] = $row3['sport'];
$games_array[]['visitor'] = $row3['visitor'];
$games_array[]['home'] = $row3['home'];
}
print_r($games_array);
?>

 

The result should be primary keys (0,1,2 etc) with sub-keys.

 

You could also use the game_id as the primary key, which might be a better option assuming they are unique.

<?php
$sql3 = "SELECT game_id, sport, visitor, home FROM games WHERE `game_id` IN ($all_games_worked_by_this_official_this_year)";
$rs3 = mysql_query($sql3,$dbc);  
while ($row3 = mysql_fetch_assoc($rs3))  
{
$games_array[$row3['game_id']]['sport'] = $row3['sport'];
$games_array[$row3['game_id']]['visitor'] = $row3['visitor'];
$games_array[$row3['game_id']]['home'] = $row3['home'];
}
print_r($games_array);
?>

[mrMarcus]

Why don't you just adjust your SQL to pull the appropriate recordset instead?

 

Much easier to manipulate than a standard array.

[bschultz]

I don't know what the content of visitor and home are going to be...and I never will.  How would I adjust the select statement if I don't know what those are?

 

I have to go umpire a game (ironically!).  Thanks for the help...I'll be back tonight!

[Kays]

You can query for a mapping of: umpire -> game, count(home_game), count(visitor_game)

 

So in a sense, you will get entries like:

Umpire1, Game1, 50, 20

Umpire1, Game2, 0, 10

.

.

.

Umpire2, Game1, 20, 0

.

.

.

etc

[bschultz]

But, the value for home and visitor will be the names of teams...

 

For instance (games worked by umpire #6, who has worked five games)

 

#1 - key sport = baseball -- key visitor = Town A -- key home = Town B

#2 - key sport = baseball -- key visitor = Town C -- key home = Town A

#3 - key sport = softball -- key visitor = Town A -- key home = Town D

#4 - key sport = baseball -- key visitor = Town E -- key home = Town C

#5 - key sport = baseball -- key visitor = Town A -- key home = Town B

 

I need the results / count of how many key = baseball (which should be 4 in this case)

I need the results / count of how many key = baseball and EITHER key visitor or key home = TOWN A (which should be 3 in this case, games 1, 2 and 5 --- not game 3 because it's a softball game)

I need the results / count of how many key = baseball and EITHER key visitor or key home = TOWN B (which should be 2 in this case, games 1 and 5)

I need the results / count of how many key = baseball and EITHER key visitor or key home = TOWN C (which should be 2 in this case, games 2 and 4)

I need the results / count of how many key = softball and EITHER key visitor or key home = TOWN D (which should be 1 in this case, game 3)

I need the results / count of how many key = baseball and EITHER key visitor or key home = TOWN E (which should be 1 in this case, game 4)

 

And all of this needs to be dynamic.  I don't know the name of the sport...the name of the team for visitor or the name for the team that is the home team.

 

 

[bschultz]

Alright, after another day of Googling...

 

I've come up with this function.  It works as is...but it doesn't take into account what sport it is.  SO, if I have 2 values in the array for baseball with Team B, and 1 value for softball for Team B...it gives me a count of 3.  I need the count for each sport.

 

Any ideas on how to alter this function to accommodate what I'm looking for?

 

Thanks!

 

<?php
function array_icount_values($arr,$lower=false) {
     $arr2=array();
     if(!is_array($arr['0'])){$arr=array($arr);}
     foreach($arr as $k=> $v){
      foreach($v as $v2){
      if($lower==true) {$v2=strtolower($v2);}
      if(!isset($arr2[$v2])){
          $arr2[$v2]=1;
      }else{
           $arr2[$v2]++;
           }
    }
    }
    return $arr2;
}

$res = array_icount_values($games);
print_r($res);
?> 

 

This returns this:

 

Array ( [sb_varsity] => 11 [Lake of the Woods] => 4 [Kelliher] => 5 [bb_varsity] => 18 [Walker] => 1 [bagley] => 3 [Detroit Lakes and Thief River Falls] => 1 [bemidji] => 3 [Ada Borup] => 1 [Fosston] => 5 [Pine River Backus] => 2 [badger Greenbush Middle River] => 2 [bb_amateur] => 3 [Wolf Lake] => 1 [Walker Walleyes] => 2 [Northwoods] => 1 [international Falls] => 2 [blackduck] => 2 [Littlefork] => 1 [Remer] => 4 [Hibbing] => 1 [subway Classic] => 1 [Cherry] => 1 [brainerd] => 1 [Northern Freeze] => 1 [Norman County East] => 1 [Thief River Falls] => 1 [Laporte] => 1 [Deer River] => 1 [McGregor] => 1 [Warroad] => 1 [Cass Lake] => 1 [Nevis North Stars] => 1 [Waubun] => 1 [Park Rapids] => 2 [First Round] => 1 [8A Playoffs] => 3 [TBA] => 2 [8AA Playoffs] => 1 [Midway] => 1 )

[Oreo]

The easiest and most efficient way to get this information would be to query for it directly.  You can do this using aggregate and grouping functions in your query.

 

For example:

SELECT sport, COUNT(*) as occurrences FROM games GROUP BY sport

Will return one row per distinct sport along with the total number of rows with that same value for sport.  So something like:

sport    | occurrences
---------------------
baseball | 4
soccer   | 2
golf     | 6

 

The MySQL manual has more information on aggregate functions (ie: COUNT) and how GROUP BY works.

 

 

Your other queries are complicated by the fact that your design has two separate columns for town, but it's still possible to do:

    SELECT sport, town, SUM(occurrences) as occurrences FROM (
        SELECT sport, home AS town, COUNT(*) as occurrences FROM games GROUP BY sport, home
        UNION ALL
        SELECT sport, visitor AS town, COUNT(*) as occurrences FROM games GROUP BY sport, visitor
    ) AS t GROUP BY sport, town;

(note: not thoroughly tested)

 

This should give you something like:

sport    | town    | occurrences
baseball | Town A  | 5
baseball | Town B  | 2
softball | Town B  | 7

 

In addition to aggregate functions (SUM, COUNT), this query makes use of subqueries, derived tables and unions.