1

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 and 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" 
          ) 
  ) 
);

My code looks like this, but is not working the right way.

$result = mysql_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") or die(mysql_error());  

$rowsResult  = array();

while ($r = mysql_fetch_assoc($result))
    {
        $rowsResult  [] = $r;       
    }

mysql_free_result($result);

echo json_encode($rowsResult);
...

/Morten

Morten Gustafsson
  • 1,869
  • 2
  • 24
  • 34
  • Morten, using `mysql_*` is not secure and safe, and makes your code vulnerable to SQL injections, please read more here: http://stackoverflow.com/questions/11939226/sql-injections-and-adodb-library-general-php-website-security-with-examples – Ilia Ross Sep 13 '12 at 06:36

6 Answers6

4

As its not possible in mysql, so we can write a script which gives all information about specific vehicle and with that data, we can create an array. Refer following 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++;
}
Vinay
  • 2,564
  • 4
  • 26
  • 35
  • 1
    You have changed the SQL query in a way the OP did not ask for, it is likely that using `INNER JOIN` instead of `LEFT JOIN` will cause the result set to be missing some of the required data. Also, please do ensure that you *always* quote the keys of associative arrays. The fact that you write code like this tells me you develop on a server with error reporting either off or set very low, please ensure that your development server has `display_errors=1` and `error_reporting=E_ALL` set in php.ini. You also have a syntax error in the code above (line 10-11) – DaveRandom Aug 31 '12 at 07:49
  • @DaveRandom Yes I did some syntax errors and also updated accordingly. But I dont think so INNER JOIN will cause the result set. – Vinay Aug 31 '12 at 07:53
  • INNER JOIN will cause MySQL to not return rows that do not have any associated records in `damages` or `damagesPhotos`. I very much doubt this is what the OP wants. – DaveRandom Aug 31 '12 at 07:55
  • @DaveRandom May be you are right. He can replace only mysql query and get whatever output he wants. – Vinay Aug 31 '12 at 08:02
  • @Vinay I Still have a problem, please look at the bottom of the screen, Can You Help me With this? – Morten Gustafsson Sep 01 '12 at 19:51
2

You can't get a multidimensional array from mysql, it just gives you a two dimensional resultset. A number of column values for each row is all you can get. If you want it nested like that, you'll have to process the array after retrieving the results.

Something like this:

$vehicles = array();

while ($r = mysql_fetch_assoc($result))
{
    $vehicle['id'] = $r['id']; 
    ...
    $damage = array();
    $damage['damagesType'] = $r['damagesType'];
    ...
    $vehicle['damages'] = $damage;
    ...
    array_push($vehicles, $vehicle);      
}
echo json_encode($vehicles);
Asciiom
  • 9,867
  • 7
  • 38
  • 57
2

It is not possible to have MySQL do all the work for you here (well, it is possible to have it do most of the work, but don't) because a result set from MySQL - or any SQL driven database - can have exactly two dimensions. The first dimension is a collection of rows, and the second is a collection of columns for each row.

Based on the example target data structure, you have two choices:

  1. Get the initial result set (vehicle LEFT JOIN damages) and iterate over it, running queries to get the photos
  2. Join all tables together as you are currently doing and post-process it into the desired multidimensional structure.

It's a trade off - option 1 results in a lot more database traffic but retrieves no more than the required data, whereas option 2 reduces it to a single query with less database traffic, but retrieves more data than required because each photo row carries the entire vehicle's data with it.

Option 1 would be a prime candidate for using prepared statements, which is something you are unable to take advantage of using the mysql_* functions. You should switch to using PDO or MySQLi instead. I personally recommend PDO. There are numerous other reasons for doing this as well, including the fact that the MySQL extension will one day be removed from PHP, and the proper use of prepared statements can completely removed any risk of security problems resulting from SQL injection via user input.

I would probably recommend option 2.

DaveRandom
  • 87,921
  • 11
  • 154
  • 174
0

Thanks to Vinay, my array looks like this. Thats nice :)

[{"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"}
    }
    }
}, ...

/Morten

Morten Gustafsson
  • 1,869
  • 2
  • 24
  • 34
  • I have added new answer for same. Their are some issues with what you want to do (which is not possible). so I have discussed those issues in new answer. – Vinay Sep 03 '12 at 05:30
0

After solving you main problem. I am writing this for new problem. As I know, you cant assign values to same key in your array means you cant allow to have duplicate keys. Because How would you access the value you want if more than one can have the same key. That's why you can choose another way to do merge your value. Re-arrange your array in following way.

$key = "damagesPhotoUrl";
$value = "link to damagesPhoto 01";
$array[$key][] = $value;

you can then access it as:

echo $array[$key][0];
echo $array[$key][1];
Vinay
  • 2,564
  • 4
  • 26
  • 35
0

Its very late.... but if you using PDO then PDO::FETCH_COLUMN with PDO::FETCH_GROUP can make result in 3 dimensional array. I think its limited, I'm not very SQL guy, but saying "You can't get a multidimensional array from mysql...or any SQL driven database - can have exactly two dimensions" can be not quite true.

Sorry for my English and off topic (this question is about mysql, not pdo)

http://php.net/manual/en/pdostatement.fetchall.php

(pseudo array):

array(
  array(//group 1
    array(
      0=>item1,
      0=>item2...
      )
    array(
      0=>item1,
      0=>item2...
      )
    array(
      0=>item1,
      0=>item2...
      )
    array(
      0=>item1,
      0=>item2...
      )
  )...
  array(//group 2
    array(
      0=>item1,
      0=>item2...
      )
    array(
      0=>item1,
      0=>item2...
      )
   )...
)
koper
  • 1