Multiple MySQL table query with array_values()

[technowar]

Good day. Before anything else by the way, I have never written a php program this deep so please bear with me for my ignorance. So moving on, this is my code below :

 

<?php
$console = array();
$model = array();
$game = array();

$gameQuery = "SELECT * FROM consoleGame";
$gameResult = mysql_query($gameQuery) or die(mysql_error());

while ($row = mysql_fetch_assoc($gameResult)) {
if(!is_array($game[$row['modelId']])) {
$game[$row['modelId']] = array();
}

$game[$row['modelId']][$row['gameId']] = array(
	 'Game Name' => $row['gameName'],
	 'Game ID' => $row['gameId']);
}

$modelQuery = "SELECT * FROM consoleModel";
$modelResult = mysql_query($modelQuery) or die(mysql_error());

while ($row = mysql_fetch_assoc($modelResult)) {
if (!is_array($model[$row['consoleId']])) {
$model[$row['consoleId']] = array();
}

$model[$row['consoleId']][$row['modelId']] = array(
	 'Model Name' => $row['modelName'],
	 'Model ID' => $game[$row['modelId']]);


}

$consoleQuery = "SELECT * FROM consoleConsole";
$consoleResult = mysql_query($consoleQuery) or die(mysql_error());

while ($row = mysql_fetch_assoc($consoleResult)) {
if (!is_array($console[$row['consoleId']])) {
$console[$row['consoleId']] = array();
}

$console[$row['consoleId']] = array(
	 'Console Name' => $row['consoleName'],
	 'Console ID' => array_values($model[$row['consoleId']]));
}

$console = array_values($console);
echo json_encode($console);
?>

 

As you can see, the code above outputs $console as a JSON file which is what I really need. But the problem is that, whenever I add a new Console (eg SNES), it creates a Warning: array_values() expects parameter 1 to be array, null given - on line ''Console ID' => array_values($model[$row['consoleId']]));'. Can you tell me which part went wrong?

Edited February 8, 2013 by technowar

[Christian F.]

I think you need to re-examine the logic inside those loops of yours. Seeing as you've declared the variables as arrays earlier, you don't need to check them in the loops. Just add the indices, and their associated values (which may very well be another array-dimension).

 

As for your problem, I think it stems from the fact that you're first fetching the models of a console type, then you fetch the console type. In that you assume that all consoles already have a model set, which I reckon your newly added SNES doesn't.

In other words: You're putting the carriage in front of the horse a bit here. First fetch the console types, then fetch their models, and finally the games associated with each model. From least specific to most.

 

You should also be using JOINs in your queries so that you fetch all relevant information in a single query. Which would also make it a lot easier to work with, as you'd only need one loop and a couple of IF-checks

 

PS: I would also advice you to use either MySQLI or PDO for your scripts. The mysql_* library is old, outdated and hasn't been maintained for a while. As such it is deprecated, and on its way to be removed from PHP completely.

[technowar]

I will be on my way to MySQLI after I get to solve this. Thank you!

 

Anyways, I have made another experiment and I used LEFT JOIN.

 

<?PHP
$consoleQuery = 'SELECT * FROM consoleConsole '.
 'LEFT JOIN consoleModel '.
 'ON consoleConsole.consoleId = consoleModel.consoleId';


$consoleResult = mysql_query($consoleQuery);

$consoleFields = array_fill_keys(array(
 'consoleName',
 ), null);

$modelFields = array_fill_keys(array(
 'modelName',
 ), null);

$gameFields = array_fill_keys(array(
 'gameName',
 ), null);

$console = array();
$model = array();
$rowConsole = array();

while ($rowConsole = mysql_fetch_assoc($consoleResult, MYSQL_ASSOC)) {
 $consoleId = $rowConsole['consoleId'];
 $modelId = $row['modelId'];
 if (isset($console[$consoleId]['Information'])) {
  $console[$consoleId]['Information'][] = array_intersect_key($rowConsole, $modelFields);
 }

 else {
  $console[$consoleId] = array_intersect_key($rowConsole, $consoleFields);
  $console[$consoleId]['Information'] = array(array_intersect_key($rowConsole, $modelFields));
 }
}


$console = array_values($console);
echo json_encode($console);
?>

 

The problem with this is that, I have another 3rd table (consoleGame) that has a foreign key with consoleTable and I do not get to query it.

Edited February 8, 2013 by technowar