Convert charset in mysql query

Question

Hi,

I have a question about converting charset from inside mysql query.

I have a 2 databases. One for the website (joomla), the other for forum (IPB). I am doing query from inside joomla, which by default have "SET NAMES UTF8". I want to query a table inside the forum databases. A table called "ibf_topics". This table has latin1 encoding.

I do the following to select anything from the not-utf8 table.

//convert connection to handle latin1.
$query = "SET NAMES latin1";
$db->setQuery($query);
$db->query();

$query = "select id, title from other_database.ibf_topics";
$db->setQuery($query);
$db->query();
//read result into an array.

//return connection to handle UTF8.
$query = "SET NAMES UTF8";
$db->setQuery($query);
$db->query();

After that, when I want to use the selected tile, I use the following:

echo iconv("CP1256", "UTF-8", $topic['title'])

The question is, is there anyway to avoid all this hassle? For now, I can't change forum database to UTF8 and I can't change joomla database to latin1 :S

Answer 1

$query = "SET NAMES UTF8";
$db->setQuery($query);
$db->query();

$query = "select id, title from other_database.ibf_topics";
$db->setQuery($query);
$db->query();

Answer 2

you could open 2 database connections, 1 to joomla and one to IPB. you'll be using more resources, but it will make your code easier to read, and you can do all the configuration (charset etc) in the db setup. this will also make it easier to migrate later, in case your situation changes - you won't need to go through your code removing all the SET NAMES.