如何在PHP中将SQL查询结果按实体合并为多维数组？

当前代码与结果 #

你当前使用的SQL查询及PHP代码如下：

$stmt= $conn->prepare("SELECT p.*, f.*
                        FROM fruit f
                        left JOIN person p ON f.fk_p_id = p.p_id
                        ");
$stmt->execute();
$result= $stmt->get_result();
while ($data= $result->fetch_assoc()) {
   $arr = array('name' => $data['name'], 'fruit' => $data['fruit']); // 修正原代码遗漏的$符号
   $props[] = $arr;
}

var_export($props);

得到的数组结果为：

array ( 
  0 => array ('name' => 'CHRISTIAN', 'fruit' => 'apple'),
  1 => array ('name' => 'CHRISTIAN', 'fruit' => 'pear'),
  2 => array ('name' => 'CHRISTIAN', 'fruit' => 'strawberry'),
  3 => array ('name' => 'CHRISTIAN', 'fruit' => 'banana'),
  4 => array ('name' => 'CHRISTIAN', 'fruit' => 'lime'),
  5 => array ('name' => 'JOSEF', 'fruit' => 'apple'),
  6 => array ('name' => 'JOSEF', 'fruit' => 'pear'),
  7 => array ('name' => 'BOB', 'fruit' => 'apple'),
  8 => array ('name' => 'BOB', 'fruit' => 'banana'),
)

你期望合并后的数组结构：

array (
  0 => array ('name' => 'CHRISTIAN', 'fruit' => ['apple', 'pear', 'strawberry', 'banana', 'lime']),
  1 => array ('name' => 'JOSEF', 'fruit' => ['apple', 'pear']),
  2 => array ('name' => 'BOB', 'fruit' => ['apple', 'banana']),
)

最终要填充的目标表格：

解决方案 #

方法一：PHP端处理现有数组 #

修改循环逻辑，以用户名作为临时数组的键，将同一用户的水果收集到数组中，最后转换为索引数组得到目标结构：

$stmt= $conn->prepare("SELECT p.*, f.*
                        FROM fruit f
                        left JOIN person p ON f.fk_p_id = p.p_id
                        ");
$stmt->execute();
$result= $stmt->get_result();

$temp = [];
while ($data= $result->fetch_assoc()) {
    $name = $data['name'];
    // 初始化用户数据
    if (!isset($temp[$name])) {
        $temp[$name] = [
            'name' => $name,
            'fruit' => []
        ];
    }
    // 追加当前水果到用户列表
    $temp[$name]['fruit'][] = $data['fruit'];
}

// 转换为索引数组
$props = array_values($temp);
var_export($props);

方法二：SQL层面直接分组（更高效） #

利用SQL的GROUP_CONCAT函数直接按用户分组，减少PHP端的处理压力：

// 修改SQL查询语句
$stmt= $conn->prepare("SELECT p.name, GROUP_CONCAT(f.fruit SEPARATOR ',') AS fruits
                        FROM fruit f
                        LEFT JOIN person p ON f.fk_p_id = p.p_id
                        GROUP BY p.p_id, p.name");
$stmt->execute();
$result= $stmt->get_result();

$props = [];
while ($data= $result->fetch_assoc()) {
    $props[] = [
        'name' => $data['name'],
        'fruit' => explode(',', $data['fruits']) // 将拼接字符串转为数组
    ];
}

var_export($props);

用处理后的数组生成目标表格 #

假设已得到合并后的$props数组，先定义所有水果列（也可从数据库查询不重复水果获取），再循环生成表格：

// 定义所有水果列
$allFruits = ['apple', 'pear', 'strawberry', 'banana', 'lime'];

// 输出表格结构
echo '<table class="s-table">';
echo '<thead><tr><th></th>';
foreach ($allFruits as $fruit) {
    echo "<th>$fruit</th>";
}
echo '</tr></thead><tbody>';

// 输出用户行数据
foreach ($props as $user) {
    echo "<tr><td>{$user['name']}</td>";
    foreach ($allFruits as $fruit) {
        // 判断用户是否拥有该水果，输出x或空单元格
        $display = in_array($fruit, $user['fruit']) ? 'x' : '';
        echo "<td>$display</td>";
    }
    echo '</tr>';
}

echo '</tbody></table>';

内容的提问来源于stack exchange，提问作者Zarian71