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!

Chris
  • 1,273
  • 5
  • 19
  • 33
  • 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 – Pekka Dec 14 '10 at 03: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. – Jonah Dec 14 '10 at 03: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. – Chris Dec 14 '10 at 03:31
  • You can both join the product details and perform an aggregation in a single query in SQL. It is not that complex. – Dan Grossman Dec 14 '10 at 03: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. –  Dec 25 '18 at 22:16

6 Answers6

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");
Ross Snyder
  • 1,945
  • 11
  • 11
  • 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']; }); –  Dec 25 '18 at 22:17
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

Max Kielland
  • 5,627
  • 9
  • 60
  • 95
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.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
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.

Surreal Dreams
  • 26,055
  • 3
  • 46
  • 61
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.

mwotton
  • 2,170
  • 18
  • 36
  • 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... – Chris Dec 14 '10 at 05:12
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.

Amil Waduwawara
  • 1,632
  • 1
  • 16
  • 14