使用 PHP 在 HTML 表格中显示 MySQL 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14430174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use PHP to Display MySQL Results in HTML Table
提问by Charles
Update for CodingBiz:
CodingBiz 更新：
I'm putting this in my code:
我把它放在我的代码中：
for($i=1;$i<=$numRows;$i++) {
$output .= '<tr>';
$row = $this->fetchAssoc($result);
$colRow = $this->fetchAssoc($colResult);
foreach($colRow as $colName) {
$output .= "<td>".$row[$colName]."</td>";
}
$output .= '</tr>';
}
in place of
代替
for($i=1;$i<=$numRows;$i++) {
$output .= '<tr>';
$row = $this->fetchAssoc($result);
for($j=1;$j<=$colNumRows;$j++) {
$colRow = $this->fetchAssoc($colResult);
$output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";
}
$output .= '</tr>';
}
Is there anything wrong with this?
这有什么问题吗？
Original Post:
原帖：
I'm writing a function in a PHP class to display the results of a query in a table. I'm not structuring any of the table myself, I want it everything to be done using PHP. Here is my code so far:
我正在 PHP 类中编写一个函数来在表中显示查询的结果。我没有自己构建任何表格，我希望它使用 PHP 完成所有工作。到目前为止，这是我的代码：
function allResults($table,$cols) {
if(isset($cols)) {
$query = "SELECT $cols FROM $table";
}
else {
$query = "SELECT * FROM $table";
}
$result = $this->query($query);
$numRows = $this->numRows($result);
$colQuery ="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='shareride' AND TABLE_NAME='$table'";
$colResult = $this->query($colQuery);
$colNumRows = $this->numRows($colResult);
$output = '<table class="allResults">';
$output .= '<tr>';
for($i=1;$i<=$colNumRows;$i++) {
$colRow = $this->fetchAssoc($colResult);
$output .= "<td>".$colRow["COLUMN_NAME"]."</td>";
}
$output .= '</tr>';
for($i=1;$i<=$numRows;$i++) {
$output .= '<tr>';
$row = $this->fetchAssoc($result);
for($j=1;$j<=$colNumRows;$j++) {
$colRow = $this->fetchAssoc($colResult);
$output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";
}
$output .= '</tr>';
}
$output .= '</table>';
return $output;
}
In case it is unclear, queryrefers to mysqli_query, numRowsrefers to mysqli_num_rows, and fetchAssocrefers to mysqli_fetch_assoc. The database name is "shareride."
如果不清楚，请query参考mysqli_query、numRows参考mysqli_num_rows和fetchAssoc参考mysqli_fetch_assoc。数据库名称是“shareride”。
I know I am missing something in this line:
我知道我在这一行中遗漏了一些东西：
$output .= "<td>".$row[$colRow["COLUMN_NAME"]]."</td>";
but I just don't know what it is. Right now, I get all the table column titles displayed correctly, and I get the correct number of content rows, but I just can't populate those rows with the actual data from the database.
但我只是不知道它是什么。现在，我正确显示了所有表列标题，并获得了正确数量的内容行，但我无法使用数据库中的实际数据填充这些行。
What am I missing? Any help would be GREATLYappreciated!
我错过了什么？任何帮助将不胜感激！
回答by codingbiz
Get the data and column names from the same result set
从同一结果集中获取数据和列名
<?php
$i = 0;
$colNames = array();
$data = array();
while($row = ***_fetch_assoc($res)) //where $res is from the main query result not schema information
{
//get the column names into an array $colNames
if($i == 0) //make sure this is done once
{
foreach($row as $colname => $val)
$colNames[] = $colname;
}
//get the data into an array
$data[] = $row;
$i++;
}
?>
UPDATE: Suggested by @YourCommonSense to replace the above code and it worked, simple and shorter - A WAY TO GET THE COLUMN NAMES/ARRAY KEYS WITHOUT LOOPING THROUGH LIKE I DID
更新：@YourCommonSense 建议替换上面的代码，它有效，简单且更短 - 一种无需像我那样循环即可获取列名称/数组键的方法
$data = array();
while($row = mysql_fetch_assoc($res))
{
$data[] = $row;
}
$colNames = array_keys(reset($data))
Continued as before: Print the table
继续如前：打印表格
<table border="1">
<tr>
<?php
//print the header
foreach($colNames as $colName)
{
echo "<th>$colName</th>";
}
?>
</tr>
<?php
//print the rows
foreach($data as $row)
{
echo "<tr>";
foreach($colNames as $colName)
{
echo "<td>".$row[$colName]."</td>";
}
echo "</tr>";
}
?>
</table>
Test Result
测试结果
You can see how I separated the data retrieval from table generation. They are dependent of each other now and you can test your table generation without the database by populating the arrays with static data
您可以看到我如何将数据检索与表生成分离。它们现在相互依赖，您可以通过使用静态数据填充数组来测试没有数据库的表生成
You can also make them into separate functions.
您也可以将它们变成单独的函数。
回答by Your Common Sense
- Nevermix your database handling code with HTML output code. These are 2 totallydifferent matters. Make your allResults function only return array with data, and then you can make another function to print in fancy way (and notin the database handler class).
- You don't need information schema to get column name - you already have it in the returned array keys.
- You don't need numRows either - use while() and foreach()
- NEVERinsert anything into query directly like you do with
$cols- eventually it will lead to errors and injections. - Such a function, without accepting some parameters for the query, makes absolutely no senseespecially in the context of migrating from mysql to mysqli - you are going yo use it as an old school mysql query inserting variables, not placeholders. So, it makes migration totally useless.
- To know "Is there anything wrong with code", one have to runit, not watch. Run and debug your code, outputting key variables and making sure you can see errors occurred.
- 永远不要将您的数据库处理代码与 HTML 输出代码混合在一起。这是两个完全不同的问题。让你的 allResults 函数只返回带有数据的数组，然后你可以让另一个函数以奇特的方式打印（而不是在数据库处理程序类中）。
- 您不需要信息架构来获取列名 - 您已经在返回的数组键中拥有它。
- 您也不需要 numRows - 使用 while() 和 foreach()
- 永远不要像你那样直接在查询中插入任何东西
$cols- 最终它会导致错误和注入。 - 这样的函数，不接受查询的某些参数，绝对没有意义，尤其是在从 mysql 迁移到 mysqli 的上下文中 - 您将把它用作老式 mysql 查询插入变量，而不是占位符。因此，它使迁移完全无用。
- 要知道“代码有什么问题”，必须运行它，而不是观看。运行和调试您的代码，输出关键变量并确保您可以看到发生的错误。
回答by Stormsson
i'd try replacing the data part with something like:
我会尝试用以下内容替换数据部分：
while($row = $this->fetchAssoc($colResult))
{
echo "<tr>";
foreach($row as $value)
{
echo sprintf("<td>%s</td>",$value)
}
echo "</tr>";
}
i know it's not a proper answer, but it's really hard to read that code imho
我知道这不是一个正确的答案，但是恕我直言，很难阅读该代码
