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

Question

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!

Answer 1

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.