php select 关联,从MySQL链接表SELECT查询创建PHP多维关联数组

我希望有人可以帮我解决这个问题.

假设我有3个DB表：

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)

我想有代码返回一个多维PHP数组,如：

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'

)

)

)

)

我有以下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

和PHP

$result = mysqli_query($db, $q);

while ($row = mysqli_fetch_assoc($result)) {

$users_with_cars[$row['user_id']] = $row;

}

但这不正确.任何人都知道如何解决这个导致上面的数组(考虑到性能)？我宁愿不想硬编码“汽车”的例外情况,这种情况可能不止一次发生.我宁愿看一些只看$row和$users_with_cars的内容,当看到一些新值时,它会通过将旧值转换为数组来附加它.也许已经有一个原生的PHP功能呢？或者更好,也许我的MySQL或整个方法是错误的？

任何帮助或提示赞赏.

问候

更新已解决

这是一个更新,也许我可以帮助别人我最终如何解决它.

我最终总是使用一个或多个汽车的数组,我调整表格总是有一个“id”作为列名.这样您就可以轻松扩展它.见例子;

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;

}

这一切都导致了美丽的阵列：

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'

)

)

)

)

假设用户也可以拥有多辆自行车.然后,您有多个连接数组,您可以使用左连接轻松绑定并将其添加到连接数组.

$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);

会产生类似的结果

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'

)

)

)

)

等等..

谢谢大家帮忙:)