php 从 sql 表创建 HTML 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11943479/
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
Create HTML table from sql table
提问by Aloke Desai
I have a table in a SQL database with the following fields: ID, Name, Email, University, Languages, and Experience. I want to create an html table that fetches data from SQL and outputs the last 10 results? How would I do that?
我在 SQL 数据库中有一个表，其中包含以下字段：ID、姓名、电子邮件、大学、语言和经验。我想创建一个从 SQL 获取数据并输出最后 10 个结果的 html 表？我该怎么做？
I'm sorry if this is a very simple question, I have very little knowledge in PHP and SQL.
如果这是一个非常简单的问题，我很抱歉，我对 PHP 和 SQL 知之甚少。
Here's the code I have right now that just displays the name and not in a table:
这是我现在拥有的代码，它只显示名称而不是在表格中：
<html>
<head>
<title>Last 5 Results</title>
</head>
<body>
<?php
$connect = mysql_connect("localhost","root", "root");
if (!$connect) {
die(mysql_error());
}
mysql_select_db("apploymentdevs");
$results = mysql_query("SELECT * FROM demo");
while($row = mysql_fetch_array($results)) {
echo $row['Name'] . "</br>";
?>
</body>
</html>
采纳答案by Josejulio
Here is something that should help you to create the table and get more knowledge of phpand mysql.
这里有一些可以帮助您创建表并获得更多php和mysql知识的东西。
Also you should move your connection logic and query to the beginning of your process, thus avoiding errors while loading the page and showing a more accurate error than just the mysql_error.
此外，您应该将连接逻辑和查询移动到流程的开头，从而避免在加载页面时出现错误并显示比 mysql_error 更准确的错误。
Edit: If your ids are incrementing, then you could add the ORDER BY clause,
change: SELECT * FROM demo LIMIT 10to: SELECT * FROM demo LIMIT 10 ORDER BY id
编辑：如果您的 id 正在增加，那么您可以添加 ORDER BY 子句，
将：更改SELECT * FROM demo LIMIT 10为：SELECT * FROM demo LIMIT 10 ORDER BY id
<html>
<head>
<title>Last 10 Results</title>
</head>
<body>
<table>
<thead>
<tr>
<td>Id</td>
<td>Name</td>
</tr>
</thead>
<tbody>
<?php
$connect = mysql_connect("localhost","root", "root");
if (!$connect) {
die(mysql_error());
}
mysql_select_db("apploymentdevs");
$results = mysql_query("SELECT * FROM demo LIMIT 10");
while($row = mysql_fetch_array($results)) {
?>
<tr>
<td><?php echo $row['Id']?></td>
<td><?php echo $row['Name']?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
回答by peterrus
An option requiring you to encode the data in the database to JSON: http://www.jeasyui.com/documentation/datagrid.php
一个选项要求您将数据库中的数据编码为 JSON：http: //www.jeasyui.com/documentation/datagrid.php
But this looks a lot more promising: http://phpgrid.com/
但这看起来更有希望：http: //phpgrid.com/
回答by petermeissner
I got very annoyed pasting together the same code over and over again to build HTML tables from SQL queries.
我对一遍又一遍地将相同的代码粘贴在一起以根据 SQL 查询构建 HTML 表感到非常恼火。
So, here we go with a function that takes mysqliresults and pastes them together to an plain simple HTML tablethat has column names according to those in the database:
因此，我们在这里使用一个函数，该函数获取mysqli结果并将它们粘贴到一个简单的 HTML 表格中，该表格具有根据数据库中的列名：
function sql_to_html_table($sqlresult, $delim="\n") {
// starting table
$htmltable = "<table>" . $delim ;
$counter = 0 ;
// putting in lines
while( $row = $sqlresult->fetch_assoc() ){
if ( $counter===0 ) {
// table header
$htmltable .= "<tr>" . $delim;
foreach ($row as $key => $value ) {
$htmltable .= "<th>" . $key . "</th>" . $delim ;
}
$htmltable .= "</tr>" . $delim ;
$counter = 22;
}
// table body
$htmltable .= "<tr>" . $delim ;
foreach ($row as $key => $value ) {
$htmltable .= "<td>" . $value . "</td>" . $delim ;
}
$htmltable .= "</tr>" . $delim ;
}
// closing table
$htmltable .= "</table>" . $delim ;
// return
return( $htmltable ) ;
}
Example Usage:
示例用法：
$DB = new mysqli("host", "username", "password", "database");
$sqlresult = $DB->query( "SELECT * FROM testtable LIMIT 1 ;" ) ;
echo sql_to_html_table( $sqlresult, $delim="\n" ) ;
回答by Brandon
Calling table( $result );on your query results will generate a html based table regardless of the size of the sql array you feed it. I hope this helps :)
table( $result );无论您提供的 sql 数组的大小如何，调用您的查询结果都会生成一个基于 html 的表。我希望这会有所帮助:)
<?php
function table( $result ) {
$result->fetch_array( MYSQLI_ASSOC );
echo '<table>';
tableHead( $result );
tableBody( $result );
echo '</table>';
}
function tableHead( $result ) {
echo '<thead>';
foreach ( $result as $x ) {
echo '<tr>';
foreach ( $x as $k => $y ) {
echo '<th>' . ucfirst( $k ) . '</th>';
}
echo '</tr>';
break;
}
echo '</thead>';
}
function tableBody( $result ) {
echo '<tbody>';
foreach ( $result as $x ) {
echo '<tr>';
foreach ( $x as $y ) {
echo '<td>' . $y . '</td>';
}
echo '</tr>';
}
echo '</tbody>';
}
回答by mdup
You might be interested in fetching with mysql_fetch_assoc()(so that you get the data in an associative array : keys=>value). In the keys are your column names ; so, for each line, you can loop through each column (with array_keys()) and print its value.
您可能对 fetch 感兴趣mysql_fetch_assoc()（以便您在关联数组中获取数据：keys=>value）。键中是您的列名；因此，对于每一行，您可以遍历每一列（使用array_keys()）并打印其值。
$results = mysql_query("SELECT * FROM demo");
while($row = mysql_fetch_assoc($results)) {
foreach (array_keys($row) as $column) {
echo $row[$key] . "</br>";
}
}
(After that, you can cache array_keys($row) in a variable which is only set once, because its value won't change while you run through the results.)
（之后，您可以将 array_keys($row) 缓存在一个只设置一次的变量中，因为在您运行结果时它的值不会改变。）
回答by Paul Carlton
Even though it's been a while I'm going to put my 2 cents in: use functions (even better - classes). Creating tables from mysql results is a very common task.
即使已经有一段时间了，我还是要把我的 2 美分投入：使用函数（甚至更好 - 类）。从 mysql 结果创建表是一项非常常见的任务。
<!DOCTYPE html>
<html>
<head><title>Create Tables from MySQL using functions</title></head>
<body>
<?php
db_connect();
// assuming you have an auto increment id as the first column
$result = mysql_query("SELECT * FROM demo ORDER BY 1 DESC LIMIT 10");
print createTable(array_result($result));
?>
</body>
</html>
<?php
/**
* Quick mysql result function
*
* @param $result
* @return array
*/
function array_result($result)
{
$args = array();
while ($row = mysql_fetch_assoc($result)) {
$args[] = $row;
}
return $args;
}
/**
* Connect to db
*
* @param string $db_host
* @param string $db
*/
function db_connect($db_host = "localhost", $db = "apploymentdevs")
{
$connect = mysql_connect($db_host,"root", "root");
if (!$connect) {
die(mysql_error());
}
mysql_select_db($db);
}
/**
* Create a table from a result set
*
* @param array $results
* @return string
*/
function createTable(array $results = array())
{
if (empty($results)) {
return '<table><tr><td>Empty Result Set</td></tr></table>';
}
// dynamically create the header information from the keys
// of the result array from mysql
$table = '<table>';
$keys = array_keys(reset($results));
$table.='<thead><tr>';
foreach ($keys as $key) {
$table.='<th>'.$key.'</th>';
}
$table.='</tr></thead>';
// populate the main table body
$table.='<tbody>';
foreach ($results as $result) {
$table.='<tr>';
foreach ($result as $val) {
$table.='<td>'.$val.'</td>';
}
$table.='</tr>';
}
$table.='</tbody></table>';
return $table;
}
回答by Sgarz
I hope you know how to make a table in HTML, with <table>, <tr> and <td>.
我希望你知道如何用 HTML 制作表格 <table>, <tr> and <td>.
Fix a table with that in your while-loop and use "SELECT * FROM demo LIMIT 10"as SQL-query.
在你的 while 循环中修复一个表并"SELECT * FROM demo LIMIT 10"用作 SQL 查询。
