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

一尘不染

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

sql

我试图创建一个网站来显示预订系统的表格。我在mysql数据库中有一个表，其中包含如下数据：

第二列是post_id。预订详细信息带有相同的post_id。

<html>
<head>
  <title>Booking</title>
  <style>
    table {
      border-collapse: collapse;
      width: 100%;
      color: #588c7e;
      font-family: monospace;
      font-size: 25px;
      text-align: left;
    } 
    th {
      background-color: #588c7e;
      color: white;
    }
    tr:nth-child(even) {background-color: #f2f2f2}
  </style>
</head>
<body>
  <table>
    <tr>
      <th>Field14</th> 
      <th>Field15</th> 
      <th>Field16</th>
    </tr>
    <?php
      $conn = mysqli_connect("localhost", "admin", "", "test");
      // Check connection
      if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
      } 
      $sql = "SELECT _field_14, _field_15, _field_16 FROM booking";
      $result = $conn->query($sql);

    ?>
  </table>
</body>
</html>

阅读 128

2021-05-16

共1个答案

一尘不染

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

询问：

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：该HAVING子句忽略了完全由NULL值组成的生成的行，这是OP所要求的。然后，只需像平常一样处理结果集行即可。

结果集：

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

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

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>';

2021-05-16