CODEWITHSUNDEEP
phpmysqlsortingsql-order-by
Adam Strudwick
Adam Strudwick

Reputation: 13129

Sort MySQL query data by calculated values

I export from MYSQL db different info that I put inside an array:

$info = array(
(ID,CALORIES,PROTEIN,CARBOS,FIBER),
...
);
ex: (1,270,24,12,5),(2,280,2,42,10),...

Then, further down the script, I need to get the IDs of the 3 products with the highest calories, of the 6 products with the highest result of 2xPROTEIN+3xCARBOS, etc for 5 charts.

How can I do such a sorting of the array to fill my different tables?

The function sort() only seems to work for a single-dimensioned array (if it works for my case, I don't know what would be the syntax). It also doesn't seem to work for my more advanced sortings (2x+3y)...

Upvotes: 1

Views: 127

Answers (4)

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Even tho it is not exactly what you are asking, I would highly suggest you preparing your tables in mysql (using all formulas to sort, etc.). Mysql's main job is do various selections and sorting, I would leave the work for it. Moreover, advanced sorting in mysql is way easier than thinking of algorithms or functions in php :)

SELECT * FROM `products`
ORDER BY (2*PROTEIN+3*CARBOS) DESC

Easy as that and no headaches. Apply LIMIT 3 at the end to get the top 3. Update

SELECT * FROM `products`

to your more advanced query. If having difficulties in code you may try to wrap it up as a subquery as this:

SELECT * FROM (SELECT * FROM `products` WHERE `type`='fruit' LIMIT 6) a
ORDER BY (2*PROTEIN+3*CARBOS) DESC LIMIT 3

Upvotes: 3

Kep
Kep

Reputation: 5857

usort is your friend here.

function sort2Protein3Carbo($a, $b)
{
    // Assuming protein is the 2nd value in your array and carbo the 3rd.
    $resA = 2 * $a[2] + 3 * $a[3];
    $resB = 2 * $b[2] + 3 * $b[3];

    if ($resA == $resB)
        return 0;

    return ($resA < $resB) ? -1 : 1;
}

usort($info, "sort2Protein3Carbo");

Upvotes: 1

Samson
Samson

Reputation: 2821

You can use usort function:

function cmp( $a, $b )
{ 
  if(  $a["calories"] *3+ $a["protein"]*2 ==   $b["calories"] *3+ $b["protein"]*2){ //do another comparison etc.. ; } 
  return ($a["calories"] *3+ $a["protein"]*2<   $b["calories"] *3+ $b["protein"]*2)) ? -1 : 1;
} 
usort($myarray,'cmp');

Upvotes: 1

user267885
user267885

Reputation:

You can use array_multisort() to sort multidimensional arrays. The syntax allows for quite some flexibilty, see the reference.

Upvotes: 1

Related Questions