php PHP查询多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1627183/
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
PHP query multiple Tables
提问by Stephen
I'm having trouble getting any information to display from this query. Anyone know where I'm going wrong?
我无法从此查询中获取要显示的任何信息。有谁知道我哪里出错了？
Thank you!
谢谢！
$query = "SELECT * ".
"FROM comments, users ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo $row['users.user_id'];
echo $row['comments.comment'];
}
回答by Hubert Perron
use mysql_fetch_assoc() instead of mysql_fetch_array(). In your loop use the column name as the array key:
使用 mysql_fetch_assoc() 而不是 mysql_fetch_array()。在您的循环中使用列名作为数组键：
while ($row = mysql_fetch_assoc($result)) {
echo $row['column_name1'];
echo $row['column_name1'];
}
In your query try to be more specific on the select statement, try not to use *.
在您的查询中尽量对 select 语句更加具体，尽量不要使用 *.
回答by Anthony M. Powers
You're probably getting the error because you are sorting (ORDER BY) on a field that does not exist in your query.
您可能会收到错误消息，因为您正在对查询中不存在的字段进行排序 (ORDER BY)。
It would be best practice to not use the "SELECT *" querying. If all you need are specific values, specify them. This also helps when retrieving the data...
最好不要使用“SELECT *”查询。如果您只需要特定值，请指定它们。这也有助于检索数据...
$query = "SELECT users.user_id, comments.comment, comments.date ".
"FROM comments, users ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo $row['user_id'];
echo $row['comment'];
echo $row['date'];
}
回答by Macros
It is good practice to specify column names in a query rather than using * - on some DBs there is a performance impact and on all it prevents any unexpected behaviour cropping up from table changes.
在查询中指定列名而不是使用 * 是一种很好的做法 - 在某些 DB 上会影响性能，并且在所有情况下都可以防止因表更改而出现任何意外行为。
In the example I think the issue is arsing from the array keys you are using - you don't need to include the table name in them, just the column name:
在示例中，我认为问题出在您使用的数组键上 - 您不需要在其中包含表名，只需包含列名：
echo $row['user_id'];
echo $row['comment'];
回答by mihan007
The better practice is to write only fields what you need in your sql query like this:
更好的做法是在 sql 查询中只编写您需要的字段，如下所示：
$query = "SELECT u.user_id uid, c.comment comment ".
"FROM comments c, users u ".
"WHERE comments.user_id = users.user_id ".
"ORDER BY comments.date DESC ".
"LIMIT 10";
Using so type of queries you reduce the time of executing your query and transmitting data from database server to your php script. After this modification your cycle transformed to:
使用此类查询可以减少执行查询和将数据从数据库服务器传输到 php 脚本的时间。在此修改后，您的循环转换为：
while ($row = mysql_fetch_array($result)) {
echo $row['uid'], $row['comment'];
}
}
