Solved

French text retrieved from database is not properly displayed for example (à, accent grave)

Posted on 2008-10-17
7
580 Views
Last Modified: 2013-12-13
I want to display a French text "Bienvenu à ACT" retrieved from MySQL database but on IE or FireFox it displays "Bienvenu ... ACT". Instead of displaying "à" it does show "..." [points de suspension]. Below is the output of select statement from my database:

mysql> select * from tbl_contents;
+----------+---------+------------------+
| LangCode | LocCode | Content          |
+----------+---------+------------------+
| en       | Marquee | Welcome to ACT   |
| fr       | Marquee | Bienvenu à ACT   |
| es       | Marquee | Bienvenido a ACT |
+----------+---------+------------------+
3 rows in set (0.00 sec)

I can see it properly from this select statement.

I also tried to run my page as a PHP script from Command Prompt and I see it output as is of database "Bienvenu à ACT" correctly. The problem is just when it is displayed on the browser.

I also try to changed Encoding on the page but it seems no luck to correctly this French text.

Which one should I use to create my database:

CREATE DATABASE actdb CHARACTER SET ucs2 COLLATE ucs2_general_ci;

or

CREATE DATABASE actdb CHARACTER SET utf8 COLLATE utf8_general_ci;
function displayContent($host, $db, $user, $password, $lang, $pos)

{

 $sql_stmnt = "SELECT content FROM tbl_contents WHERE  LangCode =" . chr(39) . "$lang" . chr(39) . " and LocCode =" . chr(39) . "$pos" . chr(39);

 mysql_connect($host, $user, $password) or die(mysql_error());

 mysql_select_db($db);	

 $results = mysql_query($sql_stmnt) or printf("%s", mysql_error( ));

 while($row = mysql_fetch_row($results)) {

	echo $row[0];	

 }

}

Open in new window

0
Comment
Question by:tballah
  • 3
  • 2
  • 2
7 Comments
 
LVL 1

Author Comment

by:tballah
ID: 22743866
the I tried to use Navicat for MySQL to display the content of the tbl_contents and I see "..." instead of à. So I think that the problem is from text stored in the table. I used the below statements to create my database. What character set and collation should I use?
CREATE DATABASE actdb CHARACTER SET utf8 COLLATE utf8_general_ci;
 

CREATE TABLE Tbl_Contents

(

	 LangCode VARCHAR(2) NOT NULL,

	 LocCode VARCHAR(20) NOT NULL,

	 Content VARCHAR(255) NOT NULL,

	 PRIMARY KEY(LangCode, LocCode)

) ENGINE=MyISAM;
 

INSERT INTO Tbl_Contents VALUES('en','Marquee','Welcome to ACT');

INSERT INTO Tbl_Contents VALUES('fr','Marquee','Bienvenu à ACT');

INSERT INTO Tbl_Contents VALUES('es','Marquee','Bienvenido a ACT');

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 22747564
You can simply use, ""characterEncoding=UTF-8  to store the multi language strings in the database.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 22747568
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 1

Author Comment

by:tballah
ID: 22748127
I dropped my database and recreated it again as the one the code snippet. When I use Navicat to display the inserted data in the table tbl_contents I still see "..." instead of à

What is my mistake in doing so? Would you have any suggestion or comment?
CREATE DATABASE actdb CHARACTER SET utf8 COLLATE utf8_bin;
 

DROP TABLE IF EXISTS Tbl_Contents;

CREATE TABLE Tbl_Contents

(

	 LangCode VARCHAR(2) NOT NULL,

	 LocCode VARCHAR(20) NOT NULL,

	 Content VARCHAR(255) NOT NULL,

	 PRIMARY KEY(LangCode, LocCode)

) ENGINE=MyISAM;
 

INSERT INTO Tbl_Contents VALUES('en','Marquee','Welcome to ACT');

INSERT INTO Tbl_Contents VALUES('fr','Marquee','Bienvenu à ACT');

INSERT INTO Tbl_Contents VALUES('es','Marquee','Bienvenido a ACT');

Open in new window

0
 
LVL 29

Accepted Solution

by:
fibo earned 250 total points
ID: 22748360
tballah,

Below what I wrote at http://www.experts-exchange.com/Database/MySQL/Q_23635041.html
1 - These character sets problems are really a nightmare, since the complete processing chain has half a dozen of steps, everyone of which might be incorrect.

2 - When you have some time, just have a read at
http://www.sitepoint.com/article/guide-web-character-encoding
which is one of the most complete articles on the subject.

3 - A typical list of all problems that might go wrong, and a suggestion to solve that. This reflects hard-learned lessons when building a multi-character sets site at
http://www.mae.u-paris10.fr/limc-france/
a - displaying the form (for input) and the result: check that all of them are UTF-8. For that, you need that the first non php text, ie the first html code issued is something like
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
b - for the same pages, I strongly recommend that your pages too are coded in UTF-8 (not 'Unicode'!). To make a long story short: this is really a nightmare in itself, since the PHP IDEs are not always handling that satisfactorily. Some years ago, when comparing commercial products like Nusphere and Zend, I found that:
-- at that time (no idea if still true), Nusphere was handling Unicode but NOT UTF-8 (if you look at the html code of the site for /limc-france/, you will see that ALL characters are really written not represented by some hexa code!); Zend was able to handle correctly UTF-8, BUT was not able to create these files!
-- I saved lots of time with the (commercial) text editor Ultra-Edit which handles perfectly all these code problems
c - I could not use the character sets code and collation because at that time they were not available with MySQL (pre 4.1). In my case, it was not a problem as long as I could store the characters... there were some slight problems (eg, when transferring data from Access to MySQL) but they were finally solved.
d - one of the main problems I found was to know why exactly I was getting ??? characters in some display -more precisely which error I had made. It was very helpful to be sure to display exactly the characters that were in MySQL, even if my display program had problems. I heavily used a trick: display the string normally, but also as UTF-8 decoded AND as UTF-8 encoded! This was really helpful, eg when changing character codes in the browser.
0
 
LVL 1

Author Closing Comment

by:tballah
ID: 31507233
Thank you for your time and the recommended sites, they're informative.
0
 
LVL 29

Expert Comment

by:fibo
ID: 22748521
B-) ravi d'être utile, merci pour les points et la note
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now