[SOLVED] Making mysql result into multi-dimensional array

[themeatpuppet]

Hi!

 

This is my first post in this forum, I posted this question on another site but it's a bit slow and I wanted to try a new forum for a change. My php problem is 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_name=$mykingdom["kingdom_name"];
        $mykingdom_king = $mykingdom["kingdom_king"];
        $mykingdom_des    = $mykingdom["kingdom_description"];
    
        $castle_name=$mykingdom["castle_name"];
        if(empty($pn)||$pn!=$castle_name){
            $castle_guards = array();
            array_push($castle,$castle_name,$mykingdom["castle_description"]);
        }
        $guard_name=$mykingdom["guard_name"];
        if(empty($ln)||$ln!=$guard_name){
            array_push($castle_guards,$guard_name, $mykingdom["guard_weapon"]);
            array_push($castle,$castle_guards);
        }
        
        if(empty($pn)||$pn!=$castle_name)
            array_push($castles,$castle);
            
        $gn=$guard_name;
        $cn=$castle_name;
    } 

 

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 could maybe make a class to handle this as an object but the server only supports PHP 4, and this seems like its the only way to go. All help is greatly appreciated and Thanks for reading.

[btherl]

Hi!  Actually the method you are using for dup detection is what I use all the time.  I don't think there is any more elegant way.  Just remember that the final item will must be added manually following the loop!  Because the test against the previous item is not made for the final item, as there is nothing following it ..

 

while (get a row) {
  if (row != previous row) {
    add to array
  }
}
Add final row to array

 

Also I notice you have no "order by" in your sql.  That's essential if you're going to do duplicate detection like that.  The order by should match the structure you are building.

 

If you want the array to be associative, use something like $castles[$cn] = $castle instead of array_push().

 

Apart from that, the idea is sound, so I am sure you will get it working!

[themeatpuppet]

hey man thanks, that was fast!

 

For some reason, I always think there's a function that everyone knows but I when thinks don't go right. This is working just find, the associative array tip worked like a charm, thanks again!

 

[themeatpuppet]

I keep getting duplicates  , but I know what's the problem. It's in the array of kingdoms, I dont see anyway of telling php when both all the castles and the guards for those castles have been processed for that kingdom to be finally added to the array. Otherwise I get a duplicate kingdom array of arrays for every new guard!

 

	$select_all_kingdom="SELECT * FROM kingdoms, castles, castleguards WHERE kingdoms.kingdomId=$c AND castles.kingdomId=$c AND castles.castleId=castleguards.castleId";
$kingdom_selected = mysql_query($select_all_kingdom) or die('Query failed: ' . mysql_error());


// array to keep each castle
$castles = array();
// array to keep each info item for each castle
$i=0;
while($kingdom=mysql_fetch_assoc($kingdom_selected)) {
//for($i=0;$i<mysql_num_rows($kingdom_selected);$i++){
	//	$kingdom_id = $kingdom["kingdomId"];
	$kingdom_name=$kingdom["kingdomName"];
	$kingdom_prof = $kingdom["kingdomProf"];
	$kingdom_des	= $kingdom["kingdomDescription"];

	$castle_id=$kingdom["castleId"];
	// if castle has not entered before
	if(empty($prev_pid)||$prev_pid!=$castle_id){
		$castle = array();	
		$guards=array();
		$prev_pid=$castle_id;
		$castle['castle_name'] = $kingdom["castleName"];	
		$castle['castle_des'] = $kingdom["castleDescription"];
	}
	$guard_id=$kingdom["guardId"];

	if(empty($prev_lid)||isset($prev_lid)&&$prev_lid!=$guard_id) {
		$guard=array();
		$guard['guard_name']=$kingdom["guardName"];
		$guard['guard_weapon']=$kingdom["guardWeapon"];
		// array of castle_guard inserted to the end of 
		array_push($guards,$guard);
		$castle['castle_guards']=$guards;
		$prev_lid=$guard_id;
	}
	// on first ru,n previous castle doesn't exist
	if(empty($prev_proi)){
		echo '"prev proi empty castle pushed !!!"'.$i."\n\n";
		array_push($castles,$castle);
	}
	elseif(isset($prev_proi)&&$prev_proi!=$i){
		echo '"prev proi='.$prev_proi.'!=castles['.$i.']"'."\n\n";
		array_push($castles,$castle);

	}
	$prev_proi=$i;
	$i++;

}
print_r($castles);
echo '</pre>';
} 

 

im way over my head! help!

[btherl]

