如何使用 PHP 从 SQL 中获取具有列名的数据？

Question

我试图在我的 HTML/PHP 网页中显示来自 SQL 的完整表格。到目前为止，我成功地从我的网页中的表格中获取数据，而无需在 HTML 中定义每一行。这有效，但它仅显示表中的数据。我想查看第一行中的列名。

请参阅下面的代码：

include_once "connection.php";

$sql = "SELECT * FROM `own`";

$result = mysqli_query($conn, $sql);

echo "<br>";

echo "<table border='1'>";

while ($row = mysqli_fetch_assoc($result)) 

{ 

  echo "<tr>";

  foreach($row as $value) { 

    echo "<td>" . $value . "</td>"; 

  }

  echo "</tr>";

}

echo "</table>";

Answer 1

当您使用mysqli_fetch_assoc()时 - 这将返回一个数组，其中列名作为每个值的键。因此，此代码将（仅对于第一个循环）将列名显示为单独的行。$headerDisplayed = false;while ($row = mysqli_fetch_assoc($result)){&nbsp; &nbsp; if ( $headerDisplayed == false )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; echo "<tr>";&nbsp; &nbsp; &nbsp; &nbsp; foreach($row as $columnName => $value) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "<th>" . $columnName . "</th>";&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; echo "</tr>";&nbsp; &nbsp; &nbsp; &nbsp; $headerDisplayed = true;&nbsp; &nbsp; }&nbsp; &nbsp; echo "<tr>";&nbsp; &nbsp; foreach($row as $value) {&nbsp; &nbsp; &nbsp; &nbsp; echo "<td>" . $value . "</td>";&nbsp; &nbsp; }&nbsp; &nbsp; echo "</tr>";}如果您希望能够给出更有意义的名称，您还可以使用列别名（例如）..select `dept_no` as `department number` from `departments`将显示department number为标题而不是dept_no.

Answer 2

最简单的解决方案是在第一次迭代时首先输出列名：while ($row = mysqli_fetch_assoc($result))&nbsp;{&nbsp;&nbsp; &nbsp; // If the variable $colNamesPrinted is undefined, print the column names&nbsp; &nbsp; if (isset($colNamesPrinted) === false) {&nbsp; &nbsp; &nbsp; &nbsp; echo "<tr>";&nbsp; &nbsp; &nbsp; &nbsp; foreach($row as $name => $value) {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; echo "<th>" . $name . "</th>";&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; echo "</tr>";&nbsp; &nbsp; &nbsp; &nbsp; // Define the variable so it will be set on the next iteration.&nbsp; &nbsp; &nbsp; &nbsp; $colNamesPrinted = true;&nbsp; &nbsp; }&nbsp; &nbsp; echo "<tr>";&nbsp; &nbsp; foreach($row as $value) {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; echo "<td>" . $value . "</td>";&nbsp;&nbsp; &nbsp; }&nbsp; &nbsp; echo "</tr>";}