Sort MySQL query results by dynamic column

[Lupi]

Hi,

I have not been able to find a solution for my issue:

Say I have a MySQL table "products" with four fields:

ID, merchant, item, price

I have a second MySQL table "shipping" with three fields:

ID, merchant, shippingcost

Now, as you can imagine, my idea is to display a table with the following columns:

merchant, item, price, shippingcost, total

I have done a LEFT JOIN to display the shipping cost. Works.

The column total is generated dynamically

$total = $row['price'] + $row['shippingcost'];

Now, I want to sort the table by column total from lowest to highest.

Of course, ORDER BY after the SELECT statement wouldn't work, as total is not actually a field of either table.

It is also not possible to calculate the total and add it as a field to table "products" before all the data is imported into the database (the whole example is very simplified).

Any ideas or work-arounds?

Cheers,
Chris

[jatar_k]

pull your data from the tables and create a multidimensional array with it

calculate your totals for each and add it onto the array

then sort the array and display

[berthamilton]

Why don't you build the total using SQL and sort by it e.g.

SELECT item, shippingcost + price as total
FROM products p, shipping s
WHERE p.merchant = s.merchant
ORDER BY total

Use whatever join you want, but your example seems to imply a natural join as above.