Country selection box problem

[renato.aloi]

Hi guys!

I am facing a strange problem with country select box in account creation form.

The above mentioned select box is appearing for me with only the last option, that is Zimbawe. Check out the image bellow...

ANNEX 1

Digging a little bit of code I found out the problem is with my language extension installation. I have installed brazilian portuguese language.

The problem is in model/localisation/country.php at line 62 through 69. For better understanding, follows the code:

--------------------------------------------------------------------
$query = $this->db->query("SELECT *, COALESCE( cd1.name,cd2.name) as name
                   FROM " . $this->db->table("countries") . " c
                   LEFT JOIN " . $this->db->table("country_descriptions") . " cd1
                      ON (c.country_id = cd1.country_id AND cd1.language_id = '" . (int)$language_id . "')
                   LEFT JOIN " . $this->db->table("country_descriptions") . " cd2
                      ON (c.country_id = cd2.country_id AND cd2.language_id = '" . (int)$default_language_id . "')
                   WHERE c.status = '1'
                   ORDER BY cd1.name,cd2.name ASC");
--------------------------------------------------------------------

In the line 67 we have a LEFT JOIN to include also country descriptions for the default language. The thing is the (int)$default_language_id is becoming 0 (zero).

So, the query results NULL for country_id field, as follows:

ANNEX 2

Any suggestions?

Thanks in advance!
Renato Aloi

[abolabo]

you found bug in sql.
just replace your SQL-query with this

Code: [Select]

$query = $this->db->query("SELECT *,
COALESCE( cd1.language_id,cd2.language_id) as language_id,
COALESCE( cd1.country_id,cd2.country_id) as country_id,
COALESCE( cd1.name,cd2.name) as name
    FROM " . $this->db->table("countries") . " c
    LEFT JOIN " . $this->db->table("country_descriptions") . " cd1
    ON (c.country_id = cd1.country_id AND cd1.language_id = '" . (int)$language_id . "')
    LEFT JOIN " . $this->db->table("country_descriptions") . " cd2
    ON (c.country_id = cd2.country_id AND cd2.language_id = '" . (int)$default_language_id . "')
    WHERE c.status = '1'
    ORDER BY cd1.name,cd2.name ASC");to solve issue

[renato.aloi]

Thank you for the information!

Best Regards,
Renato