PHP数组排序 [英] PHP Sort an array

问题描述

我有一个数组

$genreQuery = $con ->query ("select distinct(movie_year) from movies");

$movieGenre = array();
$movieTitle = array();
$movieList = array();

while($row = $genreQuery->fetch_object())  {
$movieGenre[] = $row;
}



foreach($movieGenre as $MGenre){
 $query = $con ->query 
 (" 
    select '$MGenre->movie_year' movie_year, IFNULL(count(*)/(select count(*) 
    from user_movie_ratings where user_id = '$userid'),0) rating
    from   user_movie_ratings umr,
    movies m
    where  umr.user_id = '$userid'
    and    umr.movie_id = m.id
    and    m.movie_year = '$MGenre->movie_year' ORDER BY rating DESC;
");

while($row = $query->fetch_object())  {
$movieTitle[] = $row;
}

}
$text = "";

foreach($movieTitle as $MTitle){

if (empty($text)){
    $text = "\"".$MTitle->movie_year."\"";}
else{
    $text = $text.",\"".$MTitle->movie_year."\"";
}
}

$list = $con ->query
(" 
    SELECT movie_name, avg_rating, image, id, genre
    FROM   movies       
    WHERE  id NOT IN (SELECT movie_id FROM user_movie_ratings WHERE user_id = '$userid')
    ORDER BY field(movie_year, $text), avg_rating DESC;
");

while($row = $list->fetch_object())  {
$movieList[] = $row;
}

该阵列由上面的查询填补，我想要做的排序是由评价，这样是看起来像这样

The array is filled by the above query, what I want to do is sort it by the rating so that is looks like this

year       rating
2014       0.0001
2015       0.0000
2013       0.0000
1967       0.0000
....       ......
....       ......
etc        etc

我曾尝试加入 ORDER BY等级DESC 来查询，但不工作，当我使用 rsort（$ movieTitle）它按年未评级做我需要做某种多维排序，或有另一种方式？

I have tried adding ORDER BY rating DESC to the query, but that does not work and when I use rsort($movieTitle) it sorts by the year not the rating do I need to do some kind of multidimensional sort, or is there another way?

推荐答案

我会为你​​似乎是运行在多个查询猜到 评级不会按照这种顺序的foreach 循环，每次迭代获得一年。因此，在这种情况下，您的订单将是一年，然后在一年评级。

I would guess that ordering by rating does not work as you seem to be running multiple queries in a foreach loop and each iteration gets one year. So in that case, your order would be by year and then in the year by rating.

您应该摆脱的循环，只能做一个查询，然后条件和排序顺序将是：

You should get rid of that loop and only do one query and then the condition and sort order would be:

...
AND m.movie_year IN (the,years,you,want)
ORDER BY rating DESC

通过循环在 $ movieGenre 像你现在这样做，你可以很容易地生成一个逗号分隔列表，这些年来在使用的语句。

By looping over the $movieGenre like you do now, you can easily generate a comma separated list for the years to use in the IN statement.

编辑：基于您的评论你想要所有年份，因此的foreach 循环，并在查询当年条件是不必要的

Based on your comment you want all years, so the foreach loop and the year condition in the query are unnecessary.

您可能想是这样的：

SELECT m.movie_year, IFNULL(count(*)/(select count(*) 
    from user_movie_ratings where user_id = '$userid'),0) rating
FROM user_movie_ratings umr,
    movies m
WHERE umr.user_id = '$userid'
    AND umr.movie_id = m.id
ORDER BY rating DESC

假设当然用户ID是安全的查询中使用的，你应该使用prepared声明，以避免潜在的SQL注入。

Assuming of course that the user ID is safe to use in a query, you should really use a prepared statement to avoid potential sql injection.

现在你有一个查询所有的结果，所以没有必要在外环了。

Now you have all your results in one query so there is no need for the outer loop any more.

这篇关于PHP数组排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！