Sorting MySQL results in PHP...?

I have a page on my site that lists a bunch of products, each with a user rating. I use on开发者_C百科e 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!

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");

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

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.

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.

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.

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.