mysql – Use wildcards in prepared SQL LIKE – PHP – or is my result broken?-ThrowExceptions

Exception or error:

Caveat –

  1. googled this many times
  2. have looked at several answers in stack overflow (am aware may not have looked hard enough)
  3. have looked in w3schools at prepared statements
  4. have checked out several online tutorials on prepared statements in
    PHP
  5. have worked with a local developer who knows SQL well but notPHP as much to debug
  6. have worked with a local software engineer with some SQL knowledge to debug but to no avail

So here goes – I have a simple form submission to search against a column in a database – using LIKE with wildcards. I have checked the SQL in PHP (pre using prepared statements) and in MySQL itself and the code is sound – SELECT * FROM database.table WHERE Col LIKE ‘%$variable%’ <– works.

I am now using quite rightly prepared statements for use in the real world (I’ve not coded PHP in 5 years so it was a learning curve).

All of my prepared statements work, SELECT, INSERT etc except this search…

$searchParam = mysqli_real_escape_string($conn, htmlspecialchars($_POST['Search'])); 
$searchStmt = $conn->prepare("SELECT ID, Title FROM insertdatabasenamehere.insertTableNameHere WHERE Title LIKE ?");
$searchItem = '%'.$searchParam.'%';
$searchStmt->bind_param("s", $searchItem);
$searchStmt->execute();
$searchQueryResult = $searchStmt->get_result();

Hopefully good so far, barring any niggling OMG why did you do it like that… this should work base on previous prepared SQL statements from before.
Then it gets weird.

if ($searchStmt->num_rows > 0) {
    //some html here
    while($row = $searchQueryResult->fetch_assoc()) { 
        // loop through the result set echoing out $title and $ID
    }
} else {
    echo "No result found";
}

Each time – no result found So it doesn’t error on the LIKE or anything, just no matches are made and there are definitely items in the DB that will match.

Any where I am going wrong??

thank you!

How to solve:

Leave a Reply

Your email address will not be published. Required fields are marked *