Hi, I’m trying to figure out how to use PDO to execute a SQL query. The problem is binding the parameters to the vairables.
Ok, here is the table structure in question ::
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | YES | UNI | NULL | |
| category | varchar(50) | YES | MUL | NULL | |
| brand | varchar(50) | NO | MUL | NULL | |
| weight | decimal(10,2) | YES | | NULL | |
| weight_unit | varchar(45) | YES | | NULL | |
| price | decimal(15,2) | YES | | NULL | |
| price_unit | varchar(50) | YES | | NULL | |
| flavors | varchar(45) | YES | | NULL | |
| providers | varchar(45) | YES | MUL | NULL | |
| review_text | text | YES | | NULL | |
| image_path | varchar(100) | YES | | NULL | |
+-------------+---------------+------+-----+---------+----------------+
I have to get specific fields from this table using, dynamic like condition. This dynamic number of like condition will depend on the name of the product (the name field in the table).
Here is the sql statement which i want to replicate using PDO.
select id,name,price_unit,providers,category,weight_unit from supplement_products where (name like '%Optimum%') and (name like '%100%') and (name like '%Gold%') and (name like '%Standard%') and (name like '%double%' or '%rich%' or '%chocolate%') and (brand like '%Optimum%') and (category like '%whey%' or '%protein%') and (name like '%2%');
This returns the following results::
+-------+--------------------------------------------------------------------------+------------+------------------+--------------+-------------+
| id | name | price_unit | providers | category | weight_unit |
+-------+--------------------------------------------------------------------------+------------+------------------+--------------+-------------+
| 7956 | Optimum Gold Standard 100% Whey, 2 Lbs., Double Rich Chocolate | $0.00 | Bodybuilding.com | Whey Protein | 2 Lbs. |
| 12737 | Optimum Nutrition 100% Whey Gold Standard, Double Rich Chocolate 2 Pound | $27.64 | Amazon.com | Whey Protein | 2 Pound |
+-------+--------------------------------------------------------------------------+------------+------------------+--------------+-------------+
Now, the dynamic like condition will be formed using each word of the product name. And also it will contain each words from the weight_unit, flavors, brand, and category.
Here is the code which i have written so far.
/*
* Retrieve the data from the database
*
*
*/
public function retrieveProducts() {
$connection = Yii::app()->db;
$products = new Products;
$countRows = $products->count();
//$excludeIds = array();
for ($eachRow = 1; $eachRow <= $countRows; $eachRow++) {
/*
* enumerate the array using the id
*
*
*/
$eachProducts = $products->findByPk($eachRow);
/*
* Construct the query
*
*
*
*/
$query = $this->constructQuery();
$query = $query . $this->constructQueryCondition($eachProducts->name, 'name');
if ($eachProducts->flavors != NULL) {
$query = $query . $this->constructQueryCondition($eachProducts->flavors, 'name');
}
if ($eachProducts->weight_unit != 0) {
$query = $query . $this->constructQueryCondition($eachProducts->weight_unit, 'name');
}
$query = $query . $this->constructQueryCondition($eachProducts->brand, 'brand');
$query = $query . $this->constructQueryCondition($eachProducts->category, 'category');
//end of construct query
var_dump($query);
/*
* Bind the parameters
* @var executeQuery is the connection handler
*
*
*/
$executeQuery = $connection->createCommand($query);
var_dump($executeQuery);
/*
* Bind the name fields
*
*/
$parts = explode(" ", $eachProducts->name);
foreach ($parts as $eachParts) {
echo $eachParts."<br />";
if (!stristr($eachParts, "Nutrition") && !stristr($eachParts, "Unknown") && !stristr($eachParts, "pounds") && !stristr($eachParts, "Lbs")) {
$executeQuery->bindParam(":" . $eachParts, "%" . $eachParts . "%", PDO::PARAM_STR);
}
}
/*
* Bind the flavors fields
*
*/
$flavorParts = explode(" ", $eachProducts->flavors);
foreach ($flavorParts as $eachParts) {
echo $eachParts."<br />";
if (!stristr($eachParts, "Nutrition") && !stristr($eachParts, "Unknown") && !stristr($eachParts, "pounds") && !stristr($eachParts, "Lbs")) {
$executeQuery->bindParam(":" . $eachParts, "%" . $eachParts . "%", PDO::PARAM_STR);
}
}
/*
* Bind the weight fields
*
*/
$weightParts = explode(" ", $eachProducts->flavors);
foreach ($weightParts as $eachParts) {
echo $eachParts ."<br />";
if (!stristr($eachParts, "Nutrition") && !stristr($eachParts, "Unknown") && !stristr($eachParts, "pounds") && !stristr($eachParts, "Lbs")) {
$executeQuery->bindParam(":" . $eachParts, "%" . $eachParts . "%", PDO::PARAM_STR);
}
}
/*
* Bind the brand fields
*
*/
$brandParts = explode(" ", $eachProducts->brand);
foreach ($brandParts as $eachParts) {
echo $eachParts ."<br />";
if (!stristr($eachParts, "Nutrition")) {
$executeQuery->bindParam(":" . $eachParts, "%" . $eachParts . "%", PDO::PARAM_STR);
}
}
/*
* Bind the category fields
*
*/
$categoryParts = explode(" ", $eachProducts->brand);
foreach ($categoryParts as $eachParts) {
if (!stristr($eachParts, "Nutrition")) {
$executeQuery->bindParam(":" . $eachParts, "%" . $eachParts . "%", PDO::PARAM_STR);
}
}
try {
$results = $executeQuery->query();
} catch (PDOException $e) {
echo $e->getMessage();
}
var_dump($executeQuery->errorInfo());
echo $results->errorInfo();
var_dump($results);
}
}
public function constructQuery() {
return "SELECT id, name, price_unit, providers, category FROM supplement_products where ";
}
public function constructQueryCondition($parseString, $fieldName) {
$queryCondition = "";
$parseString = trim($parseString);
/*
* Form the condition based on the name of the product
*
*/
if (stristr($fieldName, "name")) {
$parts = explode(" ", $parseString);
foreach ($parts as $eachParts) {
if (!stristr($eachParts, "Nutrition") && !stristr($eachParts, "Unknown") && !stristr($eachParts, "pounds") && !stristr($eachParts, "Lbs")) {
$queryCondition .= "(name like :" . $eachParts . ") AND ";
}
}
return $queryCondition;
/*
* Form the condition based on the brand of the product
*
*/
} else if (stristr($fieldName, 'brand')) {
$parts = explode(" ", $parseString);
foreach ($parts as $eachParts) {
if (!stristr($eachParts, "Nutrition")) {
$queryCondition .= "(brand like :" . $eachParts . ") AND";
}
}
return $queryCondition;
/*
* Form the condition category on the name of the product
*
*/
} else if (stristr($fieldName, 'category')) {
$parts = explode(" ", $parseString);
foreach ($parts as $eachParts) {
if (!stristr($eachParts, "Protein")) {
$queryCondition .= "(category like :" . $eachParts . ") AND";
}
}
$queryCondition = substr($queryCondition, 0, -3);
return $queryCondition;
}
}
The sql query is forming nicely, but i’m having troubles binding the parameters. execute() statement doesn’t work. Nothing gets printed.
I hope my question made some sense. Please provide any sort of help on how to do this. I tried doing it using ActiveRecord but i can’t find a way to form the CDbCrieria condition. So i had to use PDO. At least it shows the query alright, but its not binding the parameters properly.
Thanks, in advance,
Maxx