?
Solved

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

Posted on 2008-10-17
7
Medium Priority
?
597 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 750 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 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