How do I bind param in this query?

[bbmak]

Hi,

New to PDO, stucked on binding params in the dynamic query. 

		$searchTerms = explode(' ', $search_string);

	
		foreach ($searchTerms as $searchTerm){
			$condition .= "ci.item_name LIKE ':searchTerm' OR ";
		}
		$condition = substr($condition, 0, -4);
	
		$searchQuery = "(SELECT ci.id, ci.item_name, ci.item_description FROM core_item ci WHERE " . $condition . ")";




		if($searchStmt = $this->pdo->prepare($searchQuery))
			{
				foreach ($searchTerms as $key => $value){
					$seachTerms->bindParam($key, '%' . $value . '%');
				}

				$searchStmt->execute();
				return $searchStmt->fetchAll();
			}
			
		}
	

[mac_gyver]

in most cases, you don't need to explicitly bind parameters or values, just supply an array to the execute() method call.

 

here are some points for your current code -

 

1) the place-holder names must be unique. when you are dynamically building a prepared query statement, you should use ? place-holders.

 

2) the place-holders do not get single-quotes around them in the sql query statement.

 

3) when building similar terms, it is cleaner to build them in an array, then implode() the array with the separator keyword between the elements. this will work correct even if there is a single element.

 

4) the sql query statement doesn't need ( ) around it and having them just clutters up the code.

 

5) you should be using exceptions to handle statement errors. this will mean that your main code only has to deal with error free execution and you don't need to have conditional logic for each statement that can fail.

 

6) if you use php's array functions, it will eliminate the need to explicitly loop in the code. see the following example - 

$searchTerms = explode(' ', $search_string);
// note: you should filter empty values out of the $searchTerms array and only execute the remainder of the code if there are any remaining values in $searchTerms

// build the dynamic part of the sql query statement
$terms = implode(' OR ', array_fill(0, count($searchTerms), "ci.item_name LIKE ?"));

// produce the complete sql query statement
$searchQuery = "SELECT ci.id, ci.item_name, ci.item_description FROM core_item ci WHERE $terms";

// prepare the query
$searchStmt = $this->pdo->prepare($searchQuery);

// function to add % wildcard characters to the value
function _wildcard($val)
{
    return "%$val%";
}

// add the wildcard characters to the values
$searchTerms = array_map('_wildcard',$searchTerms);

// execute the query
$searchStmt->execute($searchTerms);

// fetch and return the result - this will be an empty array if the query didn't match any row(s)
return $searchStmt->fetchAll();

Edited March 27, 2017 by mac_gyver

[Jacques1]

You also need to escape the user input before you can safely use it in a LIKE term. The input may already contain special search characters like “%” or “_” which have to be turned into literal characters.

[bbmak]

thank you guys.