CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 1333

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 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!

Upvotes: 1

Views: 25474

Answers (6)

Amil Waduwawara
Amil Waduwawara

Reputation: 1642

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.

Upvotes: 0

Ross Snyder
Ross Snyder

Reputation: 1975

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

Upvotes: 6

mwotton
mwotton

Reputation: 2200

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.

Upvotes: 0

Max Kielland
Max Kielland

Reputation: 5841

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

Upvotes: 3

Dan Grossman
Dan Grossman

Reputation: 52382

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.

Upvotes: 1

Surreal Dreams
Surreal Dreams

Reputation: 26380

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.

Upvotes: 0

Related Questions