Page 1 of 1

reading DB info (special chars)

Posted: Fri Apr 01, 2011 2:33 pm
by fael097
hi, I'm making a website, in portuguese, and i'm trying to retrieve characters with accent signals such as é ê á etc.

while when I directly type words with such characters directly in my HTML, it displays correctly (i'm using utf-8), if i save the same words into a mysql DB, and echo them out with php, they will appear with missing characters, you know, the "?" inside a square.

im clueless. any help is appreciated.

thanks in advance

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 2:57 pm
by McInfo
The easiest solution is to make sure that everything is using the same encoding: the database, tables, and fields (collation); the PHP files themselves; the database client connection (set_charset); and any Content-Type headers or meta tags.

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 4:28 pm
by fael097
McInfo wrote:The easiest solution is to make sure that everything is using the same encoding: the database, tables, and fields (collation); the PHP files themselves; the database client connection (set_charset); and any Content-Type headers or meta tags.
tried that, didnt work. :banghead:

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 4:47 pm
by fael097
I made a simple test for debugging, heres my code:
(I typed "ácéntôão" e "tôrrãodeaçúcar" directly on html for testing, those are exactly the same words I've put on my database.)

Code: Select all

<?php
session_start();
$host_db="localhost";
$name_db="test";
$login_db="root";
$pass_db="123456";
$conn_db=mysql_connect($host_db, $login_db, $pass_db)or die("Could not connect. ".mysql_error());
$select_db=mysql_select_db($name_db, $conn_db)or die("Could not select database. ".mysql_error());
?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
</head>
<body>
ácéntôão<br />
tôrrãodeaçúcar<br />
<?php
$sql="SELECT * FROM test ORDER BY id LIMIT 3";
$result=mysql_query($sql);
if(mysql_num_rows($result))
{
	while($row=mysql_fetch_row($result))
	{
		echo $row[1]."<br />";
	}
}
?>
</body>
</html>
my result:
ácéntôão
tôrrãodeaçúcar
�c�nt��o
t�rr�odea��car
words in HTML appear exactly as I typed them, but the ones coming from DB appear with those unknown characters.
what to do? i tried everything

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 5:02 pm
by McInfo
See what mysql_client_encoding() returns. Then use mysql_set_charset() to set the encoding explicitly.

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 5:20 pm
by fael097
McInfo wrote:See what mysql_client_encoding() returns. Then use mysql_set_charset() to set the encoding explicitly.
thanks. my html encoding is utf-8, but client_encoding returns latin1. set charset doesnt appear to work. whats the correct syntax?

Re: reading DB info (special chars)

Posted: Fri Apr 01, 2011 6:28 pm
by McInfo
Try

Code: Select all

mysql_set_charset('utf8', $conn_db);
MySQL Manual: Character Sets and Collations That MySQL Supports

Re: reading DB info (special chars)

Posted: Sat Apr 02, 2011 9:05 pm
by fael097
McInfo wrote:Try

Code: Select all

mysql_set_charset('utf8', $conn_db);
MySQL Manual: Character Sets and Collations That MySQL Supports
will try it now.

but while i dont, i just replaced my html encoding to latin1, and it did the trick.
is it a problem to leave it latin1? like some users not being able to see anything?

Re: reading DB info (special chars)

Posted: Mon Apr 04, 2011 9:29 am
by fael097
that did it, however, i have to leave it in my code, whenever i remove it, everything goes back to unknown characters.
is it possible to permanently change the database charset?
its weird, because i changed everything on my db manually, to utf-8, but that didnt work, i still see with unknown characters.

Re: reading DB info (special chars)

Posted: Tue Apr 05, 2011 12:50 pm
by McInfo
mysql_set_charset() defines the character set to be used for the client connection; it does not change the character set of the database. (It is unclear whether you understood that.) That is why its effect is temporary.
PHP Manual wrote:Note: [mysql_set_charset()] is the preferred way to change the charset. Using mysql_query() to execute SET NAMES .. is not recommended.
Read this chapter of the MySQL manual: Internationalization and Localization.