php 显示一个mysql表中的所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14825417/
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
Displaying all records in a mysql table
提问by user2063487
The code below works fine for printing one record from a database table, but what I really want to be able to do is print all the records in the mysql table in a format similar to my code.
下面的代码可以很好地从数据库表中打印一条记录，但我真正想要做的是以类似于我的代码的格式打印 mysql 表中的所有记录。
I.E.: Field Name as heading for each column in the html table and the entry below the heading. Hope this is making sense to someone ;)
IE：字段名称作为 html 表中每一列的标题和标题下方的条目。希望这对某人有意义;)
$raw = mysql_query("SELECT * FROM tbl_gas_meters");
$allresults = mysql_fetch_array($raw);
$field = mysql_query("SELECT * FROM tbl_gas_meters");
$num_fields = mysql_num_fields($raw);
$num_rows = mysql_num_rows($raw);
$i = 1;
print "<table border=1>\n";
while ($i < $num_fields)
{
echo "<tr>";
echo "<b><td>" . mysql_field_name($field, $i) . "</td></b>";
//echo ": ";
echo '<td><font color ="red">' . $allresults[$i] . '</font></td>';
$i++;
echo "</tr>";
//echo "<br>";
}
print "</table>";
回答by Goddard
Just as an additional piece of information you should probably be using PDO. It has more features and is helpful in learning how to prepare SQL statements. It will also serve you much better if you ever write more complicated code.
作为附加信息，您可能应该使用 PDO。它具有更多功能，有助于学习如何编写 SQL 语句。如果您编写更复杂的代码，它也会更好地为您服务。
http://www.php.net/manual/en/intro.pdo.php
http://www.php.net/manual/en/intro.pdo.php
This example uses objects rather then arrays. Doesn't necessarily matter, but it uses less characters so I like it. Difference do present themselves when you get deeper into objects, but not in this example.
这个例子使用对象而不是数组。不一定重要，但它使用的字符较少，所以我喜欢它。当您深入了解对象时，差异确实会出现，但在此示例中不会。
//connection information
$user = "your_mysql_user";
$pass = "your_mysql_user_pass";
$dbh = new PDO('mysql:host=your_hostname;dbname=your_db;charset=UTF-8', $user, $pass);
//prepare statement to query table
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
//loop over all table rows and fetch them as an object
while($result = $sth->fetch(PDO::FETCH_OBJ))
{
//print out the fruits name in this case.
print $result->name;
print("\n");
print $result->colour;
print("\n");
}
You probably also want to look into prepared statements. This helps against injection. Injection is bad for security reasons. Here is the page for that.
您可能还想查看准备好的语句。这有助于防止注射。出于安全原因，注入是不好的。这是页面。
http://www.php.net/manual/en/pdostatement.bindparam.php
http://www.php.net/manual/en/pdostatement.bindparam.php
You probably should look into sanitizing your user input as well. Just a heads up and unrelated to your current situation.
您可能还应该考虑清理您的用户输入。只是提醒一下，与您目前的情况无关。
Also to get all the field names with PDO try this
还要使用 PDO 获取所有字段名称，请尝试此操作
$q = $dbh->prepare("DESCRIBE tablename");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
Once you have all the table fields it would be pretty easy using <div>or even a <table>to arrange them as you like using a <th>
一旦您拥有所有表字段，就可以很容易地使用<div>，甚至可以根据<table>需要使用<th>
Happy learning PHP. It is fun.
快乐学习PHP。很好玩。
回答by user2063487
Thanks guys, got it.
谢谢各位，明白了。
$table = 'tbl_gas_meters';
$result = MYSQL_QUERY("SELECT * FROM {$table}");
$fields_num = MYSQL_NUM_FIELDS($result);
ECHO "<h1>Table: {$table}</h1>";
ECHO "<table border='1'><tr>";
// printing table headers
FOR($i=0; $i<$fields_num; $i++)
{
$field = MYSQL_FETCH_FIELD($result);
ECHO "<td>{$field->name}</td>";
}
ECHO "</tr>\n";
// printing table rows
WHILE($row = MYSQL_FETCH_ROW($result))
{
ECHO "<tr>";
// $row is array... foreach( .. ) puts every element
// of $row to $cell variable
FOREACH($row AS $cell)
ECHO "<td>$cell</td>";
ECHO "</tr>\n";
}
回答by ashleedawg
Thanks! I adapted some of these answers to draw a table from all records from any table, without having to specify the field names. Just paste this into a .php file and change the connection info:
谢谢！我修改了其中的一些答案，以从任何表的所有记录中绘制一个表，而无需指定字段名称。只需将其粘贴到 .php 文件中并更改连接信息：
<?php
// Authentication detail for connection
$servername = "localhost";
$username = "xxxxxxxxxx";
$password = "xxxxxxxxxx";
$dbname = "xxxxxxxxxx";
$tablename = "xxxxxxxxxx";
$orderby = "1 DESC LIMIT 500"; // column # to sort & max # of records to display
// Create & check connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error); // quit
}
// Run query & verify success
$sql = "SELECT * FROM {$tablename} ORDER BY {$orderby}";
if ($result = $conn->query($sql)) {
$conn->close(); // Close table
$fields_num = $result->field_count;
$count_rows = $result->num_rows;
if ($count_rows == 0) {
die ("No data found in table: [" . $tablename . "]" ); //quit
}
} else {
$conn->close(); // Close table
die ("Error running SQL:<br>" . $sql ); //quit
}
// Start drawing table
echo "<!DOCTYPE html><html><head><title>{$tablename}</title>";
echo "<style> table, th, td { border: 1px solid black; border-collapse: collapse; }</style></head>";
echo "<body><span style='font-size:18px'>Table: <strong>{$tablename}</strong></span><br>";
echo "<span style='font-size:10px'>({$count_rows} records, {$fields_num} fields)</span><br>";
echo "<br><span style='font-size:10px'><table><tr>";
// Print table Field Names
while ($finfo = $result->fetch_field()) {
echo "<td><center><strong>{$finfo->name}</strong></center></td>";
}
echo "</tr>"; // Finished Field Names
/* Loop through records in object array */
while ($row = $result->fetch_row()) {
echo "<tr>"; // start data row
for( $i = 0; $i<$fields_num; $i++ ) {
echo "<td>{$row[$i]}</td>";
}
echo "</tr>"; // end data row
}
echo "</table>"; // End table
$result->close(); // Free result set
?>
回答by Taz
while ( $row = mysql_fetch_array($field) ) {
echo $row['fieldname'];
//stuff
}
回答by Prasanth Bendra
Try this :
尝试这个 ：
$raw = mysql_query("SELECT * FROM tbl_gas_meters");
$allresults = mysql_fetch_array($raw);
$field = mysql_query("SELECT * FROM tbl_gas_meters");
while($row = mysql_fetch_assoc($field)){
echo $row['your field name here'];
}
Please note that, mysql_* functions are deprecated in new php version , so use mysqli or PDO instead.
请注意，mysql_* 函数在新的 php 版本中已弃用，因此请改用 mysqli 或 PDO。
