将mysql列中的数据显示为PHP中的行 [英] Display data from mysql columns as rows in PHP
问题描述
我正在尝试创建一个网站以显示预订系统的表格.我在mysql数据库中有一个表，其中包含如下数据:
Im trying to create a website to display a booking system's table.
I have a table in mysql database which containing the data like this:
第二列是post_id.预订详细信息带有相同的post_id.
The second column is the post_id. The booking details are provided with the same post_id.
我想创建一个html表，该表在一行中包含相同的预订详细信息，如下所示:
I would like to create an html table which contains the same booking details in one row like this:
<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>
推荐答案
不要被透视查询技术吓到.就像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>';
这篇关于将mysql列中的数据显示为PHP中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
