Ken Okech Ken Okech - 6 months ago 40
MySQL Question

MySQL SELECT on Arabic Word returns 0 results on PHP but does on SQLBuddy/phpMyAdmin

Problem

I am getting 0 results when searching for an Arabic word in a MySQL database using the

SELECT
query with PHP.

However, the same exact query yields results in alternative clients, namely SQLBuddy and the likes. Everything is encoded in UTF-8.

Code

<?php
$host = "localhost";
$username = "hans_wehr_client";
// i know my security is a joke :)
$password = "hans_wehr";
$database = "hans_wehr";
$conn = new mysqli($host, $username, $password, $database);
if ($conn == TRUE){
$search = $_GET["search"];
$encoded_search = utf8_encode($search);
echo $encoded_search."<br>";
header('Content-Type: text/html; charset=utf-8');
$sql = "SELECT * FROM dictionary WHERE ARABIC LIKE '$search'";
echo $sql."<br>";
mysqli_query($conn,"SET NAMES 'utf8'");
mysqli_query($conn,'SET CHARACTER SET utf8');
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
header('Content-Type: text/html; charset=utf-8');
echo $row["ARABIC"]. " - Meaning: " . $row["ENGLISH1"]. " " . $row["ENGLISH2"]. "<br>";
}
}else {
echo "0 results";
}
}

?>


Before the mods get the pitchforks, I have to clear up my troubleshooting logic.


  1. Encoding. I set the page encoding to
    utf-8
    using
    header('Content-Type:
    text/html; charset=utf-8');
    and ran the queries
    mysqli_query($conn,"SET NAMES 'utf8'");
    and
    mysqli_query($conn,'SET CHARACTER SET utf8');
    , this cleared up the
    ???????
    and
    Ùؤتا

    rendered instead of Arabic words issue. That is kind of a different
    issue. Source. and Source2.

  2. Database Charset. My database and columns are set to UTF-8.

  3. Other clients work. SQLBuddy/MySQL native client/ PHPMyAdmin appear to be working because running the same exact query yields result. Therefore I appear to be on the same bloody boat with him. The query
    SELECT * FROM dictionary WHERE ARABIC LIKE 'آخَر، أُخرى'
    returns a result on SQLbuddy but nada on PHP.



Possible solution:

Running the query
SELECT * FROM dictionary WHERE ARABIC LIKE 'آخَر، أُخرى'
yields me a result.

However running the query with a UTF-8 encoded version of the Arabic word returns 0 results.
SELECT * FROM dictionary WHERE ARABIC LIKE '&#1570;&#1582;&#1614;&#1585;&#1548; &#1571;&#1615;&#1582;&#1585;&#1609;'
I think this simulates PHP.

The UTF-8 Arabic word version is obtained by decoding the automatically URL encoded $[_GET] parameter i.e
%26%231570%3B%26%231582%3B%26%231614%3B%26%231585%3B%26%231548%3B+%26%231571%3B%26%231615%3B%26%231582%3B%26%231585%3B%26%231609%3B


Could it be that the MySQLi actually queries the
UTF-8 version
instead of the actual Arabic word? Therefore finding no match since they are different?

If so how can I explicitly tell PHP not to URL encode my search term and therefore pass it as it is?

Since according to my tinfoil theory,
http://localhost/hans_wehr/search_ar.php?search=آخَر، أُخرى
would work but
http://localhost/hans_wehr/search_ar.php?search=%26%231570%3B%26%231582%3B%26%231614%3B%26%231585%3B%26%231548%3B+%26%231571%3B%26%231615%3B%26%231582%3B%26%231585%3B%26%231609%3B


Inputs will be greatly appreciated.

Answer

Use html_entity_decode():

Use html_entity_decode() on your $_GET["search"] value

<?php
$host = "localhost";
$username = "hans_wehr_client";
// i know my security is a joke :)
$password = "hans_wehr"; 
$database = "hans_wehr";
$conn = new mysqli($host, $username, $password, $database);
if ($conn == TRUE){
    $search = $_GET["search"];
    $encoded_search =html_entity_decode($search, ENT_COMPAT, 'UTF-8');
    echo $encoded_search."<br>";
    header('Content-Type: text/html; charset=utf-8');
    $sql = "SELECT * FROM dictionary WHERE ARABIC LIKE '$encoded_search'";
    echo $sql."<br>";
    mysqli_query($conn,"SET NAMES 'utf8'");
    mysqli_query($conn,'SET CHARACTER SET utf8');
    $result = mysqli_query($conn, $sql);
    if (mysqli_num_rows($result) > 0) {
    // output data of each row
        while($row = mysqli_fetch_assoc($result)) {
            header('Content-Type: text/html; charset=utf-8');
            echo $row["ARABIC"]. " - Meaning: " . $row["ENGLISH1"]. " " . $row["ENGLISH2"]. "<br>";
        }
    }else {
        echo "0 results";
    }
}

?>