Creating multidimensional arrays from SQL table

[NotionCommotion]

I've got the following two tables and wish to create the following array.  What is the best way to create the array?  Thanks

1. Do the first query and loop through them using PHP, and then a prepared statement for the second?
2. Inner join the two tables, loop through them using PHP, and set an array if not currently set, and if set, just add to the array?
3. Inner join the two tables, and use GROUP_CONCAT and then explode()?  While I think this approach is slick, I don't want to use it due to the truncation limitation.
4. Something else?

 

Companies

1 Ford
3 General Motors
5 Chrysler

Cars

1 1 Mustang
2 1 Fusion
3 3 Corvette
4 3 Escalade
5 5 Charger
6 5 Challenger

$cars=[
    [ 'id'=>1, 'name'=>'Ford', 'cars'=>[ ['id'=>1, 'Mustang'], ['id'=>2, 'Fusion'] ] ],
    [ 'id'=>3, 'name'=>'General Motors', 'cars'=>[ ['id'=>3, 'Corvette'], ['id'=>4, 'Escalade'] ] ],
    [ 'id'=>5, 'name'=>'Chrysler', 'cars'=>[ ['id'=>5, 'Charger'], ['id'=>6, 'Challenger'] ] ]
];

[Barand]

try

$sql = "SELECT co.id
        , co.name
        , car.id
        , car.model
        FROM companytest co
            INNER JOIN cartest car ON co.id = car.make
        ORDER BY co.id, car.id";
$res = $db->query($sql);
$cars = [];
$prev = '';
while (list($coid, $coname, $cid, $model) = $res->fetch_row()) {
    if (!isset($cars[$coid])) {
        $cars[$coid] = ['id' => $coid, 'name' => $coname, 'cars' =>[] ];
    }
    $cars[$coid]['cars'][] = ['id' => $cid, 'model' => $model];
}

the data

mysql> select * from companytest;
+------+----------------+
| id   | name           |
+------+----------------+
|    1 | Ford           |
|    3 | General Motors |
|    5 | Chrysler       |
+------+----------------+
3 rows in set (0.00 sec)

mysql> select * from cartest;
+------+------+------------+
| id   | make | model      |
+------+------+------------+
|    1 |    1 | Mustang    |
|    2 |    1 | Fusion     |
|    3 |    3 | Corvette   |
|    4 |    3 | Escalade   |
|    5 |    5 | Charger    |
|    6 |    5 | Challenger |
+------+------+------------+
6 rows in set (0.00 sec)

Edited August 2, 2016 by Barand

[NotionCommotion]

Thanks Barand,

 

I too was thinking of my second approach, but wanted another opinion.

 

And thank you too for the implementation script.  I rarely use list(), but probably  should do so more often.

[Barand]

@requinix - It was tricky getting the timing just right

[requinix]

@requinix - It was tricky getting the timing just right

"Oh I see he's reading the topic. I know he'll reply to this so I'll just wait a bit... (refresh) No, he hasn't replied yet. I'll give him more time... (refresh) Still no... (refresh) Alright, it's been an hour, I have this reply ready so I'll just copy and paste it in here, and voila! there's my po-- DAMMIT"

 

context? I posted a reply just after Barand saying the same thing so I hid mine

[NotionCommotion]

context? I posted a reply just after Barand saying the same thing so I hid mine

 

Well, it is the thought that counts