PHP & MySQL UTF-8 (Non-English) Search Problem

PHP:- German, French, Arabic etc MySQL Search Issues

[Sherif]

Hey Guys,

I am not sure exactly where I should post this question, but I have a feeling that this question should be posted here.

Currently i am working on a multilingual site where languages other than english are used mainly being "arabic".

I am using ajax in my PHP code to send search data to a page sending the text using the following command encodeURIComponent as shown below:

xmlhttp.open("GET","customer_result.php?name="+encodeURIComponent(str),true);

when i echo $_GET['name']; i get the correct letters being displayed.

The problem is that when i query a database, i do not get any results.

It must be noted, when i use phpMyAdmin, and do a SQL query using arabic text inside the query, i get the results correctly without any issues.

The PHP code that i use is the following:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname1_PersonResults = "-1";
if (isset($_GET['name'])) {
$colname1_PersonResults = $_GET['name'];
}
$query_PersonResults = sprintf("SELECT * FROM owners WHERE firstname_en LIKE %s OR lastname_en LIKE %s", GetSQLValueString("%" . $colname1_PersonResults . "%", "text"),GetSQLValueString("%" . $colname1_PersonResults . "%", "text"));
$PersonResults = mysql_query($query_PersonResults, $connection) or die(mysql_error());
$row_PersonResults = mysql_fetch_assoc($PersonResults);
$totalRows_PersonResults = mysql_num_rows($PersonResults);
?>

the echo of $_GET['name'] appears to be correct, but for some reason, the query doesn't seem to be functioning correctly.

Any ideas how we can make the php flexible to send the utf-8 values to the mysql to search?

I have searched everywhere on the internet and couldn't find any answer.

Thanks a lot for your support and cooperation.


Sincerely,
Sherif

[SteveWh]

Although this is outside my experience, the things I might look at are:

Ensure the page itself, where people fill out the form, has an encoding type of UTF-8 so that the form fields are submitted with that encoding.

If it still doesn't work, take a look at the PHP function utf8_encode() and possibly other related UTF-8 functions.

It seems like the important thing would be, assuming that the database contents are encoded in UTF-8, that the string submitted by PHP to MySQL must have the same encoding, even if you have to force it somehow.

[Sherif]

I have the following settings adjusted:

MySQL connection collation: utf8_general_ci
The table field collations are set as: utf8_unicode_ci

in the php pages, i have the following commands set:

<?php
/*Adding MySQL UTF-8 Support*/
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8'",$connection_name);
mb_internal_encoding("UTF-8");
?>

with all these settings in mind, when inserting, reading any non english language, everything works correctly with no issues at all.

The problem is that when i use php to perform a MySQL query using non english characters, nothing is obtained although it works perfectly when i use the same query in PhpMyAdmin.

The non-english characters submitted from the form are sent correctly to the other page, but something seems to be stopping php from sending the query correctly.

Thanks for the support.

Sincerely,
Sherif

[Sherif]

I think I found a solution...

i had a segment in my code as follows:

<?php
/*Adding MySQL UTF-8 Support*/
mysql_query("SET NAMES 'utf8'", $ashrafeya_admin);
mysql_query("SET CHARACTER SET 'utf8'",$ashrafeya_admin);
mb_internal_encoding("UTF-8");
?>

Then i had another php code below it containing the query for the search... AS i mentioned earlier, something was stopping the query... so I tried the following:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname1_PersonResults = "-1";
if (isset($_GET['name'])) {
$colname1_PersonResults = $_GET['name'];
}
mysql_query("SET NAMES 'utf8'", $ashrafeya_admin);
$query_PersonResults = sprintf("SELECT * FROM owners WHERE firstname_en LIKE %s OR lastname_en LIKE %s", GetSQLValueString("%" . $colname1_PersonResults . "%", "text"),GetSQLValueString("%" . $colname1_PersonResults . "%", "text"));
$PersonResults = mysql_query($query_PersonResults, $connection) or die(mysql_error());
$row_PersonResults = mysql_fetch_assoc($PersonResults);
$totalRows_PersonResults = mysql_num_rows($PersonResults);
?>

when i added the SET NAMES 'UTF' as shown above everything worked...

Could someone explain why this didn't work even though I had added it in another php query identifying this, and it worked when I added the query just before calling the search query?


Thanks a lot for your support.

Sincerely,
Sherif

[coopster]

You should only have to run the pre-query once per request, preferably right after opening the database connection. That's how I handle it. However, if you have control over the server you can modify a conf setting that is applied at mysqld server startup so you don't have to run the pre-query. This will apply across the board though so if you don't want it applied to certain databases you would have to make adjustments the other way around.

[Sherif]

Thanks coopster,

I guess i understand what you mean...

I didn't know i had to run it just right after opening the database connection.

Thanks a lot for your support.

Sincerely,
Sherif

[coopster]

Well, you don't, I prefer to do so if I know the server is not set up with utf8 as default charset. I use the mysqli functions.

[php.net...]