Making a 3d Array with PHP Mysql

Question

I want to create a Array with multidimension arrays from a database. The Database has 3 tables, one for vehicle, one for damages and one for damagesPhotos.

Table vehicle has two columns id and name

Table damages has four columns damagesID, vehicleID, damagesType and damagesDescription.

Table damagesPhotos has three columns damagesPhotoID, damagesID and damagesPhotoUrl

I need to combine thoose three columns into an array, that looks like this:

$vehicle = array( 
"id" => "somestring", 
"name" => "somestring", 
"damages" => array( 
    "damagesType" => "somestring", 
    "damagesDescription" => "somestring", 
    "photoOfDamages" => array( 
            "damagesPhotoUrl" => "somestring" 
    ) 
) 
); 

I'am using this code:

$query = "SELECT * from vehicle v LEFT JOIN damages d ON v.id = d.vehicleID LEFT JOIN   damagesPhotos p ON d.damagesID = p.damagesID WHERE d.damagesID = p.damagesID AND v.id = 1";

$result = mysql_query($query);

$i = 0;
$vehicle = array();
while($row = mysql_fetch_array($result)){
    $vehicle[$i] = array( 
        "id" => $row[id], 
        "name" => $row[name], 
        "damages" => array( 
            "damagesType" => $row[damagesType], 
            "damagesDescription" => $row[damagesDescription], 
            "photoOfDamages" => array( 
                    "damagesPhotoUrl" => $row[damagesPhotoUrl] 
        ) 
        )
  );
  $i++;
  }

And it returns this:

  [{"vehilceId":"1",
  "name":"AW55005",
  "damages":{
        "damagesType":"Exterior",
        "damagesDescription":"Rust",
        "photoOfDamages":{
              "damagesPhotoUrl":"link to damagesPhoto 01"
        }
  }
  },

  {"vehilceId":"1",
  "name":"AW55005",
  "damages":{
        "damagesType":"Exterior",
        "damagesDescription":"Rust",
        "photoOfDamages":{
              "damagesPhotoUrl":"link to damagesPhoto 02"
  }
  }
  },

  {"vehilceId":"1",
  "name":"AW55005",
  "damages":{
        "damagesType":"Interior",
        "damagesDescription":"Scratch",
        "photoOfDamages":{
              "damagesPhotoUrl":"link to damagesPhoto 03"
        }
  }
  }

But as you can see the first two objects are the same only the damagesPhotoUrl is different. How do I merge thoose two array so it will look like this:

{"vehilceId":"1",
"name":"AW55005",
"damages":{
    "damagesType":"Exterior",
    "damagesDescription":"Rust",
    "photoOfDamages":{
        {"damagesPhotoUrl":"link to damagesPhoto 01"},
        {"damagesPhotoUrl":"link to damagesPhoto 02"}
    }
}
}, ...

Thanks in advance.

/ Morten

Answer 1

The answer is that you can't do that:
what you want is something like:

"photoOfDamages":{
    {"damagesPhotoUrl":"link to damagesPhoto 01"},
    {"damagesPhotoUrl":"link to damagesPhoto 02"}
}

but if you try to add a value with the same key to an associative array, it will override the last value, meaning, if you'll create:

"photoOfDamages":{
    {"damagesPhotoUrl":"link to damagesPhoto 01"}
}

and try to add:

{"damagesPhotoUrl":"link to damagesPhoto 02"}

the result will be:

"photoOfDamages":{
    {"damagesPhotoUrl":"link to damagesPhoto 02"}
}

In order to work around that, what you have to do is change the key to have a running counter concatenated, something like:

"photoOfDamages":{
    {"damagesPhotoUrl-1":"link to damagesPhoto 01"},
    {"damagesPhotoUrl-2":"link to damagesPhoto 02"}
}

As for a code that does the work, the following is an example - in order to make it work for you, you should uncomment, the commented lines and comment the line with theforeach`
:

<?php

$rows = array(
    array(//vehicle 1 
            "id" => "somestring",
            "name" => "somestring",
            "damages" => array( 
                "damagesType" => "somestring", 
                "damagesDescription" => "somestring", 
                "photoOfDamages" => array( 
                            "damagesPhotoUrl" => "somestring1")
        )
    ),
     array(//vehicle 2
            "id" => "somestring",
            "name" => "somestring",
            "damages" => array( 
                "damagesType" => "somestring", 
                "damagesDescription" => "somestring", 
                "photoOfDamages" => array( 
                            "damagesPhotoUrl" => "somestring2")
        )
    )
);
$i = 0;
//$row = mysql_fetch_array($result);
//while($row){
foreach($rows as $row){ //this should be commented
    if($id === $row["id"]){
        $j++;
        $vehicle[$i]["damages"]["photoOfDamages"]["damagesPhotoUrl-$j"] = $row["damages"]["photoOfDamages"]["damagesPhotoUrl"];          
        continue;
    }
    $j = 1;
    $i++;
    $id = $row["id"];
    $vehicle[$i] = array( 
        "id" => $id, 
        "name" => $row[name], 
        "damages" => array( 
            "damagesType" => $row["damages"]["damagesType"], 
            "damagesDescription" => $row["damages"]["damagesDescription"], 
            "photoOfDamages" => array( 
                    "damagesPhotoUrl-$j" => $row["damages"]["photoOfDamages"]["damagesPhotoUrl"]  
            ) 
        )
  );
  //$row = mysql_fetch_array($result);      
}
print_r($vehicle);
?>

Output:

Array
(
    [1] => Array
        (
            [id] => somestring
            [name] => somestring
            [damages] => Array
                (
                    [damagesType] => somestring
                    [damagesDescription] => somestring
                    [photoOfDamages] => Array
                        (
                            [damagesPhotoUrl-1] => somestring1
                            [damagesPhotoUrl-2] => somestring2
                        )

                )

        )

)

Answer 2

Ideally, I would suggest you break your original query into three separate queries and build the associate array first processing results from "Vehicle" table then from using "damages" table and them from "damagesPhotos" table.

But if you still want to make only one query then this is how you do it, please note I have modified the code to support more than one damages per vehicle and more than one photo per damage:

$query = "SELECT * from vehicle v LEFT JOIN damages d ON v.id = d.vehicleID LEFT JOIN   damagesPhotos p ON d.damagesID = p.damagesID WHERE d.damagesID = p.damagesID AND v.id = 1";

$result = mysql_query($query);

$i = 0;
$vehicle = array();
while($row = mysql_fetch_array($result)){
    $id = $row['id'];
    $damageId  = $row['damageId'];
    $damagesPhotoId = $row['damagesId'];

    if (!isset($vehicle[$id])) {
        $vehicle[$id] =  array(
            'id' => $id,
            'name' => $row['name'],
            'damages' => array(
            )
        )
    }
    if (!isset($vehicle[$id]['damages'][$damageId])) {
        $vehicle[$id]['damages'][$damageId] = array(
            'damagesType' => $row['damagesType'],
            'damageDescription' = $row['damageDescription'],
            'photoOfDamages' => array ()
        )
    }
    $vehicle[$id]['damages'][$damageId]['photoOfDamages'][] = array(
        'damagesPhotoUrl' => $row['damagesPhotoUrl']
    )
}