Questions:
1. what is the current database collation and character set? check with
whatever GUI tool you use to admin the db
2. Are they the same? ie UTF-8
3. the query of course would be correct since it hasn't touched mysql yet.
Its a straight representation from PHP.
Notes:
1. Spaces in DB table/field names are a really bad idea and should be
avoided if possible. Replace the spaces with underscores if you want the
appearance of the space
Bastien
From: Renich Bon Ciric <renich@xxxxxxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Subject: Possible MySQLi extension BUG!
Date: Fri, 09 Sep 2005 12:50:31 -0500
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:
directorio
- 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.
My theory is this:
Consider:
<?php
$link = mysqli_connect('mysql.fedora', 'root', 'idontrecall',
'directorio');
if ( isset($_POST['submit']) && $_POST['submit'] == 'Search' )
{
$query = "SELECT *
FROM `Empresa`,
`Presidente`,
`Asistente de Presidencia`,
`Director`,
`Asistente de Dirección`, `
Centro de Distribución 01`,
`Centro de Distribución 02`,
`Centro de Distribución 03`,
`Centro de Distribución 04`,
`Centro de Distribución 05`,
`Centro de Distribución 06`,
`Centro de Distribución 07`,
`Centro de Distribución 08`,
`Centro de Distribución 09`,
`Centro de Distribución 10`
WHERE Empresa.Emp_Gro LIKE '%$_POST[input]%'
AND Empresa.ID = Presidente.ID
AND Empresa.ID = `Asistente de Presidencia`.ID
AND Empresa.ID = Director.ID
AND Empresa.ID = `Asistente de Dirección`.ID
AND Empresa.ID = `Centro de Distribución 01`.ID
AND Empresa.ID = `Centro de Distribución 02`.ID
AND Empresa.ID = `Centro de Distribución 03`.ID
AND Empresa.ID = `Centro de Distribución 04`.ID
AND Empresa.ID = `Centro de Distribución 05`.ID
AND Empresa.ID = `Centro de Distribución 06`.ID
AND Empresa.ID = `Centro de Distribución 07`.ID
AND Empresa.ID = `Centro de Distribución 08`.ID
AND Empresa.ID = `Centro de Distribución 09`.ID
AND Empresa.ID = `Centro de Distribución 10`.ID";
// Make the query
$result = mysqli_query($link,$query) or die(mysqli_error($link));
// Fetch the results
while ($data = mysqli_fetch_assoc($result))
{
// Print the data obtained
print_my_data($data);
}
}
// Close conection
mysqli_close($link);
?>
if you add a "print $query;" just before it makes the query; and as long as
you have your webpage with the charset meta-tag set to "utf-8" and
everything utf-8 encoded... you'll get the right query...
this is
SELECT * FROM `Empresa`, `Presidente`, `Asistente de Presidencia`,
`Director`, `Asistente de Dirección`, ` Centro de Distribución 01`, `Centro
de Distribución 02`, `Centro de Distribución 03`, `Centro de Distribución
04`, `Centro de Distribución 05`, `Centro de Distribución 06`, `Centro de
Distribución 07`, `Centro de Distribución 08`, `Centro de Distribución 09`,
`Centro de Distribución 10` WHERE Empresa.Emp_Gro LIKE '%try this%' AND
Empresa.ID = Presidente.ID AND Empresa.ID = `Asistente de Presidencia`.ID
AND Empresa.ID = Director.ID AND Empresa.ID = `Asistente de Dirección`.ID
AND Empresa.ID = `Centro de Distribución 01`.ID AND Empresa.ID = `Centro de
Distribución 02`.ID AND Empresa.ID = `Centro de Distribución 03`.ID AND
Empresa.ID = `Centro de Distribución 04`.ID AND Empresa.ID = `Centro de
Distribución 05`.ID AND Empresa.ID = `Centro de Distribución 06`.ID AND
Empresa.ID = `Centro de Distribución 07`.ID AND Empresa.ID = `Centro de
Distribución 08`.ID AND Empresa.ID = `Centro de Distribución 09`.ID AND
Empresa.ID = `Centro de Distribución 10`.ID
following the error
Table 'directorio.Asistente de Dirección' doesn't exist
As you can see, this is not logical. You had your query printed just above
and it printed out fine!... Sorry, if i got a little carried away...
This is why i think that the mysqli extension haves a bug. I have my
connection, server, etc... variables set to UTF-8 in MySQL. The one that is
converting the code while using the "mysqli_fetch_assoc()" function is
php...
If anybody knows how to correct this... or how to make the example work...
please tell me. I have worked on this too much.
I know that if I change the tablenames, it will work. In fact, a query like
SELECT *
FROM `Empresa`;
Works perfectly fine.
<dramatically> Help me and all the foreign php and mysql users use our own
characters! </dramatically>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
