MySQL LIKE statement problem

[CanadianGSX]

Here's the code I have

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname = '$lname' or fname = '$fname'";

Works like a charm.

Now, I want to be able to use wildcards so that the user can enter, for exemple, HO to search for HOUSE so I tried the following:

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE '$lname%' or fname LIKE '$fname%'";

It returned every record in the table so I tried :

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE '$lname' + '%' or fname LIKE '$fname' + '%'";

This just crashed the script.

Any input?

[smantscheff]

You cannot use the '+' operator for character strings in MySQL. Use the concat function instead.

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE concat('$lname','%') or fname LIKE concat('$fname','%')";

Or change your PHP code to

$query = "SELECT partID, fname, lname FROM tblPartInfo WHERE lname LIKE '$lname" . "' or fname LIKE '$fname" . "%'";

[CanadianGSX]

SOrry for the late reply, I just had a chance to get back to this project. Changing my PHP code worked for the FNAME variable. For the LNAME variable, it still dumps out the whole list and not just the LIKE list.

Using the first syntax also just dumps out the whole list...this is starting to throw me for a loop!