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);
$stmt->execute();
$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
		SELECT 
			`dev_name` 
		FROM 
			`tblSensors`
		WHERE 
			`dev_name` 
        LIKE 
           :find;
____TMP;

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

Output:

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

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

)
2 Likes

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

Update: Yey! Works perfick.

1 Like

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 %.

1 Like

This topic is covered in the tutorial I often post.

https://phpdelusions.net/pdo#like

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

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

doh

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.