Trying to SELECT ... LIKE with prepared statement

php
sqlite

#1

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.


#2

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
        )

)

#3

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

Update: Yey! Works perfick.


#4

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


#5

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();

#6

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

doh


unlisted #8

listed #9