pdo prepared statements with wildcards

Question

I want to execute the following mysql query:

SELECT * FROM `gc_users` WHERE `name` LIKE '%anyname%'

I tried this without success:

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE :name");
$stmt->bindParam(':name', "%" . $name . "%");
$stmt->execute();

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE '%:name%'");
$stmt->bindParam(':name', $name);
$stmt->execute();

So I ask you if it is possible to use the % wildcard with prepared statements.

/edit

Thank you. Its working with bindValue:

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE :name");
$stmt->bindValue(':name', '%' . $name . '%');
$stmt->execute();

As for your first attempt - change `bindParam` to `bindValue` and turn [error reporting for PDO](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15990858) — Your Common Sense, Apr 27 '13 at 18:48
[As an alternative to the given answer](https://stackoverflow.com/a/36593020/3536236) you can also use the [MySQL CONCAT function](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat) — Martin, Nov 25 '19 at 16:37

score 34 · Accepted Answer · answered Apr 27 '13 at 19:02

34

It can work with bind param too in following way:

$name = "%$name%";
$query = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` like :name");
$query->bindParam(':name', $name);
$query->execute();

answered Apr 27 '13 at 19:02

Sumoanand

8,561
1
46
45

Not sure why this has been down voted but please note this solution also works. :) – Sumoanand Apr 27 '13 at 19:37
4

I guess it has been downvoted because if your `$name` contains a wildcard itself, it is not escaped. – Alain Tiemblo Oct 15 '13 at 22:38
1

I waste my time with have no charset=utf8 in the DSN, I search with Thai character. – ittgung May 16 '16 at 10:12
Are the backticks required? – Ömer An Jul 05 '21 at 11:12

score 1 · Answer 2 · edited Oct 07 '15 at 14:26

1

This could be an alternative:

$className = '%' . $this->className . '%';
$query->bind_param('s', $className);

edited Oct 07 '15 at 14:26

jgritty

11,242
3
35
58

answered Jun 26 '14 at 03:08

jroi_web

1,882
21
23

pdo prepared statements with wildcards

2 Answers2

Linked

Related