PHP 变量和 MySQL LIKE 查询不起作用 [英] PHP variable and MySQL LIKE query is not working

问题描述

我有以下代码:

$surname=$_POST['surname'];
$sql2="SELECT * FROM andriana WHERE surname LIKE '$surname%'";
if (!mysql_query($sql2,$con)){
die('Error: ' . mysql_error());
}
$result2 = mysql_query($sql2);

echo "<table>";
while ($data = mysql_fetch_array($result2)) {
    echo "<tr>";
    echo "<td style='width:100px;height:40px'>".$data['name']."</td>";
    echo "<td style='width:100px;height:40px'>".$data['surname']."</td>";
    echo "<td style='width:100px;height:40px'>".$data['checkIN']."</td>";
    echo "</tr>";
}
echo "</table><br><br>";

假设我的表中有以下记录:

and let's say the following records in my table:

- Surname -
Greyjoy
Lannister
Stark

发生的情况是，如果我不输入完整的姓氏，则会抛出该姓氏不存在的错误.结果 LIKE "%" 不起作用.

What happens is that if I won't type the full surname, it throws error that that surname doesn't exist. As a result the LIKE "%" is not working.

我尝试过 LIKE '".$surname."$' 或 LIKE '{$surname}%'，但也没有任何反应.

I have tried LIKE '".$surname."$' or LIKE '{$surname}%', but nothing happens too.

我在 Stack 中搜索了很多，上面的试用似乎应该有效.

I searched here in Stack a lot, and it seems that the above tryouts should be working.

我错过了什么?

• 评论后编辑 -

为了更容易理解，我确信该变量包含作为字符串的实际姓氏，因为如果我输入整个姓氏，我的应用程序可以正常工作.但是，如果我输入前 3 个字母(或 4 个...)，应用程序会返回我自制的消息，指出输入的姓氏是错误的.

To be more understood, I am sure that the variable contains the actual surname as a string, because if I type the whole surname, my application works normally. However, if I type the first 3 letters (or 4...) the application returns my homemade message that the surname typed is wrong.

此外，为了解决区分大小写的问题，我的测试是使用只有小字符的姓氏完成的.

Also, to go over the problem with case sensitive, my testing is done with a surname which has only small characters.

感谢大家的努力，问题仍然存在！

Thank you all for your effort, still havinf the issue!

推荐答案

您有两个确定的问题和一个潜在的问题:

You have two definite problems and one potential problem:

首先，您没有使用绑定变量.这会使您的脚本遭受 SQL 注入攻击，这是一种极为常见且可预防的安全错误.将您的 SQL 脚本替换为:

First, you aren't using bind variables. This opens up your script to an SQL injection attack, which is an extremely common and preventable security error. Replace your SQL script with:

$sql2 = "SELECT * FROM andriana WHERE surname LIKE '%?%'";

然后prepare()你的语句，绑定你想要的变量，然后execute()它.参见 http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php 进行更多讨论.

Then prepare() your statement, binding the variable you want, and execute() it. See http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php for more discussion.

其次，% 通配符代表任何字符"，但它是位置性的，这意味着您应该将它包含在 LIKE 参数的开头，如上(%?%").

Second, the % wildcard stands for "any characters", but it is positional, which means you should include it at the beginning of your LIKE argument, as above ("%?%").

最后，一个潜在的问题:LIKE 并不总是不区分大小写的.我认为 mySQL 执行不区分大小写的 LIKE，但您应该设置其中的配置.如有疑问，请使用 ILIKE 或通过将比较的两侧小写来手动强制进行不区分大小写的比较.

Finally, a potential issue: LIKE is not always case insensitive. I think mySQL does case-insensitive LIKEs, but there may be a configuration there that you should set. When in doubt, either use an ILIKE or manually force a case-insensitive comparison by lowercasing both sides of your comparison.

这篇关于PHP 变量和 MySQL LIKE 查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！