Description:
Ok,
Firs of all, I wanna say I have spent a whole week looking for answers elsewhere. The reason of this message is to look for a practical solution.
My Systems:
Server:
Fedora Core 4 (up2dated)
PHP5.04 (cli)
MySQL 14.7 Distrib 4.1.12
using mysqli extension
Apache/2.0.54 (Fedora)
Workstation:
Windows XP SP2
Zend Studio Enterprise 4.02
Well, let me explain the problem:
I'm developing some aplications that require the use of foreign characters. The language is spanish, so you use accends or tilde on some works (example, comisión, niño, etc).
I have a simple query that uses tables (named with this foreign characters). Example:
test
- Empresa
- Dirección
- Asistente de Dirección
A query to this database should be as follows:
SELECT *
FROM `Asistente de Dirección`;
This returns an error like:
Table 'directorio.Asistente de Dirección' doesn't exist.
I have changed EVERYTHING to utf-8 charset:
httpd.conf - AddDefaultCharset = UTF-8
php.ini - default_charset = "UTF-8"
mbstring.internal_encodign = UTF-8
mbstring.http_input = UTF-8
mbstring.http_output = UTF-8
mbstring.detect_order = UTF-8,SJIS,ASCII
my.cnf - [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
default-character-set=utf8
This config sets ALL my servers to utf-8
- Apache works perfectly fine in UTF-8, no problems
- PHP encodes everything to utf-8... at least thats what i think
- All my MySQL char variables are set to UTF-8, except "default-character-result" its set to NULL
As far as I know, there is no other config made to be done.
Things I have checked:
- Searched my.cnf, httpd.conf and php.ini for any "ISO-8859-1","latin1" strings.
- Checked the db file names (/var/lib/mysql/)
- Checked if the script files are UTF-8 encoded
- Checked if the browser is accepting the UTF-8 charset
- Done the query from the command line
How to repeat:
Run this php code:
<?php
// Connect to db
$link = mysqli_connect('192.168.1.110','root','molimte','test');
// Create tables
$query1 = "CREATE TABLE `Asistente de Direccion` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;";
$query2 = "CREATE TABLE `Asistente de Dirección` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;";
$query3 = "INSERT INTO `Asistente de Direccion` VALUES('1');";
$query4 = "INSERT INTO `Asistente de Dirección` VALUES('1');";
$query5 = "SELECT * FROM `Asistente de Direccion`;";
$query6 = "SELECT * FROM `Asistente de Dirección`;";
$result1 = mysqli_query($link,$query1) or die("Error in \$query1 <br>\n" . mysqli_error($link) . "<br> \n");
$result2 = mysqli_query($link,$query2) or die("Error in \$query2 <br>\n" . mysqli_error($link) . "<br> \n");
// Insert some info
$result3 = mysqli_query($link,$query3) or die("Error in \$query3 <br>\n" . mysqli_error($link) . "<br> \n");
$result4 = mysqli_query($link,$query4) or die("Error in \$query4 <br>\n" . mysqli_error($link) . "<br> \n");
// Get table's info
$result5 = mysqli_query($link,$query5) or die("Error in \$query5 <br>\n" . mysqli_error($link) . "<br> \n");
$result6 = mysqli_query($link,$query6) or die("Error in \$query6 <br>\n" . mysqli_error($link) . "<br> \n");
// Print info
while ( $data = mysqli_fetch_assoc($result5) ) {
print "<pre>";
print_r($data);
print "</pre>";
}
while ( $data1 = mysqli_fetch_assoc($result6) ) {
print "<pre>";
print_r($data1);
print "</pre>";
}
mysqli_close($link);
?>
Suggested fix:
If you add a this to the code:
$query0 = "SET NAMES utf8;";
$result0 = mysqli_query($link,$query0) or die("Error in \$query0 <br>\n" . mysqli_error($link) . "<br> \n");
So it looks like:
<?php
// Connect to db
$link = mysqli_connect('192.168.1.110','root','molimte','test');
// Create tables
$query0 = "SET NAMES utf8;";
$query1 = "CREATE TABLE `Asistente de Direccion` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;";
$query2 = "CREATE TABLE `Asistente de Dirección` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;";
$query3 = "INSERT INTO `Asistente de Direccion` VALUES('1');";
$query4 = "INSERT INTO `Asistente de Dirección` VALUES('1');";
$query5 = "SELECT * FROM `Asistente de Direccion`;";
$query6 = "SELECT * FROM `Asistente de Dirección`;";
$result0 = mysqli_query($link,$query0) or die("Error in \$query0 <br>\n" . mysqli_error($link) . "<br> \n");
$result1 = mysqli_query($link,$query1) or die("Error in \$query1 <br>\n" . mysqli_error($link) . "<br> \n");
$result2 = mysqli_query($link,$query2) or die("Error in \$query2 <br>\n" . mysqli_error($link) . "<br> \n");
// Insert some info
$result3 = mysqli_query($link,$query3) or die("Error in \$query3 <br>\n" . mysqli_error($link) . "<br> \n");
$result4 = mysqli_query($link,$query4) or die("Error in \$query4 <br>\n" . mysqli_error($link) . "<br> \n");
// Get table's info
$result5 = mysqli_query($link,$query5) or die("Error in \$query5 <br>\n" . mysqli_error($link) . "<br> \n");
$result6 = mysqli_query($link,$query6) or die("Error in \$query6 <br>\n" . mysqli_error($link) . "<br> \n");
// Print info
while ( $data = mysqli_fetch_assoc($result5) ) {
print "<pre>";
print_r($data);
print "</pre>";
}
while ( $data1 = mysqli_fetch_assoc($result6) ) {
print "<pre>";
print_r($data1);
print "</pre>";
}
mysqli_close($link);
?>
It will work. This should be not the solution. I have changed mysql's char variables to utf-8 and is STILL not accepting utf-8 charset untill i "set names utf8"
To change character variables permanently in mysql i used the following config
My.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
default-character-set=utf8
Description: Ok, Firs of all, I wanna say I have spent a whole week looking for answers elsewhere. The reason of this message is to look for a practical solution. My Systems: Server: Fedora Core 4 (up2dated) PHP5.04 (cli) MySQL 14.7 Distrib 4.1.12 using mysqli extension Apache/2.0.54 (Fedora) Workstation: Windows XP SP2 Zend Studio Enterprise 4.02 Well, let me explain the problem: I'm developing some aplications that require the use of foreign characters. The language is spanish, so you use accends or tilde on some works (example, comisión, niño, etc). I have a simple query that uses tables (named with this foreign characters). Example: test - Empresa - Dirección - Asistente de Dirección A query to this database should be as follows: SELECT * FROM `Asistente de Dirección`; This returns an error like: Table 'directorio.Asistente de Dirección' doesn't exist. I have changed EVERYTHING to utf-8 charset: httpd.conf - AddDefaultCharset = UTF-8 php.ini - default_charset = "UTF-8" mbstring.internal_encodign = UTF-8 mbstring.http_input = UTF-8 mbstring.http_output = UTF-8 mbstring.detect_order = UTF-8,SJIS,ASCII my.cnf - [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 default-character-set=utf8 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql] default-character-set=utf8 This config sets ALL my servers to utf-8 - Apache works perfectly fine in UTF-8, no problems - PHP encodes everything to utf-8... at least thats what i think - All my MySQL char variables are set to UTF-8, except "default-character-result" its set to NULL As far as I know, there is no other config made to be done. Things I have checked: - Searched my.cnf, httpd.conf and php.ini for any "ISO-8859-1","latin1" strings. - Checked the db file names (/var/lib/mysql/) - Checked if the script files are UTF-8 encoded - Checked if the browser is accepting the UTF-8 charset - Done the query from the command line How to repeat: Run this php code: <?php // Connect to db $link = mysqli_connect('192.168.1.110','root','molimte','test'); // Create tables $query1 = "CREATE TABLE `Asistente de Direccion` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;"; $query2 = "CREATE TABLE `Asistente de Dirección` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;"; $query3 = "INSERT INTO `Asistente de Direccion` VALUES('1');"; $query4 = "INSERT INTO `Asistente de Dirección` VALUES('1');"; $query5 = "SELECT * FROM `Asistente de Direccion`;"; $query6 = "SELECT * FROM `Asistente de Dirección`;"; $result1 = mysqli_query($link,$query1) or die("Error in \$query1 <br>\n" . mysqli_error($link) . "<br> \n"); $result2 = mysqli_query($link,$query2) or die("Error in \$query2 <br>\n" . mysqli_error($link) . "<br> \n"); // Insert some info $result3 = mysqli_query($link,$query3) or die("Error in \$query3 <br>\n" . mysqli_error($link) . "<br> \n"); $result4 = mysqli_query($link,$query4) or die("Error in \$query4 <br>\n" . mysqli_error($link) . "<br> \n"); // Get table's info $result5 = mysqli_query($link,$query5) or die("Error in \$query5 <br>\n" . mysqli_error($link) . "<br> \n"); $result6 = mysqli_query($link,$query6) or die("Error in \$query6 <br>\n" . mysqli_error($link) . "<br> \n"); // Print info while ( $data = mysqli_fetch_assoc($result5) ) { print "<pre>"; print_r($data); print "</pre>"; } while ( $data1 = mysqli_fetch_assoc($result6) ) { print "<pre>"; print_r($data1); print "</pre>"; } mysqli_close($link); ?> Suggested fix: If you add a this to the code: $query0 = "SET NAMES utf8;"; $result0 = mysqli_query($link,$query0) or die("Error in \$query0 <br>\n" . mysqli_error($link) . "<br> \n"); So it looks like: <?php // Connect to db $link = mysqli_connect('192.168.1.110','root','molimte','test'); // Create tables $query0 = "SET NAMES utf8;"; $query1 = "CREATE TABLE `Asistente de Direccion` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;"; $query2 = "CREATE TABLE `Asistente de Dirección` ( `ID` smallint(5) unsigned NOT NULL ) TYPE = MYISAM CHARACTER SET utf8;"; $query3 = "INSERT INTO `Asistente de Direccion` VALUES('1');"; $query4 = "INSERT INTO `Asistente de Dirección` VALUES('1');"; $query5 = "SELECT * FROM `Asistente de Direccion`;"; $query6 = "SELECT * FROM `Asistente de Dirección`;"; $result0 = mysqli_query($link,$query0) or die("Error in \$query0 <br>\n" . mysqli_error($link) . "<br> \n"); $result1 = mysqli_query($link,$query1) or die("Error in \$query1 <br>\n" . mysqli_error($link) . "<br> \n"); $result2 = mysqli_query($link,$query2) or die("Error in \$query2 <br>\n" . mysqli_error($link) . "<br> \n"); // Insert some info $result3 = mysqli_query($link,$query3) or die("Error in \$query3 <br>\n" . mysqli_error($link) . "<br> \n"); $result4 = mysqli_query($link,$query4) or die("Error in \$query4 <br>\n" . mysqli_error($link) . "<br> \n"); // Get table's info $result5 = mysqli_query($link,$query5) or die("Error in \$query5 <br>\n" . mysqli_error($link) . "<br> \n"); $result6 = mysqli_query($link,$query6) or die("Error in \$query6 <br>\n" . mysqli_error($link) . "<br> \n"); // Print info while ( $data = mysqli_fetch_assoc($result5) ) { print "<pre>"; print_r($data); print "</pre>"; } while ( $data1 = mysqli_fetch_assoc($result6) ) { print "<pre>"; print_r($data1); print "</pre>"; } mysqli_close($link); ?> It will work. This should be not the solution. I have changed mysql's char variables to utf-8 and is STILL not accepting utf-8 charset untill i "set names utf8" To change character variables permanently in mysql i used the following config My.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 default-character-set=utf8 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql] default-character-set=utf8