implement LIKE query in PDO

Question

I am running problems in implementing LIKE in PDO

I have this query:

$query = "SELECT * FROM tbl WHERE address LIKE '%?%' OR address LIKE '%?%'";
$params = array($var1, $var2);
$stmt = $handle->prepare($query);
$stmt->execute($params);

I checked the $var1 and $var2 they contain both the words I want to search, my PDO is working fine since some of my queries SELECT INSERT they work, it's just that I am not familiar in LIKE here in PDO.

The result is none returned. Do my $query is syntactically correct?

Possible duplicate of [How do I create a PDO parameterized query with a LIKE statement?](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement) — feeela, Aug 14 '18 at 09:32

score 96 · Accepted Answer · answered Jun 20 '12 at 10:06

96

You have to include the % signs in the $params, not in the query:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

If you'd look at the generated query in your previous code, you'd see something like SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%', because the prepared statement is quoting your values inside of an already quoted string.

answered Jun 20 '12 at 10:06

Carlos Campderrós

22,354
11
51
57

+1 for the explanation.. But I don't think there is a way to look at the generated query.. is there anything like that? – Vignesh Jul 28 '15 at 12:22
Couldn't figure this out for the life of me and I had forgotten all about this. Awesome answer. Thanks! – b3tac0d3 Dec 18 '15 at 18:50
2

What happens if my parameter is "$name%" and the user enters: $name = '%bob'. Wouldn't that have the unintended side effect of an anchored search instead of a left anchored search? How would I prevent this? – danielson317 Jul 04 '18 at 20:29

score 8 · Answer 2 · answered Jul 03 '17 at 21:01

8

Simply use the following:

$query = "SELECT * FROM tbl WHERE address LIKE CONCAT('%', :var1, '%')
            OR address LIKE CONCAT('%', :var2, '%')";

$ar_val = array(':var1'=>$var1, ':var2'=>$var2);
if($sqlprep->execute($ar_val)) { ... }

answered Jul 03 '17 at 21:01

Grant

2,413
2
30
41

1

This is slower than adding the % directly to the binded variables. – CristiC Jan 21 '18 at 06:09

score 5 · Answer 3 · answered Jun 20 '12 at 10:04

5

No, you don't need to quote prepare placeholders. Also, include the % marks inside of your variables.

LIKE ?

And in the variable: %string%

answered Jun 20 '12 at 10:04

Madara's Ghost

172,118
50
264
308

Hmm, pardon but I'm not quite sure how to implement what you have just suggested. – Leandro Garcia Jun 20 '12 at 10:05
I'm on mobile but I'll try to explain the best I can. See my edit. – Madara's Ghost Jun 20 '12 at 10:07

score 4 · Answer 4 · answered Jun 20 '12 at 10:06

4

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

answered Jun 20 '12 at 10:06

Miqdad Ali

6,129
7
31
50

score 2 · Answer 5 · edited Jun 20 '12 at 14:25

2

You can see below example

$title = 'PHP%';
$author = 'Bobi%';
// query
$sql = "SELECT * FROM books WHERE title like ? AND author like ? ";
$q = $conn->prepare($sql);
$q->execute(array($title,$author));

Hope it will work.

edited Jun 20 '12 at 14:25

Thiem Nguyen

6,345
7
30
50

answered Jun 20 '12 at 10:12

Dinesh Goyal

127
1
7

implement LIKE query in PDO

5 Answers5

Linked

Related