What's Wrong WIth My Array?

[ShoeLace1291]

So I'm making a roster section for my clan's website.  I have two tables, one for networks(PC, Xbox Live, etc) and another for games(CS: Source, Halo, etc).  The games table is linked to the networks table so the games can be "grouped" by networks.  And I put the word grouped in quotations because I'm not actually grouping them in the queries.  I just run a query that gets the networks and in that loop I query the games table based on the networkID of the game row.  I am trying to store this information in an array so I can pass it through to another file.  The problem is within my array structure, because the page displays the correct results for the first network, but in the second network the games from the first network are displayed.  I hope I explained that clearly enough.  So here is my code:

 

$network_query = "
	SELECT *
	FROM ".DB_PREFIX."roster_networks
	ORDER BY title";
$network_query = mysql_query($network_query) or die(mysql_error());
if(mysql_num_rows($network_query) > 0){
	while($network=mysql_fetch_assoc($network_query)){
		$networks[$network['id']] = array(
			'id' => $network['id'],
			'title' => $network['title']
			);

		$games_query = "
			SELECT *
			FROM ".DB_PREFIX."roster_games
			WHERE networkID = ".$network['id']."
			ORDER BY title";
		$games_query = mysql_query($games_query) or die(mysql_error());
		if(mysql_num_rows($games_query) > 0){
			while($game=mysql_fetch_assoc($games_query)){
				$games[$game['id']] = array(
					'id' => $game['id'],
					'title' => $game['title'],
					'icon' => '',
					'record' => '',
					'members' => '',
					'status' => ''
					);
			}
		} else {
			$games = array();
		}
		$networks[$network['id']]['games'] = $games;
	}
} else {
	$networks = array();
}

 

I cannot figure out why this is happening.  Anyone have any ideas?

[Psycho]

Do not run queries in loops - it is horribly inefficient. You should run one query with a JOIN to get all the data. Also, don't use * in your SELECT statement unless you absolutely need all the fields. Just list out the fields you need. Lastly, I would recommend not giving fields in different tables the same name (unless one is a foreign key of the other). It makes it more difficult when you do need to use JOINs.

 

Anyway, the problem you are having is that when you process each "network" you are appending new records to the $games array and then assigning that value to the $network array. So, on the second network, you are adding those new games to the $games array (which still holds the records from the first network). Anyway, you are doing this wrong as it is and should be using a single query with a JOIN statement.

 

Thsi may have a typo or two, but should work as you need it

$query = "SELECT network.id as n_id, network.title as n__title,
                 games.id as g_id, games.title as g_title
          FROM ".DB_PREFIX."roster_networks AS network
          JOIN ".DB_PREFIX."roster_games AS games
            ON games.networkID = network.id
          ORDER BY network.title, games.title";
$result = mysql_query($query) or die(mysql_error());

$networks = array(); //Create the variable with default value
while($row = mysql_fetch_assoc($result))
{
    if(!isset($networks[$row['n_id']]))
    {
        //Network ID is different than last record
        $network_id = $row['n_id'];
        $networks[$network_id] = array();
        $networks[$network_id]['title'] = $row['n_title'];
        $networks[$network_id]['games'] = array();
    }
    //Add game data to current network index
    $game_id = $row['g_id'];
    $networks[$network_id]['games'][$game_id] = array(
            'id'      => $game_id,
            'title'   => $row['g_title'],
            'icon'    => '',
            'record'  => '',
            'members' => '',
            'status'  => ''
        );
}