CodeIgniter Forums
Storing accented characters in mysql db - 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: Storing accented characters in mysql db (/thread-9814.html)



Storing accented characters in mysql db - El Forum - 07-09-2008

[eluser]Desireco[/eluser]
I am working on small cms that powers wine card. Often descriptions or names will include accented characters and I didn't have much luck with this. I get ??? in my database. If I enter them directly in mysql (from command line) everything works fine.

$sql=$this->db->update_string('wines',$data,"id=$id");
$this->db->query($sql);

When I try this 'á à è é â ñ ü û ' it goes through the update statement ok, but in database I get ? ? ? ? ? ? ? etc. So it must be that query screws it up.

I am sure there is simple and easy solution to this. If someone who handled this could enlighten me, I would really appreciate.

Thanks in advance.

Zeljko Dakic


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]walterblaurock[/eluser]
One method would be to replace the characters with their numeric codes. A list of such codes can be found here: http://www.december.com/html/spec/codes.html

Doing this will ensure compatibility both with your database and with your viewers' browsers.

I am really not sure of the best way to go about doing this, as in the past I have simple written functions to remove accents (e.g. turn é into e).

Perhaps you can do some kind of replace, where it would take é and replace it with &#thecode;. Assuming you only do this one (when inserting the data), the overhead required by such a function (which arguably wouldnt be that much) wouldn't become an issue, unless you are adding wines by the boatload.

I hope someone else has a better answer for you. Best of luck!


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Bramme[/eluser]
htmlentities() ???

It's got probably something to do with the encoding of your database...


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Desireco[/eluser]
No, DB is ok, htmlentities would be used to display from db of course, I wouldn't like to store in a db in such way. DB is ok as I said, you can enter and it will be stored like it is entered. Query method is doing some escaping that I think is creating a problem.

[quote author="Bramme" date="1215699628"]htmlentities() ???

It's got probably something to do with the encoding of your database...[/quote]


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Desireco[/eluser]
Actually I just tried again and seems that htmlentities does the work well. I don't know why it didn't worked before. Must be that I overlooked something. Anyway, it solves the problem.


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]flojon[/eluser]
You need to have the same encoding both in your HTML and in the database or you'll get in trouble. But htmlentities() is probably a simple way around that...


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Desireco[/eluser]
[quote author="flojon" date="1215718465"]You need to have the same encoding both in your HTML and in the database or you'll get in trouble. But htmlentities() is probably a simple way around that...[/quote]

Maybe that was the issue, db has good encoding but maybe in html I didn't specify. Smile Very good point. Thanks.

Zeljko


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Chicken's Egg[/eluser]
I had ones a problem like TS. I stored my data in a MySQL-database using the charset utf-8 for the database. Al special characters like é and ü where well stored in the database, but when I printed them on my webpage they became all characters like ?. I found that very strange, as I had defined the charset of the page as utf-8 aswell. When I redefined it to iso-8859-1 the problem was solved.


Storing accented characters in mysql db - El Forum - 07-10-2008

[eluser]Desireco[/eluser]
OK now I kind of figured it all out. Here is what you need to do when you have issue like this.

Page needs to have something like this:
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type" />

then in your code before saving data:
$data['category']=htmlentities($data['category'], ENT_NOQUOTES, "UTF-8",false);

and that is it. One of the issues I had is that I had here different encoding then my page and that is why these things didn't work. It is actually very simple.

Thanks for suggestions.


Storing accented characters in mysql db - El Forum - 07-11-2008

[eluser]Unknown[/eluser]
@Desireco

i wonder why CI coudn't do that on his one, as soon you define UTF8 in the settings..
would save a lot of manuell adding
htmlentities(..., ENT_NOQUOTES, “UTF-8”,false);

well.. maybe some time it will find its way into the core