We help IT Professionals succeed at work.

How do I use a LIKE clause in a prepared statement in MSQLi

I'm playing around with some prepared statement code that I've written.

<?php
require 'includes/dbconnect.inc.php';
?>
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
  <meta charset="utf-8">
  <title>Test</title>
</head>
<body>
  <?php
  $query="q";

  $stmt = $dbConnection->prepare("SELECT * FROM eurofullsearch WHERE firstLetter = ? AND city_name_ascii LIKE '?%' ORDER BY CHAR_LENGTH(minnow) LIMIT 10");
  $stmt->bind_param("ss",$query , $query);
  $stmt->execute();
  $result=$stmt->get_result();
  if($result->num_rows === 0) exit('No rows');

  while($row=$result->fetch_assoc()){
    echo $row["city_name_ascii"];
    echo $row["state_name"];
    echo $row["country_name"];

  }

  $stmt->close();
  $dbConnection->close();
  ?>

</body>
</html>

Open in new window


I've never used a prepared statement with a LIKE clause in it;

 $stmt = $dbConnection->prepare("SELECT * FROM eurofullsearch WHERE firstLetter = ? AND city_name_ascii LIKE '?%' ORDER BY CHAR_LENGTH(minnow) LIMIT 10");

Open in new window


This code is failing as I believe it's to do with the syntax around the ?% in this prepared statement.

The error I receive is;

Php error
which leads me to believe my syntax '?%' is wrong.

Please help

Thank you
James
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
try and see if this worked for you?

$stmt = $dbConnection->prepare("SELECT * FROM eurofullsearch WHERE firstLetter = ? AND city_name_ascii LIKE ? ORDER BY CHAR_LENGTH(minnow) LIMIT 10");
  $stmt->bind_param("ss",$query , $query."%");

Open in new window

Author

Commented:
Hi Ryan,

I'm getting the error:

Fatal error: Uncaught Error: Cannot pass parameter 3 by reference in C:\xampp\htdocs\2020-01-15 Auto Suggest\index4SQLTest.php:16 Stack trace: #0 {main} thrown in C:\xampp\htdocs\2020-01-15 Auto Suggest\index4SQLTest.php on line 16

with that.

thanks James

Author

Commented:
I think I just came up with the solution

  
$stmt = $dbConnection->prepare("SELECT * FROM eurofullsearch WHERE firstLetter = ? AND city_name_ascii LIKE CONCAT(?,'%') ORDER BY CHAR_LENGTH(minnow) LIMIT 10");
    
$stmt->bind_param("ss",$query , $query);

Open in new window


Does that look right to you professionals out there?

It does seem to be giving the expected result with no errors.
Software Team Lead
Commented:
I saw the syntax of using CONCAT(?,'%') somewhere online and seems to be a working solution

Author

Commented:
Thanks for helping Ryan and stimulating my brain-cells to work this out.

All seems good with the code right now so I'll go with that solution. The solution doesn't seem to prevent any security risks (I'm learning ethical-hacking at a later date) so am happy for now.

Have a nice day,
James