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

please don't use mysql_* functions, it's deprecated (see red box) and vulnerable to sql-injection. Use PDO or MySQLi. — Nir Alfasi
– Nir Alfasi, Commented Sep 2, 2012 at 17:12

bpatel · Accepted Answer · 2012-09-02 17:28:35Z

1

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']
    )
}

edited Sep 2, 2012 at 17:28

answered Sep 2, 2012 at 17:22

bpatel

3911 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Morten Gustafsson Over a year ago

Any suggestion how I do it with three queries?

bpatel Over a year ago

Do you have any hard requirement to output the data in the structure you mentioned in your Question? I think a better structure would to be to only have one entry per vehicle in the output array and then this single entry should contain all the damages and each damage will have all the associated Photos with it...like I had shown in the example code I provided.

Nir Alfasi · Accepted Answer · 2012-09-03 07:27:15Z

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
                        )

                )

        )

)

Nice... Now I only got one problem left. :) "link to damagesPhoto 03" also is in the array of photoOfDamages. But "link to damagesPhoto 03" is for another damages... If you see my array at the top of the screen the two first damages is the same an Exterior damages, but the third is an Intorier damages.
@MortenGustafsson then you can modify my code, instead of checking if($id === $row["id"]) you can add a condition to check if the damagesType fits as well.

Collectives™ on Stack Overflow

Making a 3d Array with PHP Mysql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related