Implement LIKE in PHP prepared Statements with % wildcards

Question

I'm working on setting up a basic search query for my database and returning it to a table. I'm running into an issue with the syntax to use within prepared statements.

I based my initial setup on a w3schools example.

The search input comes from a form on an html page. This calls the php file.

Within the php file, I connect to the SQL server and execute the following:

$search = $_POST['search'];

$stmt = $link->prepare("SELECT lname, fname FROM planner WHERE lname LIKE ?");
$stmt->bind_param('s', $search);
$stmt->execute();
$result = $stmt->get_result();

print_r($result);

if ($result->num_rows > 0) {
echo "<table><tr><th>Last Name</th><th>First Name</th></tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr><td>".$row["lname"]."</td><td>".$row["fname"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}

The challenge I'm running into is how to define the parameters for the LIKE to evaluate within the prepared statement construct. W3Schools give lots of examples of LIKE parameters using various combinations of letters and %, but I'm not sure where to put these in the prepared statement. I want to base the lname results on the search criteria provided from the form on the html page.

Several old posts on this site point to setting the criteria within a string. I'm not sure exactly how it works. If that's a good way to go about it, I'd like to know more about what's going on with this method.

Appreciate any help you all can provide.

It's not clear what you want to do here. What do you mean by "define the parameters for the LIKE to evaluate within the prepared statement construct". It looks like you are doing that already. The prepared statement gets sent to mysql and the variable is replaced by your search parameter. Are you getting any errors? — Cave Johnson, Aug 04 '17 at 00:18
include the `%` with the variable value so if you have `$name` than you have to add the wild cards like this `$name.'%'` begins with. Do not add it to the SQL like this `LIKE ?%` it's a common issue. or in your example `bind_param('s', $search.'%');` — ArtisticPhoenix, Aug 04 '17 at 00:19
The basic question you're asking is what are the operators for a LIKE clause. The answer to that is that `%` is a wildcard search that searches 0 or more characters. _ matches exactly a single character. So `te%` would match `test`, `tea`, and `teal` while `te_` would match only `tea`. https://www.w3schools.com/sql/sql_like.asp — PressingOnAlways, Aug 04 '17 at 00:21
Kodos, I don't get any errors, just zero results everytime, even if I enter a lname exactly how it is in the database — airider74, Aug 04 '17 at 00:22
What is your search string and what do you think it should match. — ArtisticPhoenix, Aug 04 '17 at 00:23
ArtisticPhoenix, thanks I'll give that a try. I was playing around with options on the ? as well as $_POST['search'}....Didn't think to add it to the bind. — airider74, Aug 04 '17 at 00:24
PressingOnAlways, thanks, that's what I read on w3school as well. — airider74, Aug 04 '17 at 00:25
also do you have `PHPmyadmin` for database work? IF so take the query itself and run it right in there, that way if you get results in `PHPmyadmin` but not the page you know something else is wrong in your code. You can do `echo str_replace('?', "'".$search"'", $sql)` to output the query assing its stored in `$sql` etc. Don't use that to build a query as it defeats the purpose of preparing them. — ArtisticPhoenix, Aug 04 '17 at 00:27
ArtisticPhoenix, I now get an error...Fatal error: Uncaught Error: Cannot pass parameter 2 by reference in D:\xampp\htdocs\php\mentor.php:91 Stack trace: #0 {main} thrown in D:\xampp\htdocs\php\mentor.php on line 91 — airider74, Aug 04 '17 at 00:28
Ok, It's dinner time for me( my wife will get mad if I ignore her) sorry, and good luck. Plus she made her famous stuffed bell peepers :-) — ArtisticPhoenix, Aug 04 '17 at 00:31
My search string I use is "john", which should match lastnames (lname) like johnson which I have in my database. — airider74, Aug 04 '17 at 00:32
If that is one of `Several old posts on this site` you already saw please update the question to show your usage of the wildcard `%`. — chris85, Aug 04 '17 at 00:42
chris85, I've played around with that one a bit, but it isn't addressing the wildcard syntax I'm asking about here. I updated the question. — airider74, Aug 04 '17 at 00:56

score 8 · Accepted Answer · answered Aug 04 '17 at 01:19

I want to thank everyone for their help with this. ArtisticPhoenix got me headed in the right direction.

This post hit the mark of what I was looking for to bring it all together:

Adding a wildcard character to a string in PHP

Here's the "slightly" updated code:

    $search = $_POST['search'].'%';

    //echo($search);

    $stmt = $link->prepare("SELECT lname, fname FROM planner WHERE lname LIKE ?");
    $stmt->bind_param('s', $search);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
    echo "<table><tr><th>Last Name</th><th>First Name</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["lname"]."</td><td>".$row["fname"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

It will search the last index matches, better will be-> $search = '%'.$_POST['search'].'%'; Which will search full string. Thanks airider.. I'm having this problem recently.. — Maniruzzaman Akash, Aug 04 '17 at 01:21

score 2 · Answer 2 · answered Aug 04 '17 at 01:20

2

You can do like's keyword work in sql like this simply

-> To search only for lname

$search = $_POST['search'];
$stmt = $link->prepare("SELECT * FROM planner WHERE lname like ?");
$stmt->execute(array("%".$search."%"));

-> To search both for lname and fname just do this->

 $search = $_POST['search'];
 $stmt = $link->prepare("SELECT * FROM planner WHERE lname like ? OR fname like ? ");
 $stmt->execute(array("%".$search."%", "%".$search."%"));

answered Aug 04 '17 at 01:20

Maniruzzaman Akash

4,610
1
37
34

1

This is close, but the `execute` binding is exclusive to PDO. OP is using `mysqli`. – chris85 Aug 04 '17 at 01:28

Implement LIKE in PHP prepared Statements with % wildcards

2 Answers2