[eluser]Andy UK[/eluser]
I'm running a query which selects properties on max and min price. The current form SELECT options where the customer submits his or her search are in just one currency, so I'm editing these to change depending on the active view currency (held in an environment variable) and show nice round number options for each currency, whether it be USD $100,000 or EUR €100,000.
I have a method in the model that returns an array with exchange rates for all active currencies when i plug in the active view currency. For example, here is the array with Colombia Pesos as the active currency:
[1] => Array
(
[symbol] => $
[name] => Peso Colombiano
[curr_code] => COP
[exchange_rate] => 1.000000
[timestamp] => 1403809203
)
[2] => Array
(
[symbol] => $
[name] => Dolar
[curr_code] => USD
[exchange_rate] => 0.000531
[timestamp] => 1403809203
)
[3] => Array
(
[symbol] => £
[name] => Libra Esterlina
[curr_code] => GBP
[exchange_rate] => 0.000312
[timestamp] => 1403809203
)
[4] => Array
(
[symbol] => €
[name] => Euro
[curr_code] => EUR
[exchange_rate] => 0.000390
[timestamp] => 1403809203
)
Currently my active query is something like this:
Code:
$this->db->where($price . ' >=', $this->session->userdata('price_min'));
$this->db->where($price . ' <=', $this->session->userdata('price_max'));
The relevant select is:
Code:
property.currency_id,
property.{$price} as price,
with $price being either 'sale_price' or 'rental_price'
So far so good. Now what i would like to do it convert the value of the min and max search parameters to the currency of the property in a query that may draw hundreds of different properties, not all of which will have the same base currency. In quasi-code, i would like to do the following, although i'm not even sure this is possible:
Code:
$this->db->where($price . ' >=', $this->session->userdata('price_min') * $curr_array[property.currency_id]['exchange_rate']);
$this->db->where($price . ' <=', $this->session->userdata('price_max') * $curr_array[property.currency_id]['exchange_rate']);
As you can see, I want to include the currency ID of the property so as to get the correct exchange rate from the array. Can someone tell me if this is possible and if so, show me how to do this? Thanks!