Need PHP help: nested MySQL table

[bakakon]

---

naa koy table sa akngg mysql nga na ka foreign keys.akong ganahan buhaton ky akong ma combine ang tables dayn akng ma encode as JSON. so far, mao ra ni ako nabuhat

    $consoleQuery = 'SELECT * '.                    
        'FROM consoleConsole '.
            'JOIN consoleModel '.
                'ON consoleConsole.consoleId = consoleModel.consoleId '.
            'JOIN consoleGame '.
                'ON consoleModel.modelId = consoleGame.modelId';


    $consoleResult = mysql_query($consoleQuery);
    
    $consoleFields = array_fill_keys(array(
        'consoleName',
        ), null);
    
    $modelFields = array_fill_keys(array(
        'modelName',
        ), null);


    $console = 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);

ang result nya ky kini:

[
  {
    "consoleName": "PlayStation",
    "Information": [
      {
        "modelName": "PlayStation"
      },
      {
        "modelName": "PlayStation 2"
      },
      {
        "modelName": "PlayStation 3"
      },
      {
        "modelName": "PlayStation Portable"
      }
    ]
  },
  {
    "consoleName": "Wii",
    "Information": [
      {
        "modelName": "Wii"
      }
    ]
  },
  {
    "consoleName": "Xbox",
    "Information": [
      {
        "modelName": "Xbox"
      },
      {
        "modelName": "Xbox 360"
      }
    ]
  },
  {
    "consoleName": "SNES",
    "Information": [
      {
        "modelName": null
      }
    ]
  }
]

but wat I want to hapen is,

[
  {
    "Console": "PlayStation",
    "Information": [
      {
        "Model": "PlayStation 3",
        "Title": [
          {
            "Game": "007 Legends",
            "Publisher": "Electronic Arts"
          },
          {
            "Game": "Ace Combat: Assault Horizon",
            "Publisher": "Namco"
          }
        ]
      },
      {
        "Model": "PlayStation 2",
        "Title": [
          {
            "Game": "007: Agent of Fire",
            "Publisher": "Electronic Arts"
          },
          {
            "Game": "Ace Combat 4: Shattered Skies",
            "Publisher": "Namco"
          }
        ]
      },
      {
        "Model": "PlayStation 1",
        "Title": [
          {
            "Game": "007 Racing",
            "Publisher": "Electronic Arts"
          },
          {
            "Game": "Ace Combat",
            "Publisher": "Namco"
          }
        ]
      }
    ]
  },
  {
    "Console": "Wii",
    "Information": [
      {
        "Model": "Wii",
        "Title": [
          {
            "Game": "007: Quantum of Solace",
            "Publisher": "Activision"
          },
          {
            "Game": "AC/DC Live: Rock Band Track Rack",
            "Publisher": "MTV Games"
          }
        ]
      }
    ]
  },
  {
    "Console": "Xbox",
    "Information": [
      {
        "Model": "Xbox",
        "Title": [
          {
            "Game": "AFL",
            "Publisher": "Acclaim"
          },
          {
            "Game": "American Chopper",
            "Publisher": "Activision"
          }
        ]
      },
      {
        "Model": "Xbox 360",
        "Title": [
          {
            "Game": "AFL Live",
            "Publisher": "Electronic Arts"
          },
          {
            "Game": "Akai Katana Shin",
            "Publisher": "Cave"
          }
        ]
      }
    ]
  }
]

unsaon na pra mogawas ang title nga array ug naay data? akng query diay kwangan pa na ky libog au ang join.

[carmicael]

hatag daw image sa imong db relationships.

[bakakon]

sa phpmyadmin sql cli

	SELECT *
	FROM consoleConsole
		JOIN consoleModel
			ON consoleConsole.consoleId = consoleModel.consoleId
		JOIN consoleGame
			ON consoleModel.modelId = consoleGame.modelId

mao ra ni ako mkuha bos ky ala koy workbench pra erd.

[mike_believe]

workbench sa ERD? try DeZign...erd creator that'll make SQL SCHEMA pa..

Edited: The problem lies here

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));
}
}

STRUCTURE NIMO IS:
WHILE(something) {
IF (something) {

}

else (something) {


}
}

SHOULD BE:

WHILE (something) {
WHILE (something) {

IF (something) {

}
ELSE (something) {
}
}
}
SUGGESTION:
kung PHP ka, i-do while nlang na...or basin na na sa imong platform

[bakakon]

i think ok nko alay erd, d mn jd unta ko mo focus sa php ug mysql kaso pre-req mn pra sa android programing aku gitun-an gd. pde ptabang solve ani?