时间戳

首页 > 解决方案 > MySQL LEFT JOIN - 如何处理记录与 PHP 不匹配的情况

php - MySQL LEFT JOIN - 如何处理记录与 PHP 不匹配的情况

问题描述

我有一个 HTML 表，其中填充了从 MySQL 返回的记录LEFT JOIN。

这可行，但我需要处理在JOIN. 目前，HTML 表如下图所示，不匹配的 unit_outcome 记录位于没有对应 program_outcome 的单元格中。

+----------------------------------+--------------------------------------------------+
| program_outcome                  |  unit_outcome                                    |
+----------------------------------+--------------------------------------------------+
|                                  | unit_outcome 5, unit_outcome 7                   |
+----------------------------------+--------------------------------------------------+
| program outcome 1                | unit_outcome 2, unit_outcome 4                   |
+----------------------------------+--------------------------------------------------+
| program outcome 2                | unit_outcome 1, unit_outcome 3, unit_outcome 6   |
+----------------------------------+--------------------------------------------------+

这本身没问题，但我想在当前空白的 program_outcome 单元格中添加一个字符串，例如“没有匹配的程序结果”。我想在我的代码中的 PHP 中执行此操作，但不确定如何处理：

$query = "SELECT MAX(unit.unit_pk) AS unit_pk,
       GROUP_CONCAT(CONCAT('<strong>',unit.unit_code,': </strong>', unit_outcome.unit_outcome) SEPARATOR '|') unit_outcomes,
       MAX(program_outcome.program_outcome) program_outcome,
       GROUP_CONCAT(unit_outcome.unit_outcome_pk) unit_outcome_pks, 
       program_outcome.program_outcome_pk,
       program_outcome.program_outcome
FROM unit
LEFT JOIN unit_unit_outcome_lookup
    ON unit_unit_outcome_lookup.unit_fk = unit.unit_pk
LEFT JOIN unit_outcome
    ON unit_outcome.unit_outcome_pk = unit_unit_outcome_lookup.unit_outcome_fk
LEFT JOIN program_outcome_unit_outcome_lookup
    ON program_outcome_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk
LEFT JOIN program_outcome
    ON program_outcome.program_outcome_pk = program_outcome_unit_outcome_lookup.program_outcome_fk
GROUP BY program_outcome_pk ORDER BY cast(program_outcome as unsigned) ASC";

$result = $connection->query( $query );

echo "<table width='100%' border='1'><thead><tr><th>Program Outcomes</th><th>Unit Outcomes</th></tr></thead><tbody>";
while ($row = mysqli_fetch_array($result)) {
echo "<tr><td>" . $row['program_outcome'] . "</td><td>" . str_replace('|', '<p>',$row['unit_outcomes']) . "</td></tr>";
}
echo "<tbody></table>";

标签： phpmysql

解决方案

您可以在 SQL 中使用IFNULL().

$query = "SELECT MAX(unit.unit_pk) AS unit_pk,
       GROUP_CONCAT(CONCAT('<strong>',unit.unit_code,': </strong>', unit_outcome.unit_outcome) SEPARATOR '|') unit_outcomes,
       MAX(program_outcome.program_outcome) program_outcome,
       GROUP_CONCAT(unit_outcome.unit_outcome_pk) unit_outcome_pks, 
       program_outcome.program_outcome_pk,
       IFNULL(program_outcome.program_outcome, 'No matching program outcome.') AS program_outcome
FROM unit
LEFT JOIN unit_unit_outcome_lookup
    ON unit_unit_outcome_lookup.unit_fk = unit.unit_pk
LEFT JOIN unit_outcome
    ON unit_outcome.unit_outcome_pk = unit_unit_outcome_lookup.unit_outcome_fk
LEFT JOIN program_outcome_unit_outcome_lookup
    ON program_outcome_unit_outcome_lookup.unit_outcome_fk = unit_outcome.unit_outcome_pk
LEFT JOIN program_outcome
    ON program_outcome.program_outcome_pk = program_outcome_unit_outcome_lookup.program_outcome_fk
GROUP BY program_outcome_pk ORDER BY cast(program_outcome as unsigned) ASC";

推荐阅读