MYSQL sort function, depending on the arithmetic operation using the database field values as array keys

Question

I have table of products, and there are 2 fields: price, currency. Possible values in currency fields are 1,2,3. 1 for MDL, 2 for USD, and 3 for EUR. I have to sort my products by price in mdl currency. So i have an array with rates:

$v = array(1,11.38,15.8);

Help my please with my query, I've tried something like this, but I've got errors:

$results = $this->Product
                ->query("SELECT `id`,`price`,`currency` FROM products 
                         ORDER BY price*$v[currency] DESC");

Hmm... I`ll try to explain, through an example.

My table:

id|price|currency
_________________
1 | 500 | 2
2 | 300 | 3

It shows that first products price is saved in USD, and second products price is saved in EUR. But i have to sort them in MDL valute. So i get an array of rates for each value: $rates = array([1] = > 1, [2] => 11.50, [3] => 15.50);

So i have to order my products by the result of formula: price*value rate

in first case: 500*$rates['currency value from db, in our case 2] = 500 * 11.50 etc.

Thanks in advance.

Answer 1

Because of the extended example on this problem I have edited this query. Lets assume that the currencies are alse stored in some table, lets say currency (if not, it should be anyway).

Table currency should be as follows:

ID        VALUE        CODE
-----------------------------
1         1            USD
2         11.38        EUR
3         15.8         MDL

Then the query should be:

SELECT p.`id`, p.`price`, p.`price` * c.`value` AS 'ratio' 
FROM products p
LEFT JOIN currency c ON c.`id` = p.`currency`
ORDER BY `ratio` DESC

By this query You select the currency value from the table currency depending on the currency ID from products table and finaly the results are ordered by the ration price * currency value.

I understand that maybe You have the currencies only hardcoded as array within some config, but it really would be better to put the currencies into the DB (if it is not).