我希望你能帮助我解决这个问题。
假设我有3个数据库表:
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;
}
但这是不正确的。有人知道如何解决这个问题并得到上述数组(考虑性能)吗?我不想硬编码“cars”是可能会多次发生的异常情况。我更希望有一些东西只需查看 $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'
)
)
)
)
感谢所有帮忙的人 : )
等等还有更多..
[cars]
数组)我只是对某些 Web 服务感到恼火,因为它们有时将特定变量作为单个字符串或数组返回,而在另一个结果集中则作为数组的数组返回。因此,我需要在客户端检查每个值是否有更多的子项。如果您拥有一个数据结构,它在不同的结果集中没有改变,那么再次阅读数据将会更容易。因此,您的[cars]
数组应始终包含任何汽车的“父”数组,即使只有一个条目。 - feeela(array) new StdClass()
也会返回一个数组。但你得到的不是包含单个对象的数组 (array( new StdClass() )
),而是对象成员的数组。 - feeela