Trying to SELECT ... LIKE with prepared statement



I’m not sure if this a Db or a PHP issue. I am trying to perform a SELECT … LIKE with a prepared statement using SQLite and am having trouble with the % signs.

The following query works if the search term is entered in full

$query = "SELECT cid, mname FROM matters WHERE mname LIKE :find;";
$stmt = $db->prepare($query);
$stmt->bindParam('find', $_POST['find'], PDO::PARAM_STR);
$result = $stmt->fetchAll();

but if I try %:find% I get an error:

SQLSTATE[HY000]: General error: 1 near “%”: syntax error

and I get a similar error if I quote the %s - and if I have '%:find%'.

SQLSTATE[HY000]: General error: 25 column index out of range.

My little brain is running out of ideas.


I tried this and it worked on my computer :slight_smile:

	$query 	= <<< ____TMP

	$stmt = $this->pdo->prepare($query);
	$find = '%watford%';
	$stmt->bindParam('find', $find);//, PDO::PARAM_STR);
	$result = $stmt->fetchAll();
  echo '<pre>', print_r($result, true) ,'</pre>';die;	


    [0] => Array
            [dev_name] => Old Watford Office
            [0] => Old Watford Office

    [1] => Array
            [dev_name] => New Watford Office
            [0] => New Watford Office



Ah-ha, I’d not tried adding the % signs to the search term. Thanks John. Fingers crossed…

Update: Yey! Works perfick.


Keep in mind that part of what prepared statements do is wrap your string parameters in quotes.

So the Database engine got a query that looked like

SELECT cid, mname FROM matters WHERE mname LIKE %"yoursearchterm"%;

and borked because it hit an unencapsulated %.


This topic is covered in the tutorial I often post.

$search = "%$search%";
$stmt  = $pdo->prepare("SELECT column_name FROM table WHERE name LIKE ?");
$data = $stmt->fetchAll();


Thanks. I do usually refer to PHP delusions but didn’t on this occasion, and just relied on the SQLite documentation.


unlisted #8

listed #9