title: Create PHP multidimension associative array from MySQL linked tables SELECT query
body: <p>I'm hoping someone could help me with this problem.</p>
  
  <p>Say I have 3 DB tables:</p>
  
  <pre><code>Users:
  user_id, user_name
  100, John
  101, Jessica
  
  Cars:
  car_id, car_name
  30, Corvette
  31, BMW
  
  
  UsersCars:
  user_id, car_id, car_colour
  100, 30, Red
  101, 30, Green
  101, 31, Green
  
  (so John got a red corvette and Jessica has a green Corvette and a BMW)
  </code></pre>
  
  <p>I would like to have code that returns a multidimensional PHP array something like:</p>
  
  <pre><code>Array
  (
      [100] =&gt; Array
      (
          [user_id] =&gt; 100
          [user_name] =&gt; John
          [cars] =&gt; Array
          (
              [car_id]=&gt;30,
              [car_name]=&gt;'Corvette',
              [car_colour]=&gt;'Red'
  
          )            
      )
      [101] =&gt; Array
      (
          [user_id] =&gt; 101
          [user_name] =&gt; Jessica
          [cars] =&gt; Array
          (
              [0] =&gt; Array
              (
                  [car_id]=&gt;30,
                  [car_name]=&gt;'Corvette',
                  [car_colour]=&gt;'Green'
              ),
              [1] =&gt; Array
              (
                  [car_id]=&gt;31,
                  [car_name]=&gt;'BMW',
                  [car_colour]=&gt;'Green'
              )
          )            
      )
  )
  </code></pre>
  
  <p>I have the following SQL</p>
  
  <pre><code>SELECT u.*, c.* FROM Users u
  LEFT JOIN UsersCars uc ON u.user_id = uc.user_id
  LEFT JOIN Cars c ON uc.car_id = c.car_id
  </code></pre>
  
  <p>And PHP</p>
  
  <pre><code>$result = mysqli_query($db, $q);
  while ($row = mysqli_fetch_assoc($result)) {
      $users_with_cars[$row['user_id']] = $row;
  }
  </code></pre>
  
  <p>But this isn't correct. Anyone knows how to solve this resulting the above array (with performance in mind)? I rather do not want to hard-coded the exception of "cars" being something that can happen more than once. I rather have something that just looks at $row and $users_with_cars and when seeing some new value, it appends it, by converting the old value into an array. Maybe there's already a native PHP function for this? Or better, maybe my MySQL or whole approach is wrong? </p>
  
  <p>Any help or tips appreciated.</p>
  
  <p>Regards</p>
  
  <hr>
  
  <p><strong>UPDATE SOLVED</strong></p>
  
  <p>Here's an update, maybe I can help someone else how I solved it eventually.</p>
  
  <p>I ended up with always using an array for one or more cars, and I adjusted the tables to always have an "id" as column name. This way you can easily expand it. See example;</p>
  
  <pre><code>Users:
  id, name
  100, John
  101, Jessica
  
  Cars:
  id, name
  30, Corvette
  31, BMW
  
  
  UsersCars:
  user_id, car_id, car_colour
  100, 30, Red
  101, 30, Green
  101, 31, Green
  
  
  
  $q = 'SELECT u.*, c.id as car_id, c.name as car_name, uc.colour as car_colour FROM Users u
  LEFT JOIN UsersCars uc ON u.id = uc.user_id
  LEFT JOIN Cars c ON uc.car_id = c.id';
  
  $result = mysqli_query($db, $q);
  while ($row = mysqli_fetch_assoc($result)) {
      $users_with_cars[] = $row;
  }
  $joins = array('cars' =&gt; array('car_id'=&gt;'id','car_name'=&gt;'name','car_colour'=&gt;'colour'));
  $users_with_cars = create_join_array($users_with_cars, $joins);
  
  print_r($users_with_cars);
  
  
  
  function create_join_array($rows, $joins){
      /* build associative multidimensional array with joined tables from query rows */
  
      foreach((array)$rows as $row){
          if (!isset($out[$row['id']])) {
              $out[$row['id']] = $row;
          }
  
          foreach($joins as $name =&gt; $item){
              unset($newitem);
              foreach($item as $field =&gt; $newfield){
                  unset($out[$row['id']][$field]);
                  if (!empty($row[$field]))
                      $newitem[$newfield] = $row[$field];
              }
              if (!empty($newitem))
                  $out[$row['id']][$name][$newitem[key($newitem)]] = $newitem;
          }
      }
  
      return $out;
  }
  </code></pre>
  
  <p>This all results in the beautiful array:</p>
  
  <pre><code>Array
  (
      [100] =&gt; Array
      (
          [id] =&gt; 100
          [name] =&gt; John
          [cars] =&gt; Array
          (
              [30] =&gt; Array
              (
                  [id]=&gt;30
                  [name]=&gt;'Corvette',
                  [colour]=&gt;'Red'
              )
          )            
      )
      [101] =&gt; Array
      (
          [id] =&gt; 101
          [name] =&gt; Jessica
          [cars] =&gt; Array
          (
              [30] =&gt; Array
              (
                  [id]=&gt;30,
                  [name]=&gt;'Corvette',
                  [colour]=&gt;'Green'
              ),
              [31] =&gt; Array
              (
                  [id]=&gt;31,
                  [name]=&gt;'BMW',
                  [colour]=&gt;'Green'
              )
          )            
      )
  )
  </code></pre>
  
  <p>Let's say the users also can have multiple bikes. Then, you have multiple join arrays, you can easily bind on with left joins and add it to the join array.</p>
  
  <pre><code>$q = 'SELECT u.*, c.id as car_id, c.name as car_name, uc.colour as car_colour, b.id as bike_id, b.name as bike_name FROM Users u
  LEFT JOIN UsersCars uc ON u.user_id = uc.user_id
  LEFT JOIN Cars c ON uc.car_id = c.id
  LEFT JOIN UsersBikes ub ON u.user_id = ub.user_id
  LEFT JOIN Bikes b ON ub.bike_id = b.id';
  
  $result = mysqli_query($db, $q);
  while ($row = mysqli_fetch_assoc($result)) {
      $users_with_cars_bikes[] = $row;
  }
  
  $joins = array('cars' =&gt; array('car_id'=&gt;'id', 'car_name'=&gt;'name', 'car_colour'=&gt;'colour'), 
                 'bikes' =&gt; array('bike_id'=&gt;'id', 'bike_name'=&gt;'name'));
  $users_with_cars_bikes = create_join_array($users_with_cars_bikes, $joins);
  
  print_r($users_with_cars_bikes);
  </code></pre>
  
  <p>Would result in something like</p>
  
  <pre><code>Array(
      [100] =&gt; Array
      (
          [id] =&gt; 100
          [name] =&gt; John
          [cars] =&gt; Array
          (
              [30] =&gt; Array
              (
                  [id]=&gt;30
                  [name]=&gt;'Corvette',
                  [colour]=&gt;'Red'
              )
          )
          [bikes] =&gt; Array
          (
              [41] =&gt; Array
              (
                  [id]=&gt;41
                  [name]=&gt;'BMX'
              )
          )           
      )
  )
  </code></pre>
  
  <p>and so on..</p>
  
  <p>Thanks all for helping out :)</p>