PHP在MySQL上返回0结果，而在SQLBuddy/phpMyAdmin上返回0结果

Question

问题

在MySQL数据库中使用SELECT查询搜索阿拉伯单词时，将得到0的结果。

但是，相同的查询结果会产生替代客户端，即SQLBuddy等。所有东西都是用UTF-8编码的。

码

<?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";
        }
    }

?>

在mods得到干草叉之前，我必须清理我的故障排除逻辑。

1. 编码。I使用header('Content-Type: text/html; charset=utf-8');将页面编码设置为utf-8，并运行查询mysqli_query($conn,"SET NAMES 'utf8'");和mysqli_query($conn,'SET CHARACTER SET utf8');，从而清除了???????和Ùؤتا呈现的问题，而不是阿拉伯单词问题。这是一个不同的问题。Source.和Source2.
2. 数据库字符集.我的数据库和列被设置为UTF-8.
3. 其他客户端工作。 SQLBuddy/MySQL本机客户机/ PHPMyAdmin似乎工作，因为运行相同的查询结果。因此，我似乎与him.处于同一条船上，查询SELECT * FROM dictionary WHERE ARABIC LIKE 'آخَر، أُخرى'在SQLbuddy上返回一个结果，而在PHP上返回nada。

可能的解决方案：

运行查询SELECT * FROM dictionary WHERE ARABIC LIKE 'آخَر، أُخرى'将得到一个结果。

但是，使用UTF-8编码版本的阿拉伯单词运行查询将返回0结果。SELECT * FROM dictionary WHERE ARABIC LIKE 'آخَر، أُخرى' --我想这是模拟PHP的。

UTF-8阿拉伯词版本是通过解码自动URL编码的$_GET参数(即%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 )获得的。

难道MySQLi实际上是在查询UTF-8 version而不是实际的阿拉伯词吗？因此，找不到匹配，因为他们是不同的？

如果是这样的话，我如何显式地告诉PHP不要对我的搜索词进行编码，从而传递它呢？

因为根据我的锡箔理论，http://localhost/hans_wehr/search_ar.php?search=آخَر، أُخرى可以工作但是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

投入将受到极大的赞赏。

Answer 1

使用html_entity_decode()：

在您的html_entity_decode“搜索”值上使用$_GET()

<?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";
    }
}

?>