MySQL多表数据查询与PHP结果格式化实现方法问询

现有数据库表结构 #

1. plants表（植物主表） #

ID (主键, 自增) | botanicalName      | quickCode
1           | Monstera adansonii | 1234567
2           | Aloe Vera          | 1233456

2. commonNames表（通用名称表） #

plantsID为plants表ID的外键

ID (主键, 自增) | plantsID | commonName     
1           | 1        | 瑞士奶酪藤
2           | 2        | 开普蕨
3           | 1        | 飓风草

3. images表（植物图片表） #

plantsID为plants表ID的外键

ID (主键, 自增) | plantsID | fileName    
1           | 1        | monstera_adansonii.jpg
2           | 2        | capefern.jpg
3           | 2        | capefern2.jpg

需求 #

编写MySQL查询语句及PHP代码，将查询结果格式化为如下数组结构（以ID=1的植物为例）：

array (
    'id' => 1, // plants表ID
    'botanicalName' => 'Monstera adansonii', // plants表学名
    'commonNames' => array ( 0 => '瑞士奶酪藤', 1 => '飓风草' ), // 关联的通用名称集合
    'fileName' => array ( 0 => 'monstera_adansonii.jpg' ) // 关联的图片文件名集合
)

解决方案 #

1. MySQL 查询语句 #

使用GROUP_CONCAT聚合关联字段，便于后续PHP处理：

SELECT 
    p.ID AS id,
    p.botanicalName,
    GROUP_CONCAT(DISTINCT cn.commonName SEPARATOR '|||') AS commonNamesStr,
    GROUP_CONCAT(DISTINCT i.fileName SEPARATOR '|||') AS fileNamesStr
FROM plants p
LEFT JOIN commonNames cn ON p.ID = cn.plantsID
LEFT JOIN images i ON p.ID = i.plantsID
GROUP BY p.ID, p.botanicalName;

选择|||作为分隔符，避免与名称、文件名中的常规字符冲突，确保拆分准确。

2. PHP 处理代码 #

通过PDO连接数据库，将聚合后的字符串拆分为目标数组结构：

// 数据库连接参数
$dsn = 'mysql:host=localhost;dbname=你的数据库名;charset=utf8mb4';
$username = '你的用户名';
$password = '你的密码';

try {
    // 初始化PDO连接
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 执行查询
    $stmt = $pdo->query("SELECT 
                            p.ID AS id,
                            p.botanicalName,
                            GROUP_CONCAT(DISTINCT cn.commonName SEPARATOR '|||') AS commonNamesStr,
                            GROUP_CONCAT(DISTINCT i.fileName SEPARATOR '|||') AS fileNamesStr
                         FROM plants p
                         LEFT JOIN commonNames cn ON p.ID = cn.plantsID
                         LEFT JOIN images i ON p.ID = i.plantsID
                         GROUP BY p.ID, p.botanicalName");

    $formattedResult = [];
    // 遍历查询结果并格式化
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        // 处理通用名称数组，空值时返回空数组
        $commonNames = !empty($row['commonNamesStr']) ? explode('|||', $row['commonNamesStr']) : [];
        // 处理图片文件名数组，空值时返回空数组
        $fileNames = !empty($row['fileNamesStr']) ? explode('|||', $row['fileNamesStr']) : [];

        $formattedResult[] = [
            'id' => $row['id'],
            'botanicalName' => $row['botanicalName'],
            'commonNames' => $commonNames,
            'fileName' => $fileNames
        ];
    }

    // 输出格式化后的结果
    print_r($formattedResult);
} catch (PDOException $e) {
    die("数据库操作失败: " . $e->getMessage());
}

关键说明 #

• 用LEFT JOIN保证即使植物没有关联的通用名称或图片，也能被正常查询到
• GROUP_CONCAT减少数据库返回的行数，降低PHP循环处理的开销
• 自定义分隔符避免拆分错误，同时处理空值情况，保证数组结构的一致性

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