I have a table which contains values in different currencies. I was wondering if it is possible to order by the price after converting the prices into 1 currency.


For example;

Table has fields "id", "product name", "currency", "price"
With records;

"1","Product A","$","150"
"2","Product B","$","100"
"3","Product C","€","100"

Today €1 = $1.32 which means that Product 3 is $132

The desired result for sorting the list would be;

"2","Product B","$","100" ($100)
"3","Product C","€","100" ($132)
"1","Product A","$","150" ($150)


I realise I could create another column and create the total in dollars every time I go to do the sorting but I wondered if there was a way to perform this operation in 1 quer

Thank you, a question that actually made me think!

The solution is JOIN.

Call the table you currently have table1, and call the columns (in order) pid, product, currency, price.

Create a second table, table2, with columns currency, conversion, with records e.g.

"$","100"
"€","132"

SELECT `table1`.`product`, (`table1`.`price` * `table2`.`conversion`) AS `final` FROM `table1` JOIN `table2` ON `table1`.`currency`=`table2`.`currency` ORDER BY (`table1`.`price` * `table2`.`conversion`) DESC

That should give you most of what you need, feel free to modify it.

This approach also means you can easily change the conversion rates, and is trivially expandable to more currencies.

swordz