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?
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:
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!