View Full Version : Making three linked tables into a 3 dimensional array

07-24-2007, 06:19 AM

I want to make the result of 3 tables linked through foreign keys into a 3-dimensional array that reflects their relationship without repetition and without having to call the database more three times. I know I'm doing a couple of things wrong, one thing for instance is initializing a variable at the end of the loop to remember the previous value s that it does not repeat itself, surely there must be a more elegant way to go about this, but I'm lost and I've been stuck on this all day.
here's the infamous loop:

$select_kcg = "SELECT * FROM kingdoms, castles, guards WHERE kingdoms.kingdomId=$mykingdom AND castles.kingdom_id=$mykingdom AND castles.castle_id=guards.castleId";
$mykingdom_selected = mysql_query($select_kcg) or die('Query failed: ' . mysql_error());

$castles = array();
$castle = array();

while($mykingdom=mysql_fetch_assoc($mykingdom_selected)) {
$mykingdom_king = $mykingdom["kingdom_king"];
$mykingdom_des = $mykingdom["kingdom_description"];

$castle_guards = array();
array_push($castle_guards,$guard_name, $mykingdom["guard_weapon"]);


Basically, I want to make the result of a query into an array like this one:

Array [0]=>Array ([0]=>"Castle Rock",[1]=>King Hendrix,[2]=>Made of Sand, [3]=>Array([0]=>Timothy,[1]=> Thomas,[2]=>Kurt)))
Array [1] ...

would be even better it were associative:

Array [0]=>Array (['castle_name']=>"Castle Rock",['castle_king']=>King Hendrix,['castle_description']=>Made of Sand, ['castle_guards']=>Array([0]=>Timothy,[1]=> Thomas,[2]=>Kurt)))
Array [1] ...

Now I would make a class to handle this as an objct but the server only supports PHP 4, and this seems like its the only way to go.All help is greatly appreciated, Thanks for reading !