1

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:

$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?

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!

Improve this question
5
  • 4
    Do this in the query. It's the easiest way. Maybe ask a separate question on how to do that, including more detail about the table structure Commented Dec 14, 2010 at 3:28
  • 1
    There's really almost never a reason to sort Database data in PHP. In almost every case, it can be done much quicker in SQL. Commented Dec 14, 2010 at 3:30
  • 1
    Hi Pekka, because of the way my tables are structured I don't think I can do this within a query. The "details query" pulls individual records based on country and the "ratings query" is aggregated across countries. I'm PRETTY sure I have to do the sorting post-query in php. Commented Dec 14, 2010 at 3:31
  • You can both join the product details and perform an aggregation in a single query in SQL. It is not that complex. Commented Dec 14, 2010 at 3:35
  • Good comments, but not entirely accurate. Sometimes it is faster to do the sort post query, especially if you are running a query on a very large database with a limit option. The sort option will create a lot of overhead (via EXPLAIN). Sometimes limiting the results, then doing a sort outside of MySQL is faster. Commented Dec 25, 2018 at 22:16

6 Answers 6

Reset to default
6

You're mis-using the MySQL functions - mysql_fetch_array doesn't take a SQL query, it takes a MySQL result resource:

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");
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

PHP 7 (which wasn't available at the time this question was answered) allows you to use the spaceship operator to do a quick nested sort: usort($result, function ($item1, $item2) { return $item2['Rank_Order'] <=> $item1['Rank_Order']; });
3

I would have done this in the SQL Query.

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.

$SQL = "SELECT foo,bar,baz FROM MyTable ORDER BY ".$SortField." DESC";

Let the SQL server do as much as possible for you :D

Improve this answer

Comments

1

mysql_fetch_array retrieves a single row from the database result set into an indexed and associative 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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

I'm not sure I can do this. The average user rating I obtain from "rating query" is aggregated across all reviews for the product across all countries and search types. "Details query" pulls a specific link based on the user's country and other parameters, so I don't think I can get everything into a single result. I think I'd have to run two queries, then sort one by the other. Not sure how to do that either...
0

After populating $data array (as instructed by Dan Grossman or beamrider9) you can use your custom callback function like:

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.

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.