It looks to me like you are adding the castle to the castles array based on $prev_proi, which increments every time through the loop.  Instead, you should add the castle inside the first if statement.  In fact, you can do everything in there - Put guards in the castle, and put castle in the castle list.  Then you empty out the castle and guard lists and initialize them for the new castle (which is in the current loop iteration).

 

If (new castle) {

  Put guards in the castle

  Put castle in the castle list

  Initialize new castle and guard lists

}

 

if (new guard) {

  Put guard in the guard list

}

 

There's no need to put the guards in the castle when finding a new guard, only when finding a new castle.

[themeatpuppet]

Finally!

 

It still took me a lot of tweaking (including a neurotic spree of commenting each step of the process) even AFTER it all made sense, but I finally made it with your hel btheri thank you so much. I truly feel that I've stepped above a new level of both php programming knowledge and confidence after this one

 

Now, just to make sure, for the million cent question, is this code:

$select_all_kingdom="SELECT * FROM kingdoms, castles, castleguards WHERE kingdoms.kingdomId=$c AND castles.kingdomId=$c AND castles.castleId=castleLinks.castleId ORDER BY castles.castleId";
$kingdom_selected = mysql_query($select_all_kingdom) or die('Query failed: ' . mysql_error());
$castles = array();

while($kingdom=mysql_fetch_assoc($kingdom_selected)) {
	$kingdom_name=$kingdom["kingdomName"];
	$kingdom_king = $kingdom["kingdomKing"];
	$kingdom_des = $kingdom["kingdomDescription"];
	$castle_id=$kingdom["castleId"];
	$guard_id=$kingdom["guardId"];

	if(isset($prev_castle_id)&&$prev_castle_id!=$castle_id){
		$castle['castle_guards']=$guards;
		array_push($castles,$castle);

	}
	if(empty($prev_castle_id)||$prev_castle_id!=$castle_id){
		$guard=array();
		$guards=array();
		$guard['guard_name']=$kingdom["guardName"];
		$guard['guard_weapon']=$kingdom["guardWeapon"];

		array_push($guards,$guard);
		$prev_guard_id=$guard_id;
		$castle = array();
		$castle['castle_name'] = $kingdom["castleName"];	
		$castle['castle_des'] = $kingdom["castleDescription"];

		$prev_castle_id=$castle_id;

	}
	if(isset($prev_guard_id)&&$prev_guard_id!=$guard_id) {
		$guard=array();
		$guard['guard_name']=$kingdom["guardName"];
		$guard['guard_weapon']=$kingdom["guardWeapon"];
		array_push($guards,$guard);	
		$prev_guard_id=$guard_id;
	}
}
if(isset($guards)&&$castle){
	$castle['castle_guards']=$guards;
	array_push($castles,$castle);
}

all that much better than this one:

 

$select_castles ="SELECT * FROM castles WHERE kingdomId=".$k;
		$castles_selected = mysql_query($select_castles) or die(mysql_error());
		$castles = array();
		while($this_castle=mysql_fetch_assoc($castles_selected))
		{
		$castle=array();
			$castle_name = $this_castle['castleName'];
			$castle_des = $this_castle['castleDescription'];
			$castle_id = $this_castle['castleId'];

			$castle['castle_name']=$castle_name;
			$castle['castle_des']=$castle_des;

			$castle_guards = array();

			$select_guards ="SELECT * FROM castleguards WHERE castleId=".$castle_id;
			$guards_selected = mysql_query($select_guards) or die(mysql_error());

			while($guard=mysql_fetch_assoc($guards_selected)) {
				$castle_guard = array();
				$castle_guard['guard_name']=$guard['guardName'];
				$castle_guard['guard_weapon']=$guard['guardWeapons'];
				array_push($castle_guards,$castle_guard);		
			}
			$castle['castle_guards'] = $castle_guards;
			array_push($castles,$castle);
		}

???

 

 

[btherl]

Hmm.. benchmark them and see for yourself   It's honestly very difficult to tell by looking at code whether it'll be faster or not, unless there is some overwhelming factor.

 

The single large query probably has higher peak memory usage, but I would expect it to be faster.  The multiple queries will have lower peak memory usage but I expect them to be slower.  But I could be totally wrong.

 

If your database is hosted somewhere distant from your script, then the single query will be MUCH faster, but I assume the database is on the same machine or on a fast local network.

[themeatpuppet]

I guess in this case the question is how much of an effect does calling the mysql database have in the overall performance of the site, in one script the database is called for a single record every time it is needed while in the other it is only called a single time and the results are retrieved. As much as I test here it doesn't really seem to make a difference but I wonder if it does elsewhere.