MySQL两表关联统计指定section下各machine记录数及PHP生成JSON需求

嘿，你已经有了初步的思路，但现有查询语句还差关键一步才能得到准确结果，我来帮你梳理修正，再给出PHP生成JSON的完整方案。

1. 修正SQL查询语句 #

你的原查询用了DISTINCT却缺少GROUP BY，这会导致统计结果混乱——我们需要按每个机器分组来单独计算关联记录数，正确的SQL写法如下：

SELECT 
    a.section, 
    a.machine, 
    COUNT(b.user) AS countX 
FROM machine_tbl AS a 
LEFT JOIN open_notification_tbl AS b 
    ON a.machine = b.machine 
WHERE a.section = ?
GROUP BY a.section, a.machine;

简单解释下关键逻辑：

• LEFT JOIN确保machine_tbl里的所有机器都会被返回，哪怕在open_notification_tbl里没有关联记录
• COUNT(b.user)会自动把无关联的记录计为0（因为无关联时b.user为NULL，COUNT会忽略NULL值）
• GROUP BY a.section, a.machine是核心：确保我们按指定section下的每个机器单独统计数量，原查询缺了这一步，结果会完全不对

2. PHP生成JSON的示例代码 #

这里提供两种常用的数据库操作方案，你可以根据自己的习惯选择：

方案一：使用PDO（推荐，更安全兼容） #

<?php
// 替换成你的数据库连接信息
$host = '你的数据库地址';
$dbname = '你的数据库名';
$username = '数据库用户名';
$password = '数据库密码';

try {
    // 建立PDO连接
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 可以动态传入section值，比如从GET/POST参数获取
    $targetSection = '2400-TWO-001';

    // 准备并执行查询
    $stmt = $pdo->prepare("SELECT a.section, a.machine, COUNT(b.user) AS countX FROM machine_tbl AS a LEFT JOIN open_notification_tbl AS b ON a.machine = b.machine WHERE a.section = ? GROUP BY a.section, a.machine");
    $stmt->execute([$targetSection]);

    // 获取所有结果
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 输出JSON格式数据
    header('Content-Type: application/json');
    echo json_encode($results, JSON_PRETTY_PRINT);

} catch(PDOException $e) {
    // 错误处理
    echo json_encode(['error' => '数据库查询失败: ' . $e->getMessage()]);
}
?>

方案二：使用mysqli #

<?php
// 替换成你的数据库连接信息
$host = '你的数据库地址';
$dbname = '你的数据库名';
$username = '数据库用户名';
$password = '数据库密码';

// 建立mysqli连接
$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
    die(json_encode(['error' => '数据库连接失败: ' . $conn->connect_error]));
}

$targetSection = '2400-TWO-001';
// 准备查询语句
$stmt = $conn->prepare("SELECT a.section, a.machine, COUNT(b.user) AS countX FROM machine_tbl AS a LEFT JOIN open_notification_tbl AS b ON a.machine = b.machine WHERE a.section = ? GROUP BY a.section, a.machine");
$stmt->bind_param("s", $targetSection);
$stmt->execute();
$result = $stmt->get_result();

// 整理结果数组
$results = [];
while ($row = $result->fetch_assoc()) {
    $results[] = $row;
}

// 输出JSON
header('Content-Type: application/json');
echo json_encode($results, JSON_PRETTY_PRINT);

// 关闭连接
$stmt->close();
$conn->close();
?>

3. 预期结果示例 #

比如查询2400-TWO-001时，返回的JSON会是这样：

[
    {
        "section": "2400-TWO-001",
        "machine": "AT-TWB-001",
        "countX": 3
    },
    {
        "section": "2400-TWO-001",
        "machine": "AT-TWB-002",
        "countX": 2
    },
    {
        "section": "2400-TWO-001",
        "machine": "AT-TWB-003",
        "countX": 1
    },
    {
        "section": "2400-TWO-001",
        "machine": "AT-TWB-004",
        "countX": 1
    }
]

如果某个机器没有关联通知记录（比如AT-TWB-007），查询对应section时它的countX会显示为0。

内容的提问来源于stack exchange，提问作者D.Madu