在PHP中将mysql列中的数据显示为行
在PHP中将mysql列中的数据显示为行
提问于 2018-07-13 15:07:28
我正在尝试创建一个网站来显示预订系统的表。我在mysql数据库中有一个表，其中包含如下数据：
第二列是post_id。预订详细信息由相同的post_id提供。
我想创建一个html表，它在一行中包含相同的预订详细信息，如下所示：
<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>回答 5
Stack Overflow用户
回答已采纳
发布于 2018-07-13 16:37:47
不要被pivot查询技术吓跑了。它和GROUPing一样简单，然后在简单的case语句上调用MAX()。
查询：
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值组成的生成的行。然后像往常一样处理结果集行。
结果集：
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>';Stack Overflow用户
发布于 2018-07-13 16:25:45
试试这个。
<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>Post_id</th>
<th>Field14</th>
<th>Field15</th>
<th>Field16</th>
</tr>
<?php
$conn = mysqli_connect("10.0.1.1", "user", "pwd", "mydb");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT DISTINCT(POST_ID) from booking";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$idsql = "select metar_key, meta_value from booking where POST_ID=\"".$row["POST_ID"]."\"";
$idresult = $conn->query($idsql);
while ($id = $idresult->fetch_assoc()) {
$var[$id["metar_key"]] = $id["meta_value"];
}
echo "<tr>\n";
echo "<td>".$row["POST_ID"]."</td>\n";
echo "<td>".$var["_field_14"]."</td>\n";
echo "<td>".$var["_field_15"]."</td>\n";
echo "<td>".$var["_field_16"]."</td>\n";
echo "</tr>\n";
}
}
/*
$sql = "SELECT _field_14, _field_15, _field_16 FROM booking";
$result = $conn->query($sql);
*/
?>
</table>
</body>
</html>Stack Overflow用户
发布于 2018-07-13 15:24:36
我不知道我是否做对了，但我认为你必须加入。假设第一个表是表A，包含预订详细信息的表是表B。您的请求将是：
SELECT post_id, field14, field15, field16 FROM B WHERE post_id IN (SELECT post_id FROM A);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51319503
复制相关文章
相似问题