PHP MySQL关联两表按ID将图片整合为数组的实现问题

我需要基于ID关联artcolumn表（字段：id、name、image、title）和artwork_images表（字段：a_id、art_work），目标是把同一ID对应的多张图片整合到单个数组中，示例格式如下：

art_column {id:66,name:Test2,title:Art2,image:null,art_work:[Penguins.jpg,Tulips.jpg]}

但当前的关联查询代码会因为多图片导致id、name、title、image字段重复，求实现目标格式的方法。现有代码如下：

$result = ("SELECT artcolumn.id,title, image,name,art_work FROM artcolumn JOIN artwork_images ON artwork_images.a_id = artcolumn.id") or die(mysqli_error());
$sql=mysqli_query($con,$result);

if (mysqli_num_rows($sql) > 0) {
    // looping through all results items node
    $response["artcolumn"] = array();
    while ($row = mysqli_fetch_array($sql,MYSQLI_ASSOC)) {
        // temp user array
        $news = array();
        $news["id"] = $row["id"];
       
        $news["name"] = $row["name"];
        $news["title"] = $row["title"];
        $news["image"] = $row["image"];
        
        $news["art_work"] = $row["art_work"];
     
        array_push($response["artcolumn"], $news);
    }
}

解决方案 #

方法一：PHP循环内合并重复项 #

通过ID作为标识，在遍历结果时判断是否已处理过该记录，已处理则追加图片，未处理则新建记录：

$result = "SELECT artcolumn.id, title, image, name, art_work FROM artcolumn JOIN artwork_images ON artwork_images.a_id = artcolumn.id";
$sql = mysqli_query($con, $result) or die(mysqli_error($con));

$response["artcolumn"] = array();
$tempRecords = array(); // 用ID做键临时存储已处理的记录

if (mysqli_num_rows($sql) > 0) {
    while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)) {
        $currentId = $row["id"];
        
        // 首次处理该ID，初始化基础数据和图片数组
        if (!isset($tempRecords[$currentId])) {
            $tempRecords[$currentId] = array(
                "id" => $currentId,
                "name" => $row["name"],
                "title" => $row["title"],
                "image" => $row["image"],
                "art_work" => array()
            );
        }
        // 将当前图片追加到对应ID的数组中
        $tempRecords[$currentId]["art_work"][] = $row["art_work"];
    }
    // 把临时数组转成最终的响应格式
    $response["artcolumn"] = array_values($tempRecords);
}

方法二：用MySQL的GROUP_CONCAT聚合图片 #

直接在SQL层面将同一ID的图片拼接成字符串，再在PHP中拆分为数组：

// 用GROUP_CONCAT把同一ID的art_work用逗号拼接
$result = "SELECT artcolumn.id, title, image, name, GROUP_CONCAT(art_work SEPARATOR ',') AS art_work 
           FROM artcolumn 
           JOIN artwork_images ON artwork_images.a_id = artcolumn.id 
           GROUP BY artcolumn.id, title, image, name";
$sql = mysqli_query($con, $result) or die(mysqli_error($con));

$response["artcolumn"] = array();

if (mysqli_num_rows($sql) > 0) {
    while ($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)) {
        $news = array(
            "id" => $row["id"],
            "name" => $row["name"],
            "title" => $row["title"],
            "image" => $row["image"],
            // 将拼接后的字符串拆分为数组
            "art_work" => explode(',', $row["art_work"])
        );
        array_push($response["artcolumn"], $news);
    }
}

注意：如果art_work字段本身可能包含逗号，需要更换一个不会出现的分隔符（比如|），同时修改SQL中的SEPARATOR参数和PHP中的explode分隔符。

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