Coping with two currencies.

Hi Mike,

It should be possible to adjust the SQL to perform the currency conversion if the currency is not EUR, and return the converted value as minPrice. If you study search.php, you will see the following section of SQL in each of the lines that construct the search query based on the query type:

You could try changing each instance of the above code as follows:

I would test this first with the exchange rate hard coded, and if it produces the results you want then modify the script further to pick the exchange rate from the database and load it into a variable before constructing the SQL. If you need help doing this, let me know the structure of your exchange rate table and i'll post the code for extracting it into a variable using Price Tapestry's db library...

Cheers,
David.

Hello,

That works - thanks very much! The next query I move onto though is that the products page when you click on an item that has two different merchants, seems to display the one that comes first, not the one that is the best price. In products.php there is the line:

$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;

I believe this is the one that needs a similar thing doing to it.

Any more help would be appreciated.

Mike

Hi Mike,

Yes - a similar modification would be required. This time, instead of:

...try:

Just to explain a little further, the syntax of SQL's IF statement is as follows:

IF(test, then, else)

So in this situation, the test is whether currency='GBP', the THEN case returns the converted price if the test is true, otherwise just return the price as is.

Note how the ORDER BY clause has been changed to sort by the calculated sortPrice field. You will still need to do the currency conversion during display as this change won't affect the prices displayed at all.

Hope this helps!
Cheers,
David.

That's brilliant!

It took me a while to work out why the product page was displaying the cheapest merchant first, but with the wrong price - but that was a typo, my fault.

Thanks very much for your help!

Mike

Hi, I have implemented the currency conversion method (using 2 fields), and it works perfectly for featured/searchresults pages...

The individual product page, however, doesn't seem apply the hard-coded exchange rate. I modified products.php as follows:

$sql = "SELECT *,IF(currency='GBP',(price * 1.975),price) AS sortPrice FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY sortPrice LIMIT ".$config_resultsPerPage;

It still shows 50.00 USD, when it should convert to 98.75 USD.

Is there an additional step required?

Thanks!

Hi,

That calculation only applies to sortPrice, which looking at your code is only used to do the sort; not for actual display anywhere.

If you then want to use sortPrice as the actual display price, simply modify html/product.php and change:

$mainProduct["price"] to $mainProduct["sortPrice"]

and

$priceProduct["price"] to $priceProduct["sortPrice"]

That's probably the easiest way around it...

Cheers,
David.

.. and of course, that worked!!

Thanks so much for all your help David, it's very much appreciated.

T.

David

I'm looking for a simple way to implement price comparison with feeds from different countries and wondered if you would comment on this:

Add three fields to the products database:

- a ccy code for the existing price field
- a "Sold in" ccy code
- a "sold in" price.

Pick as base ccy, say USD. The current price field will always hold the price in the base currency so that no changes are needed to the price comparison engine.

Make the import routine do the currency conversion:

If Sold In ccy code NE Base ccy code THEN
get the relevant exchange rate (from an array in admin.php or a table somewhere?)
Base Price used by PT in the database = the converted "Sold In" price
ELSE
Set the Base Price = the Sold In Price

This way the base price would be used by PT but the Sold in Price and Sold In ccy could be displayed for info if required.

Upside is minimal changes to PT
Downside is feeds would have to be re-imported to do the ccy conversions. If currencies are very volatile this might be too much work but if rates are only picked up once a month, it might be OK

Hi Alastair,

If you're going to try and do this that is certainly the way I would go about it.

- To summarise, do the currency conversion during import
- Add fields as required to hold price in different currencies
- Compare as normal on the single price field
- Display different currencies on the product page as required

Cheers,
David.

This works fine thank you.

For another PT site I would like to have the opportunity for the visitor to select either a "US" site displaying products and prices from US merchants or a UK/European site disaplying them from Europe, or a "both" option. I do store currency code in the database.

I'd appreciate any ideas on how to do this. Many thanks

Alastair

Hi Alastair,

Most sites that offer this sort of functionality use cookies. You would probably have a common piece of code (perhaps in your header) that generates the select box, and in response to changing it sets a cookie that contains the users preference. The select box can also be defaulted to the chosen currency / region.

If you called this cookie "region" (for example), this would give you the variable $_COOKIE["region"] that you could refer to throughout your site to decide which currencies products to display.

Check the following page for more info on using cookies:

http://uk.php.net/manual/en/function.setcookie.php

Cheers,
David.