searching for character % in mysql table with PDO

Question

I have a table with units :

insert into table unit ('id', 'name') values ('1', 'mg'), 
('2', 'km'), ('3', '%'), ('4', '%.'), ('5', 'mg/100%'), ('6', 'km/h'); 

now I want to find all units that have a % in name (here : %, %. and mg/100%) :

select * from unit where name like  '%\%%'; 

works fine from console or phpmyadmin, but when I try to do it with PDO

$st = $db->prepare('select * from unit where name like :n'); 
$st->execute(array('n'=>'%mg%')); 

is OK, but

$st->execute(array('n'=>'%\%%')); 

give me no results...

how to escape % character in PDO prepare/execute ?

Answer 1

Just add an ESCAPE clause to your query:

WHERE name LIKE :n ESCAPE '\';

In PHP, you'll have to escape the backslashes, of course:

$stmt = $pdo->prepare('... WHERE name LIKE :n ESCAPE \'\\\';');