Mr. Developer Mr. Developer - 5 months ago 9
SQL Question

How can create parameterized query with AND and Like operator using PDO?

I'm trying to create query which can fetch data by title and category id. Below simple query is working for me in phpMyAdmin.

`SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id`
WHERE prmCat.`cat_id` = 3 AND prm.`pm_title` LIKE '%Essa%' `


I'm using PDO, so I have to convert it according to PDO procedure. I'm trying below two queries but these are returning error.

PDO query 1



$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id`
WHERE prmCat.`cat_id` = :cat_id AND prm.`pm_title` LIKE ? ");
$PromotionSts->bindParam(':cat_id', $cat_id);
$PromotionSts->bindParam(':pm_title', '%$pm_title%');
$cat_id = $cat_id;
$pm_title = $pm_title;
$PromotionSts->execute();


PDO query 2



$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id`
WHERE prmCat.`cat_id` = :cat_id AND prm.`pm_title` LIKE ? ");
$PromotionSts->execute(array(3,'%Essa%'));


I will appreciate if someone guide me regarding this. Thank You

Answer

If you've read prepare manual carefully, you will notice that

The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style.

So, proper queries are:

$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id` 
                WHERE prmCat.`cat_id` = :cat_id AND prm.`pm_title` LIKE :pm_title");

// Both query parameteres are named parameters
$PromotionSts->bindParam(':cat_id', $cat_id);
// variables in single quotes are NOT parsed
$PromotionSts->bindParam(':pm_title', '%' . $pm_title . '%');
// this lines are useless
//$cat_id = $cat_id;  
//$pm_title = $pm_title;
$PromotionSts->execute();

Or:

$PromotionSts=$this->pdoConnection->prepare("SELECT * FROM `ws_promotion` AS prm LEFT JOIN `ws_pro_cat` AS prmCat ON prm.`pm_id` = prmCat.`pm_id` 
                WHERE prmCat.`cat_id` = ? AND prm.`pm_title` LIKE ? ");
// Both query parameteres are ? marks
$PromotionSts->execute(array(3,'%Essa%'));
Comments