使用PHP在简单的MySQL查询中查询不返回包含特殊字符的记录

我正在寻找以编程方式查找和替换特殊字符的方法,这些特殊字符会阻止查询返回结果。

背景是我正在使用MYSQL和PHP通过API插入和检索文本。文本由多种来源组成,包括诗歌,通常具有诗人使用的特殊字符,或者仅仅是展示的产物。使用MYSQL插入文本可以完美工作。

但是,当需要检索记录时,如果文本不时包含特殊字符,则查询根本不会返回某些记录。

In one case, use of a curly quote as opposed to a straight apostrophe ', caused the query to return no records. I solved this by using str_replace to replace curly quotes with straight apostrophes. However, in other instances--especially longer poems, I can't identify what is causing the problem and need a broader way to solve the problem.

例如,查询下面的引号不会返回记录,我仍然不知道为什么:

表:

id|quotation|source|author
1|To be or not to be that is the question|hamlet|shakespeare

保存如下:

$strVal = $mysqli->real_escape_string($strVal);
$strVal = str_replace("\n\r",",",$strVal);
$strVal = str_replace("’","'",$strVal);
$strVal = str_replace("‘","'",$strVal);

检索如下:

while($row = mysqli_fetch_assoc($res)) {
    $return[] = $row;
}

echo json_encode(array('myresults'=>$return));

但是当我尝试检索它时,我得到空值。

如何确保一旦检索到数据库中输入的文本即可?

       *         *         *         *

No! I am not Prince Hamlet, nor was meant to be; Am an attendant lord, one that will do To swell a progress, start a scene or two, Advise the prince; no doubt, an easy tool, Deferential, glad to be of use, Politic, cautious, and meticulous; Full of high sentence, but a bit obtuse; At times, indeed, almost ridiculous— Almost, at times, the Fool.

I grow old ... I grow old ... I shall wear the bottoms of my trousers rolled.

Shall I part my hair behind? Do I dare to eat a peach? I shall wear white flannel trousers, and walk upon the beach. I have heard the mermaids singing, each to each.

I do not think that they will sing to me.

I have seen them riding seaward on the waves Combing the white hair of the waves blown back When the wind blows the water white and black. We have lingered in the chambers of the sea By sea-girls wreathed with seaweed red and brown Till human voices wake us, and we drown.

谁能推荐一种以编程方式设置文本格式以便始终可以检索的方法?

感谢您的任何建议。

评论