All geek questions in one place

Create a PHP multicast associative array from MySQL related SELECT query tables

I hope someone can help me with this problem.

Let's say I have 3 DB tables:

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)

I would like to have code that returns a multidimensional PHP array like this:

 Array ( [100] => Array ( [user_id] => 100 [user_name] => John [cars] => Array ( [car_id]=>30, [car_name]=>'Corvette', [car_colour]=>'Red' ) ) [101] => Array ( [user_id] => 101 [user_name] => Jessica [cars] => Array ( [0] => Array ( [car_id]=>30, [car_name]=>'Corvette', [car_colour]=>'Green' ), [1] => Array ( [car_id]=>31, [car_name]=>'BMW', [car_colour]=>'Green' ) ) ) )

I have the following SQL

 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

And php

 $result = mysqli_query($db, $q); while ($row = mysqli_fetch_assoc($result)) { $users_with_cars[$row['user_id']] = $row; }

But this is not true. Does anyone know how to solve this problem by getting the above array (considering performance)? I rather do not want to hard code the exception of "cars" as something that can happen more than once. I rather have something that just looks at $ row and $ users_with_cars, and when you see some new value, it adds it, converting the old value to an array. Maybe there is already a built-in PHP function for this? Or is it better, maybe my MySQL or the whole approach is wrong?

Any help or advice appreciated.

Hello

UPDATE DECISION

Here's an update, maybe I can help someone else as I decided it in the end.

I ended up always using an array for one or more cars, and I adjusted the tables to always have an "id" as the column name. That way you can easily expand it. See an example:

 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' => array('car_id'=>'id','car_name'=>'name','car_colour'=>'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 => $item){ unset($newitem); foreach($item as $field => $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; }

All this leads to a beautiful array:

 Array ( [100] => Array ( [id] => 100 [name] => John [cars] => Array ( [30] => Array ( [id]=>30 [name]=>'Corvette', [colour]=>'Red' ) ) ) [101] => Array ( [id] => 101 [name] => Jessica [cars] => Array ( [30] => Array ( [id]=>30, [name]=>'Corvette', [colour]=>'Green' ), [31] => Array ( [id]=>31, [name]=>'BMW', [colour]=>'Green' ) ) ) )

Let's say users can also have multiple bikes. Then you have several arrays of connections, you can easily bind them to left connections and add them to the join array.

 $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' => array('car_id'=>'id', 'car_name'=>'name', 'car_colour'=>'colour'), 'bikes' => array('bike_id'=>'id', 'bike_name'=>'name')); $users_with_cars_bikes = create_join_array($users_with_cars_bikes, $joins); print_r($users_with_cars_bikes);

Something like

 Array( [100] => Array ( [id] => 100 [name] => John [cars] => Array ( [30] => Array ( [id]=>30 [name]=>'Corvette', [colour]=>'Red' ) ) [bikes] => Array ( [41] => Array ( [id]=>41 [name]=>'BMX' ) ) ) )

etc.

Thank you all for your help :)

+4
source share
2 answers

This is what I could come up with. It (probably) will create an array just like your desired result. Not sure if this will work, I wrote it here without testing. Let me know!:)

 $result = mysqli_query($db, $q); while ($row = mysqli_fetch_assoc($result)) { // Add user ID and name to the array $users_with_cars[$row['user_id']]['user_id'] = $row['user_id']; $users_with_cars[$row['user_id']]['user_name'] = $row['user_name']; // Check if this user has cars in the array. If not, this is the first car (see else) if(isset($users_with_cars[$row['user_id']]['cars'])) { // Check if there is exactly 1 car in the array if(count($users_with_cars[$row['user_id']]['cars']) == 1) { // If yes, put that car in a 'sub array' $users_with_cars[$row['user_id']]['cars'] = array(0 => $users_with_cars[$row['user_id']]['cars']); // Then add the new car $users_with_cars[$row['user_id']]['cars'][] = array('car_id' => $row['car_id'], 'car_name' => $row['car_name'], 'car_color' => $row['car_color']); } else { // It already has more than one car in the array. Just add it $users_with_cars[$row['user_id']]['cars'][] = array('car_id' => $row['car_id'], 'car_name' => $row['car_name'], 'car_color' => $row['car_color']); } } else { // Add a single car without 'sub array' $users_with_cars[$row['user_id']]['cars']['car_id'] = $row['car_id']; $users_with_cars[$row['user_id']]['cars']['car_name'] = $row['car_name']; $users_with_cars[$row['user_id']]['cars']['car_color'] = $row['car_color']; } }

EDIT:

This is an example that I mentioned in my comment:

 $result = mysqli_query($db, $q); while ($row = mysqli_fetch_assoc($result)) { $count = count($users_with_cars[$row['user_id']]['cars']); foreach($row AS $key => $value) { if(substr($key, 0, 4) == "car_") { // Single: $users_with_cars[$row['user_id']]['cars'][$key] = $value; // Multiple: $users_with_cars[$row['user_id']]['cars'][$count][$key] = $value; } } }
+1
source 
 $result = mysqli_query($db, $q); $users_with_cars = array(); while ($row = mysqli_fetch_assoc($result)) { if (!isset($users_with_cars[$row['user_id']])) { $user = array(); $user['user_id'] = $row['user_id']; $user['user_name'] = $row['user_name']; $user['cars'] = array(); $users_with_cars[$row['user_id']] = $user; } $car = array(); $car['car_id'] = $row['car_id']; $car['car_name'] = $row['car_name']; $car['car_colour'] = $row['car_colour']; $users_with_cars[$row['user_id']]['cars'] = $car; }
0
source

Source: https://habr.com/ru/post/1445076/

More articles:


All Articles