Geek asks and answers

MYSQL sorting function, depending on the arithmetic operation using the values ​​of the database field in the form of array keys

I have a product table, and there are 2 fields: price, currency. Possible values ​​in currency fields - 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 bets:

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

Help me with my request, I tried something like this, but I have errors:

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

Hmm ... I'll try to explain with an example.

My table:

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

This shows that the price of the first products is saved in US dollars, and the second price of the product is saved in euros. But I have to sort them in the MDL value. Therefore, I get an array of bets for each value: $rates = array([1] = > 1, [2] => 11.50, [3] => 15.50);

So, I have to order my products according to the formula: price*value rate

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

.

+3
2

- . , , currency ( , ).

currency :

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

:

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

, ration price * currency.

, , , , , ( ).

+3

, mysql - , php. php , .
:

$results = $this->Product->query
        (
        "SELECT `id`,`price`,
        CASE `currency` 
            WHEN '1' THEN $v[0]
            WHEN '2' THEN $v[1]
            WHEN '3' THEN $v[2]
        END AS 'ratio'
        FROM products
        ORDER BY price*ratio DESC
        "
        );
+3

More articles:


All Articles