php 如何使用mysql从php中的表中返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14117165/
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
How to return multiple rows from a table in in php using mysql
提问by donsiuch
I've decided to build a website for fantasy football for my family but am getting stuck with returning multiple rows from the database.
我决定为我的家人建立一个梦幻足球网站，但我一直在从数据库中返回多行。
What I want: to make a single sql call and get the entire list of players so I can populate an object or list of objects. (if the whole table could be returned that would be great...). My goal is to have the list of available players to be drafted simply displayed to the user.
我想要的是：进行单个 sql 调用并获取整个玩家列表，以便我可以填充一个对象或对象列表。（如果可以退回整张桌子，那就太好了......）。我的目标是让可供选择的球员名单简单地显示给用户。
Currently I can somewhat see some results when testing by the following method (credit: from the php docs...). However I can't help be feel lost as to what is going on.. I can't make this make sense in my brain.
目前，通过以下方法进行测试时，我可以看到一些结果（信用：来自 php 文档...）。然而，我不禁对正在发生的事情感到迷茫......我无法在我的大脑中理解这一点。
// My query
$sql = mysql_query("SELECT * FROM players");
//$data = mysql_fetch_array($sql);
while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
printf("Name: %s <br/>", $row[1]);
}
This seems to print the names of each player. However how does 'mysql_fetch_array()' iterate each row as the while loop iterates? Further, since there are multiple columns how can I access each column... does this have to be hardcoded such as:
这似乎打印了每个玩家的名字。但是，'mysql_fetch_array()' 如何在 while 循环迭代时迭代每一行？此外，由于有多个列，我如何访问每一列......这是否必须进行硬编码，例如：
while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
printf("Name: %s <br/>", $row[1]);
printf("Team: %s <br/>", $row[2]);
..
.
printf("Team: %s <br/>", $row[5]);
}
Eventually I will replace the print statements with code that will store relevant date in a roster object or something:
最终，我将用将相关日期存储在名册对象或其他东西中的代码替换打印语句：
class Roster(){
$playerName;
$team;
$etc.
}
The while loop method above feels difficult and clunky. Is there a better way to access all returned rows? How would someone else attempt this? My limited experience is C# and sql server, which can do some amazing stuff with some effortless configuration... immediately return a list ^
上面的 while 循环方法感觉既困难又笨重。有没有更好的方法来访问所有返回的行？其他人会如何尝试？我有限的经验是 C# 和 sql server，它们可以通过一些毫不费力的配置来做一些了不起的事情......立即返回一个列表 ^
If I'm not clear I will check back soon and comment further. I'm still a CS student. Thank you for your patience.
如果我不清楚，我会尽快回来查看并进一步评论。我还是个CS学生。感谢您的耐心等待。
回答by Techy
Try this,
尝试这个，
while($row_data = mysql_fetch_array($row_data))
{
$row_player_name = $row_data['column_name_player'];
$row_team = $row_data['column_team'];
echo $row_player_name;
echo $row_team;
}
column_name_player is the column name of table which contains player name.
column_name_player 是包含玩家名称的表的列名。
回答by Hanky Panky
Although as Jason Suggested, please do not use mysql_* functions. But just to clarify your concept
尽管按照 Jason 的建议，请不要使用 mysql_* 函数。但只是为了澄清你的概念
// My query
$sql = mysql_query("SELECT * FROM players");
//$data = mysql_fetch_array($sql);
while ($row = mysql_fetch_array($sql, MYSQL_NUM)) {
printf("Name: %s <br/>", $row[1]);
}
This is complicated for a beginner unnecessarily. Instead of using row indexes like 1 or 2 or 3 you can (and probably should) simply use your field names there.
这对于初学者来说是不必要的复杂。而不是使用像 1 或 2 或 3 这样的行索引，您可以（并且可能应该）简单地在那里使用您的字段名称。
So update it to like
所以更新它喜欢
// My query
$sql = mysql_query("SELECT * FROM players");
//$data = mysql_fetch_array($sql);
while ($row = mysql_fetch_assoc($sql)) {
echo "Name:". $row["name"];
echo "Other Data:". $row["otherdata"];
echo "<br>";
}
Ofcourse nameand otherdataare just assumed. You will replace them with your actual field names from table
当然名称和其他数据只是假设。您将用表中的实际字段名称替换它们
回答by Mr. Polywhirl
If you want to retrieve every column in every row without having to know each column name, you can count the number of columns in the table.
如果要检索每一行中的每一列，而不必知道每个列名，则可以计算表中的列数。
$queryCols = "SHOW COLUMNS FROM players":
$queryPlayers = "SELECT * FROM players";
$result = mysql_query($queryPlayers);
$playerColumns = mysql_query($queryCols);
$colsResult = mysql_fetch_row($playerCols);
$numFields = count($colsResult);
while ($row = mysql_fetch_array($result)) {
for ($i = 0; $i < $numFields; $i++) {
$row[$i]; // Each column in each row
}
}
Here an example of a mysqliselect statement which is the successor to the soon to be deprecatedmysql_* statements:
这是一个mysqliselect 语句的示例，它是即将弃用的后继语句mysql_* statements：
$DB_NAME = 'test';
$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if (mysqli_connect_errno()) {
printf('Connection to $s using %s@$s failed: %s\n',
$DB_NAME, $DB_USER, $DB_HOST, mysqli_connect_error());
exit();
}
$query = "SELECT `fname`, `lname`, `team` FROM `players`;";
if ($stmt = $mysqli->prepare($query)) {
$stmt->execute();
$stmt->bind_result($fname, $lname, $team);
$table = <<< TABLE
<table border="1">
<thead><tr>
<th>First Name</th><th>Last Name</th><th>Team</th>
</tr></thead>
<tbody>
TABLE;
while ($stmt->fetch()) {
$table .= "<tr><td>$fname</td><td>$lname</td><td>$team</td></tr>";
}
$table .= "</tbody></table>";
printf($table);
$stmt->close();
} else {
printf("Prepared Statement Error: %s\n", $mysqli->error);
}
Table
桌子
CREATE TABLE IF NOT EXISTS `players` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`fname` varchar(32) NOT NULL,
`lname` varchar(32) NOT NULL,
`team` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
INSERT INTO `players` (`id`, `fname`, `lname`, `team`) VALUES
(1, 'Peyton', 'Manning', 'Denver Broncos'),
(2, 'Matt', 'Ryan', 'Atlanta Falcons'),
(3, 'Tom', 'Brady', 'New England Patriots'),
(4, 'Colin', 'Kaepernick', 'San Francisco 49ers'),
(5, 'Matt', 'Schaub', 'Houston Texans'),
(6, 'Aaron', 'Rodgers', 'Green Bay Packers'),
(7, 'Joe', 'Flacco', 'Baltimore Ravens'),
(8, 'Robert', 'Griffin III', 'Washington Redskins'),
(9, 'Andrew', 'Luck', 'Indianapolis Colts'),
(10, 'Matt', 'Flynn', 'Seattle Seahawks'),
(11, 'Andy', 'Dalton', 'Cincinnati Bengals'),
(12, 'Christian', 'Ponder', 'Minnesota Vikings');
The Results:
结果：
First Name Last Name Team
Peyton Manning Denver Broncos
Matt Ryan Atlanta Falcons
Tom Brady New England Patriots
Colin Kaepernick San Francisco 49ers
Matt Schaub Houston Texans
Aaron Rodgers Green Bay Packers
Joe Flacco Baltimore Ravens
Robert Griffin III Washington Redskins
Andrew Luck Indianapolis Colts
Matt Flynn Seattle Seahawks
Andy Dalton Cincinnati Bengals
Christian Ponder Minnesota Vikings
回答by Naresh Shahi
//This function will print 5 rows 10 columns
$n = 5;
$m = 10;
$tr='';
for($r=0; $r<$n; $r++)
{
$td='';
for ($c=0; $c<$m; $c++)
{
$td = $td."<td>".$c.$r."</td>";
}
$tr =$tr."<tr>".$td. "</tr>";
}
echo $otp = "<table border='0'>".$tr."</table>";