Create multi dimensional array from one-to-many mysql result

Question

I have three mysql tables: clients, cars and insurance. Each client can have multiple cars and each car cand have multiple insurance policies. Here's my query and a link to sqlfiddle:

SELECT
  a.*,
  b.id AS car_id, b.license_number, b.brand, b.model,
  c.id AS insurance_id, c.insurance_company, c.start_date, c.end_date, c.price
FROM clients a
LEFT JOIN cars b ON a.id = b.client_id
LEFT JOIN insurance c ON b.id = c.car_id

http://sqlfiddle.com/#!2/773a5/1

So for two clients where the first client has two cars and three insurance policies and the second one has no car and no insurance, the query will return 4 rows.

What I can't figure out is how to loop this query in order to return the following structure, while avoiding duplicate client entries.

Array
(
  [0] => Array
    (
      [id] => 1
      [first_name] => John
      [last_name] => Smith
      [cars] => Array
        (
          [id] => 1
          [license_number] => 'plate1'
          [brand] => 'BMW'
          [model] => 'E35'
          [insurance] => Array
            (
              [id] => 1
              [start_date] => '2015-02-10'
              [end_date] => '2016-02-10'
              [price] => '100'
            )

            (
              [id] => 2
              [start_date] => '2014-02-10'
              [end_date] => '2015-02-10'
              [price] => '50'
            )
        )

        (
          [id] => 2
          [license_number] => 'plate2'
          [brand] => 'VW'
          [model] => 'Golf'
          [insurance] => Array
            (
              [id] => 3
              [start_date] => '2015-02-10'
              [end_date] => '2016-02-10'
              [price] => '100'
            )
        )
    )

  [1] => Array
    (
      [id] => 1
      [first_name] => John
      [last_name] => Smith
      [cars] => NULL
    )
)

People ask this kind of question a lot. But its really simple: loops. — developerwjk
– developerwjk, Commented Feb 19, 2015 at 20:07

DevStan · Accepted Answer · 2015-02-19 20:41:38Z

3

It's quite easy. It is about using an assoc arrays. You could use id, car_id and insurance_id as array keys.

So assuming you got the result as fetched assoc rows stored in $rows variable. The simple loop will be:

$result = array();
foreach($rows as $row) {
    $id    = $row['ID'];
    $carId = $row['CAR_ID'];
    $insId = $row['INSURANCE_ID'];

    $result[$id]['first_name'] = $row['FIRST_NAME'];
    $result[$id]['last_name']  = $row['LAST_NAME'];

    if(!isset($result[$id]['cars'])) {
        $result[$id]['cars'] = array();
    }

    if($carId && !isset($result[$id]['cars'][$carId])) {
        $car = array(
            'id'             => $row['CAR_ID'],
            'license_number' => $row['LICENSE_NUMBER'],
            'brand'          => $row['BRAND'],
            'model'          => $row['MODEL'],
            'insurance'      => array()
        );
        $result[$id]['cars'][$carId] = $car;
    }

    if($insId && isset($result[$id]['cars'][$carId]) && !isset($result[$id]['cars'][$carId]['insurance'][$insId])) {
        $insurance = array(
            'id'         => $row['INSURANCE_ID'],
            'start_date' => $row['START_DATE'],
            'end_date'   => $row['END_DATE'],
            'price'      => $row['PRICE']
        );
        $result[$id]['cars'][$carId]['insurance'][$insId] = $insurance;
    }
}

answered Feb 19, 2015 at 20:41

DevStan

1425 bronze badges

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

2 Comments

Norbert Over a year ago

Is there a way to return the values (clients, cars, insurance) as arrays instead of objects and index the arrays numerically?

DevStan Over a year ago

@Norbert actually there are also an arrays in my example and yes it is not hard to make them numerical autoindexed rather than use own keys. You should just additionally iterate over already added items of cars and insurance and check correspond id values to make sure the item that you want to add is not yet exists.

Marc B · Accepted Answer · 2015-02-19 20:39:32Z

3

I'm not going to write out all of the necessary fields for you, but it really does come down to just a single fetch loop and some if() logic to build the sub-arrays:

$info = array()
while($row = fetch from db) {
    if(!isset($info[$row['userID']]) {
         $info[$row['userID']] = array(
              'first_name' => $row['first_name'],
              ...
              'cars' => array()
         );
    }
    if (!isset($info[$row['userID']]['cars'][$row['carID']]) {
         etc...
    }
}

For every sub-array you put in, you just need another if() test to initialize that subarray, then populate it from the $row you've fetched.

You'll naturally build up your structure as you iterate the rows.

answered Feb 19, 2015 at 20:39

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

Create multi dimensional array from one-to-many mysql result

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related