NoSkilz NoSkilz - 27 days ago 5
SQL Question

Adding % wildcards to my SQL query causes a syntax error

Quick question here.
I have this script:

$sql4='SELECT prod_name,description,price,in_stock,plat_name,genre_name FROM product WHERE prod_name LIKE %:prodname%';
$query5=$db->prepare($sql4);
$query5->execute(array(':prodname'=> $_GET['product']));
$result2=$query5->fetch(PDO::FETCH_ASSOC);


And its giving me this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%'some text'%'

I know that those percent signs are problem, if i remove them the code works just fine but how do i get it working with those signs? I need them because i want to select all records containing given string.
Thanks for answers and sorry for my bad english :).

Answer

You should try to change for it:

$sql4='SELECT prod_name,description,price,in_stock,plat_name,genre_name FROM product WHERE prod_name LIKE :prodname';
$query5=$db->prepare($sql4);
$query5->execute(array(':prodname'=> '%'.$_GET['product'].'%'));
$result2=$query5->fetch(PDO::FETCH_ASSOC);

Where now, the php will correct format the query to ".. like '%yourValue%'"

Do you see the diference?

SELECT prod_name,description,price,in_stock,plat_name,genre_name FROM product WHERE prod_name LIKE '%'some text'%' (Wrong)

SELECT prod_name,description,price,in_stock,plat_name,genre_name FROM product WHERE prod_name LIKE '%some text%' (Correct!)