PHP变量和MySQL LIKE查询无法正常工作

问题描述：

I have the following code:

$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

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.

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

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

What am I missing?

post-comments-editing -

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!

我有以下代码： p>

  $ 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“＆lt; table＆gt;”; 
while（$ data = mysql_fetch_array（$ result2））{
 echo“＆lt; tr＆gt;  “; 
 echo”＆lt; td style ='width：100px; height：40px'＆gt;“。$ data ['name']。”＆lt; / td＆gt;“; 
 echo”＆lt; td style ='  width：100px; height：40px'＆gt;“。$ data ['surname']。”＆lt; / td＆gt;“; 
 echo”＆lt; td style ='width：100px; height：40px'＆gt;“。  $ data ['checkIN']。“＆lt; / td＆gt;”; 
 echo“＆lt; / tr＆gt;”; 
} 
echo“＆lt; / table＆gt;＆lt; br＆gt;＆lt; br＆gt;”; 
   code>  pre> 
 
 让我们说下表中的以下记录： p> 
 
 
   - 姓氏 -  
Greyjoy 
Lannister 
Stark 
   code>  pre> 
 
 如果我不输入完整的姓氏，会发生错误，指出该姓氏不存在。 结果LIKE“％”无效。 p> 
 
 
我试过LIKE'“。$ surname。”$'或LIKE'{$ surname}％'，但也没有任何反应 。 p> 
 
 
我在Stack中搜索了很多，似乎上面的试用版应该正常工作。   p> 
 
 
我缺少什么？ p> 
 
 
评论后编辑 -   li> 
  ul> 
  
 为了更加明白，我确信变量包含实际的姓氏作为字符串，因为如果我输入整个姓氏，我的应用程序正常工作。 但是，如果我输入前3个字母（或4 ...），应用程序将返回我自己的姓氏输入错误的消息。 p> 
 
 
此外，还要以区分大小写的方式检查问题 ，我的测试是用一个只有小字符的姓氏来完成的。 p> 
 
 
感谢大家的努力，仍然对这个问题感到满意！ p> 
  div>

答

You have two definite problems and one potential problem:

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 '%?%'";

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.

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 ("%?%").

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.

答

Put the wildcard at the beginning as well as the end: $sql2="SELECT * FROM andriana WHERE surname LIKE '%$surname%'";.

答

I guess it would work either way, but try this:

"SELECT * FROM andriana WHERE surname LIKE '" . $surname . "%'";

答

Make sure surname has a value and that you are passing one to it. I recommend doing a var dump

$surname=$_POST['surname'];
var_dump($surname);

That will show you the values of what $surname is equal to, if it is nothing, then that is why your query is not working.

答

I'm a complete idiot. Guyz you were perfect, actually the query with "LIKE '$surname%'" works fine.

My problem is that before that, I was having a check control and I didn't check for LIKE but for the variable itself.

Please accept my dumpness, and thank you again for your time!

PHP变量和MySQL LIKE查询无法正常工作

相关推荐