Converting a value (price) within a mysql query. Is this possble?

[simboski19]

Hi all,

 

I have a mysql query which pulls out products to display. In my database each product has a price associated with it. These prices are mixed so I have products from the US, UK or Europe and obviously these prices will be in these different currencies:

 

Product X - 1.99 (GBP)

Product Y - 3.99 (US Dollars)

Product Z - 2.99 (Euros)

 

My query pulls out products into a list format which can then be sorted by price. My query uses ORDER BY price. I then convert the price retrieved into the default currency (using php). So you can imagine the prices that are sorted can sometimes look incorrect when displayed as they will be sorted and then converted.

 

My question is can you convert an integer within a mysql query???

 

Thanks

[ManiacDan]

If you store the conversion rate in a table along with the currency, then yes.  What does your table look like?

[simboski19]

Hi Dan,

 

This is the structure of my query:

 

select

product_id,

product_name,

product_description,

product_image,

product_brand,

product_active,

masterid,

catid,

subcatid,

mername,

currency,

min(feedprice) feedprice

from (

select

m.product_id AS product_id,

m.product_name AS product_name,

m.product_description AS product_description,

m.product_image AS product_image,

m.product_brand AS product_brand,

m.product_active AS product_active,

cj.masterid AS masterid,

cj.catid AS catid,

cj.subcatid AS subcatid,

mr.merchantname AS mername,

mr.currency AS currency,

case

when f.product_sale_price != '0'

then f.product_sale_price

else f.product_price

end feedprice

from tblmpr m

JOIN tblmpr_mpc_junction j ON (m.product_id = j.mprid)

JOIN tblfeeds f ON (j.product_xmlid = f.product_xmlid)

JOIN tbl_mpr_cat_junction cj ON (cj.mprid = m.product_id)

JOIN tblmerchants mr ON (f.supplier_id = mr.merchantid)

JOIN tblaffiliates af ON (m.product_affiliate = af.affiliateid)

WHERE

masterid = $masterid

AND

catid = $categoryid

AND

subcatid = $subcategoryid

AND

m.product_active = 1

AND

f.product_active = 1

AND

mr.active = 1

AND

af.active = 1

$addgender

$addbrand

ORDER BY feedprice

) as x

GROUP BY product_id

$pricesort

 

I have made the main price BOLD. This is the price I would need to convert before then ordering in the outer query. You will see a table named: tblmerchants (mr) - this is where the currency is stored. It is only stored as a string i.e. GBP, USD or EUR. Would I need to store the actual exchange rate here?

 

Thanks

Simon

[Adam]

Yes, but not for each row. You would have a table containing a single row for each currency and its corresponding exchange rate, which would all need to be based on the same currency.

 

For example, say this is your currencies table that is all based on GBP:

 

mysql> select * from currencies;

+---------------+---------------+

| currency_code | exchange_rate |

+---------------+---------------+

| GBP          | 1.0000        |

| USD          | 1.5678        |

| EUR          | 1.1476        |

+---------------+---------------+

 

You would then use a sub-query to select the exchange rate based on the currency code, retrieving the feedprice/exchange_rate:

 

            [...]
            mr.currency AS currency,
            case   
               when f.product_sale_price != '0'
               then f.product_sale_price
               else f.product_price
               end feedprice,
            (select feedprice/exchange_rate from currencies where currency_code = currency) AS international_price
            [...]

 

Edit

 

Make the exchange rates more precise than 4 decimal places by the way.

[simboski19]

Brilliant. I shall give this a go and get back to you how I get on.

 

Thanks a million

Simon

[simboski19]

Hi there,

 

I have the theory sorted and the query 95% complete, however I can't seem to select the currency from the same sub-query. This is what I have:

 

                [...]

                        converted_price

from (

select

m.product_id AS product_id,

m.product_name AS product_name,

m.product_description AS product_description,

m.product_image AS product_image,

m.product_brand AS product_brand,

m.product_active AS product_active,

cj.masterid AS masterid,

cj.catid AS catid,

cj.subcatid AS subcatid,

mr.merchantname AS mername,

mr.currency AS currency,

case

when f.product_sale_price != '0'

then f.product_sale_price

else f.product_price

end feedprice,

                              (select feedprice/conversionrate from tblcurrencies where conversiontype = currency".$_COOKIE['currency'].") AS converted_price

                              [...]

 

The conversion works well and I have successfully retrieved the correctly converted price when manually entering the 'curreny' in the new select i.e. USDGBP which means the currency of the product is in USD and the currently selected currency (which is stored in a cookie) is GBP. This is then placed into my currency table like this:

 

conversiontype        conversionrate

USDGBP                    1.54838

 

When I run the query it returns something like this:

 

(select feedprice/conversionrate from tblcurrencies where conversiontype = currencyGBP) AS converted_price

 

Any ideas how I first retrieve the currency which isnt working at the moment and also how I write the code to do this. Currently I cant think how to write this php/mysql into the select.

 

Many thanks

Simon

[simboski19]

Hi there,

 

I have just figured this out using CONCAT. Here is the way to do it for anyone else who may need this method:

 

(select feedprice/conversionrate from tblcurrencies where conversiontype = CONCAT(currency, 'GBP'))

 

Works like a treat.

 

Thanks for all the help, really appreciated!

 

Simon