Author Topic: country not translated due to LEFT JOIN issue  (Read 1771 times)

alain.roger

  • Jr. Member
  • **
  • Posts: 135
    • Korcule & Kolieska
country not translated due to LEFT JOIN issue
« on: August 15, 2010, 11:00:04 am »
Hi,

i run Joomla 1.5.20 with joomfish 2.0.4 and VM 1.1.5.
i've discovered that evern if countries are translated into a different language than english, country is always isplayed in english during checkout process.

digging a little bit i discovered that in ps_checkout.php, a function display_address has the following SQL statement:
Quote
$q  = "SELECT * FROM #__{vm}_user_info ";
      $q .= "INNER JOIN #__{vm}_country ON (#__{vm}_user_info.country = #__{vm}_country.country_3_code OR #__{vm}_user_info.country = #__{vm}_country.country_2_code) ";
      $q .= "LEFT JOIN #__{vm}_state ON (#__{vm}_user_info.state = #__{vm}_state.state_2_code AND #__{vm}_state.country_id = #__{vm}_country.country_id) ";
      $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
      $q .= "AND address_type='BT' ";
in this case, country will remain always in english.

however, a few test and i found that if you comment the left join line, the country will be correctly discplay in the checkout page of your site.

however, in order to allow US, canada and other country having state indicated in checkout process, i'm not able to fix the issue with this left join.

any idea what could make joomfish not transating the country when LEFT JOIN is available ?

thanks a lot,

A.
Production server : Joomla : 2.5.8 / VM : 2.0.12f / PHP : 5.4.8 /MySQL : 5.5.28
-------------------
http://www.korcule-kolieska.sk

Testing server: Joomla 2.5.2 / VM: 2.0.2 stable
-------------------------------------------------------------------
What does not kill you, makes you strong

Krin

  • Beginner
  • *
  • Posts: 1
Re: country not translated due to LEFT JOIN issue
« Reply #1 on: April 26, 2011, 03:29:57 am »
Just replace the original SQL query
Quote
$q  = "SELECT * FROM #__{vm}_user_info ";
      $q .= "INNER JOIN #__{vm}_country ON (#__{vm}_user_info.country = #__{vm}_country.country_3_code OR #__{vm}_user_info.country = #__{vm}_country.country_2_code) ";
      $q .= "LEFT JOIN #__{vm}_state ON (#__{vm}_user_info.state = #__{vm}_state.state_2_code AND #__{vm}_state.country_id = #__{vm}_country.country_id) ";
      $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
      $q .= "AND address_type='BT' ";

by this
Quote
   $q  = "SELECT i.*, c.country_id as key1, c.*, s.state_id as key2, s.* FROM #__{vm}_user_info i ";
   $q .= "LEFT JOIN #__{vm}_country c ON (i.country=c.country_3_code OR i.country=c.country_2_code) ";
   $q .= "LEFT JOIN #__{vm}_state s ON (i.state=s.state_2_code AND s.country_id=c.country_id) ";
   $q .= "WHERE user_id='" . $auth["user_id"] . "' ";
   $q .= "AND address_type='BT'";

For me it's work fine.