CodeIgniter Forums
when sql query has utf character in it, codeigniter doesn't execute query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: when sql query has utf character in it, codeigniter doesn't execute query (/thread-10988.html)



when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-21-2008

[eluser]aman_tuladhar[/eluser]
Codeigniter ver. 1.63

database.php has this setting
config > database.php
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

MySQL database and table in it has collation as utf8_general_ci

when I issue query like this
$query = $this->db->query("select product_id from tbl_product where title = 'Consumíveis'");
echo $query->num_rows();

It gives 0 no. of rows,
when I run the same query inside phpmyadmin it gives 2 no. of rows as it's result.
Being surprise, I tested the query with native php and mysql function, and it also display no. of records as 2.

So I am wondering if it is codeigniter bug or I have missed something some where in codeigniter setting ?
Any idea ?


when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-21-2008

[eluser]Randy Casburn[/eluser]
Please check to see if you're sending a content type meta-tag in your HTML header thus:

Code:
<meta http-equiv="Content-type" content="text/html; charset=utf-8" />

Might be a problem for you to start your search. If not set explicitly, there is no telling what your browser is doing to you.

Randy


when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-21-2008

[eluser]aman_tuladhar[/eluser]
Thanks Randy but the putting meta tag with character set UTF8 didn't solve the problem. However it seems that you also need to encode the model, controller, view file into UTF8 wihout BOM encoding to be able to execute query which has characters other than english.

Let me give example:
suppose u want to execute query like this,
$query = $this->db->get_where('tbl_product', array('title'=>"договору с Грузией"));

I used notepad++ to convert file (model, and controller) encoding from ansi to UTF8 without BOM, and magic happened the query executed.

Spend my whole day to this problem, hope it helps somebody else.


when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-22-2008

[eluser]Randy Casburn[/eluser]
Notepad++ is automatically detecting the filetype. And it appears, for your language it is doing it incorrectly. This is from notepad++ docs:

Quote:Notepad++ "guesses" the encoding mode of the loaded file by seeing if the unicode characters is present in the file. If not, then the ANSI mode will be set automatically.

This happens even if you set UTF-8 without BOM as the file type sometimes.

I would force all files to be saved in UTF-8 and bypass this automatic feature if it is possible.

Randy


when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-23-2008

[eluser]aman_tuladhar[/eluser]
I think I just found more practical way,
utf8_encode() function seems to do the trick no need to monkey around with file encoding.

example:
$sql = utf8_encode("select * from tbl_product where title='Сборная'");
$this->db->query($sql);


when sql query has utf character in it, codeigniter doesn't execute query - El Forum - 08-24-2008

[eluser]Randy Casburn[/eluser]
Whatever works for you. Either way you are "monkeying around with the encoding" right?

Code:
[ source ] ->  [ encode ] -> [ manipulate ] -> [ generate ] -> [ output ]

From a CI perspective if the Source is already encoded the flow looks like this...

Code:
[ source ] ->  [ manipulate ] -> [ generate ] -> [ output ]

From a philosophical perspective, implementing your hack is really just a bandage on a wound that will need to be looked at again and again. It seems implementing true UTF-8 enabled source files as input to a system expecting UTF-8 source data would be a more appropriate fix.

Either way thought, I'm glad you got it fixed.

Randy