Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question
1  
please don't use mysql_* functions, it's deprecated (see red box) and vulnerable to sql-injection. Use PDO or MySQLi. –  alfasin Sep 2 '12 at 17:12
add comment

2 Answers

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']
    )
}
share|improve this answer
    
It won't work - see my answer. –  alfasin Sep 2 '12 at 17:24
    
Any suggestion how I do it with three queries? –  Morten Gustafsson Sep 3 '12 at 12:52
    
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. –  bpatel Sep 3 '12 at 14:32
add comment

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
                        )

                )

        )

)
share|improve this answer
    
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. –  Morten Gustafsson Sep 3 '12 at 12:40
    
@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. –  alfasin Sep 3 '12 at 23:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.