2

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!

Improve this question
3
  • what is the exact error you're getting? Commented Dec 17, 2013 at 16:06
  • 1
    You need to include the code making the db calls... PDO? Mysqli? Commented Dec 17, 2013 at 16:09
  • Is SQL escaping too much to ask for? This is reckless programming. Commented Dec 17, 2013 at 16:10

5 Answers 5

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

4 Comments

I expected the outburst of complaints about faulty programming, as I don't prevent any SQL injection. however, this application I'm building will be hosted localy. anyway, from what I understood, if I want only to type the first letters and the have the application assume the rest, won't I need only the character "%" AFTER the variable?
Yes, security is a tradeoff, but that particular error is a) so common, and b) so easily preventable, that it's never a good idea to ignore it. Software has a life beyond its original intent, and today's local tool is tomorrow's customer-facing management application. Regardless, though, you are correct: if you only care about matching the first part of a string, then LIKE 'foo%' is correct, with the case-sensitivity caveat I noted. Do you have some trailing characters (perhaps a newline) on the end of your $surname variable? Dump the variable in a log and see.
I dumped it, it returns normally the value.
@PanosVakalopoulos This is never a good argument. Even on your local network it is never guaranteed that there will not be anyone with bad intentions. Not escaping data is simply stupid...
1

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

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

4 Comments

Would that actually make any difference whatsoever?
This does nothing. Please, PHP people, stop recommending this nonsense. Read about how strings work and how " can interpolate values.
Strings that are enclosed in double quotes have the contents parsed for PHP variables automatically. Strings surrounded by single quotes do not, and therefore require concatenation. As such, your suggestion will do nothing.
This is cargo cult programming, it's the programming equivalent of waving a chicken over your code to make it work.
0

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

Improve this answer

4 Comments

That shouldn't make a difference, that just allows there to be random letter in front of the surname as well as behind it, instead of just behind it.
Yes, currently it is only wildcard matching the end of the name, and so the beginning must match exactly. There was no comment as to the partial surnames that had been tested, so this could be the case.
That is why he should do a var_dump rather than conform his program to be more liberal with the responses when he does not want them to be. My guess is that the names he will be trying to grab will be mostly exact to the ones in the database.
I'm sure that the variable contains the surname, because if I type the whole surname, it works as it should. but if I type only the first three letters, it returns the message I wrote for wrong surnames typed
0

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!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.