在 PHP 中对 MySQL 结果进行排序...？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4435600/
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
Sorting MySQL results in PHP...?
提问by Chris
I have a page on my site that lists a bunch of products, each with a user rating. I use one query to pull some data points for each product ("details query"), and a second query that returns the average user rating for each product ("ratings query").
我的网站上有一个页面列出了一堆产品，每个产品都有一个用户评分。我使用一个查询为每个产品提取一些数据点（“详细信息查询”），第二个查询返回每个产品的平均用户评分（“评分查询”）。
I want to append the user rating for each product onto the "details query" result set, then sort by rating in descending order. I've read a bunch of entries on Stack Overflow, php.net etc. and I think I need to use usort() with a custom function, but every time I pass my MySQL result to usort() I get a php error saying the object I'm passing to usort() isn't an array. For example, I've tried:
我想将每个产品的用户评分附加到“详细信息查询”结果集中，然后按评分降序排序。我在 Stack Overflow、php.net 等上阅读了一堆条目，我认为我需要将 usort() 与自定义函数一起使用，但是每次我将 MySQL 结果传递给 usort() 时，我都会收到一个 php 错误消息我传递给 usort() 的对象不是数组。例如，我试过：
$data = mysql_fetch_array($details_query);
usort($data,"compare");
Doing the above will throw an error saying $data isn't an array. What am I doing wrong?
执行上述操作将引发错误，指出 $data 不是数组。我究竟做错了什么？
Also, if anyone has any other suggestions on how to get this done I'd really appreciate. I'm having a really tough time with this for some reason...
另外，如果有人对如何完成这项工作有任何其他建议，我将不胜感激。由于某种原因，我在这方面遇到了非常艰难的时刻...
Thanks!
谢谢！
回答by Ross Snyder
You're mis-using the MySQL functions - mysql_fetch_array doesn't take a SQL query, it takes a MySQL result resource:
你误用了 MySQL 函数 - mysql_fetch_array 不接受 SQL 查询，它需要一个 MySQL 结果资源：
http://php.net/manual/en/function.mysql-fetch-array.php
http://php.net/manual/en/function.mysql-fetch-array.php
So you want something like:
所以你想要这样的东西：
$all_data = array();
$sql = "SELECT blah FROM blah";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$all_data[] = $row;
}
// $all_data is now a 2-D array with all your data.
usort($all_data, "compare");
回答by Max Kielland
I would have done this in the SQL Query.
我会在 SQL 查询中完成此操作。
SELECT foo,bar,baz
FROM MyTable
ORDER BY bar DESC
You can put in your sorting field with PHP after "ORDER BY" and add ASC or DESC depending what way you want to sort.
您可以在“ORDER BY”之后使用 PHP 放入排序字段，并根据您想要的排序方式添加 ASC 或 DESC。
$SQL = "SELECT foo,bar,baz FROM MyTable ORDER BY ".$SortField." DESC";
Let the SQL server do as much as possible for you :D
让 SQL 服务器为你做尽可能多的事情 :D
回答by Dan Grossman
mysql_fetch_arrayretrieves a single row from the database result set into an indexed and associative array.
mysql_fetch_array将数据库结果集中的一行检索到索引和关联数组中。
It does not give you an array containing every row in the result set. You still need to loop to get all the rows:
它不会为您提供包含结果集中每一行的数组。您仍然需要循环获取所有行：
while ($row = mysql_fetch_array($details_query)) {
$data[] = $row;
}
Now you can define and use your comparison function to sort by each entry of $data's rating property, or whatever you need to do.
现在，您可以定义并使用比较函数来按 $data 的 rating 属性的每个条目或您需要执行的任何操作进行排序。
回答by Amil Waduwawara
After populating $dataarray (as instructed by Dan Grossman or beamrider9) you can use your custom callback function like:
填充$data数组后（按照 Dan Grossman 或 beamrider9 的指示），您可以使用自定义回调函数，例如：
function compare($a, $b)
{
// Assuming you're sorting on bar field
return strcmp($a['bar'], $b['bar']);
}
But as many have commented, do it in database end (using ORDER BY) rather than in your PHP code.
但正如许多人评论的那样，在数据库端（使用ORDER BY）而不是在您的 PHP 代码中执行此操作。
Also if you use mysql_fetch_array($details_query, MYSQL_ASSOC), you'll get a better result. PHP has a fantastic function called print_r(). Use it extensively to debug your code.
此外，如果您使用mysql_fetch_array($details_query, MYSQL_ASSOC)，您将获得更好的结果。PHP 有一个很棒的函数叫做print_r(). 广泛使用它来调试您的代码。
回答by Surreal Dreams
Have you tried outputting the $data array so you can see it with print_r()? Sounds like $data may be empty, hence not an array. If that's the case, either the result set is empty, or there's a problem with your query.
您是否尝试过输出 $data 数组以便您可以使用 print_r() 看到它？听起来 $data 可能是空的，因此不是数组。如果是这种情况，要么结果集为空，要么您的查询有问题。
回答by mwotton
This should be done in your MySQL select statement. If it's across multiple tables then use JOINs to combine all the data into a single result and sort it according to your needs.
这应该在你的 MySQL select 语句中完成。如果它跨多个表，则使用 JOIN 将所有数据组合成一个结果并根据您的需要对其进行排序。
