且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

将mysql列中的数据显示为PHP中的行

更新时间:2021-12-26 01:25:49

不要被透视查询技术吓到.就像GROUPING一样简单,然后在简单的case语句上调用 MAX().

Don't be scared off by the pivot query technique. It's as simple as GROUPing and then calling MAX() on simple case statements.

查询:

SELECT 
    `post_id`,
    MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field 14`,
    MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field 15`,
    MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field 16`
FROM `booking`
GROUP BY `post_id`
HAVING field14 IS NOT NULL AND field15 IS NOT NULL AND field16 IS NOT NULL
ORDER BY `post_id`;

* edit:根据OP的要求, HAVING 子句会忽略生成的行,这些行完全由 NULL 值组成.然后只需像往常一样处理结果集行即可.

*edit: The HAVING clause omits generated rows that are fully comprised of NULL values -- as requested by the OP. Then just process the resultset rows as you would normally.

结果集:

post_id |   field 14  | field 15  |  field 16
--------|-------------|-----------|-----------
  490   |     IND     |   LHSM    | 2018-07-07
  491   |     ERK     |   LHKE    | 2018-07-08

这是一个代码段,其中包含错误检查点,以向您展示如何处理结果集:

Here is a code snippet, packed with error checkpoints to show you how to process the resultset:

echo '<body>';
if (!$conn = new mysqli('localhost', 'admin', '', 'test')) {
    echo 'Connection Error'; // $conn->connect_error;  // never show the exact error message to the public
} else {
    $pivot = "SELECT 
                `post_id`,
                MAX(CASE WHEN `metar_key` = '_field_14' THEN `meta_value` ELSE NULL END) AS `field14`,
                MAX(CASE WHEN `metar_key` = '_field_15' THEN `meta_value` ELSE NULL END) AS `field15`,
                MAX(CASE WHEN `metar_key` = '_field_16' THEN `meta_value` ELSE NULL END) AS `field16`
            FROM `booking`
            GROUP BY `post_id`
            ORDER BY `post_id`";
    if (!$result = $conn->query($pivot)) {
        echo 'Syntax Error'; // $conn->error;  // never show the exact error message to the public
    } else {
        if (!$result->num_rows) {
            echo 'No Rows Returned From Pivot Query';
        } else {
            echo '<table>';
                echo '<tr><th>Field14</th><th>Field15</th><th>Field16</th></tr>';
                while ($row = $result->fetch_assoc()) {
                    echo "<tr><td>{$row['field14']}</td><td>{$row['field15']}</td><td>{$row['field16']}</td></tr>";
                }
            echo '</table>';
        }
        // $result->free();  // just a consideration
    }
    // $conn->close();  // just a consideration
}
echo '</body>';