PDO Binder Function

Since the number of variables to be bound could potentially become quite large, a function can be used to build the bindValue() methods for the prepared statements.

 

First an associative array is created with the variables being associated as the values as the keys:

 

$myArr = array('team'=>$team, 'status'=>$status);

 

The array is then used within a foreach loop to build the prepared statements:

 

foreach($myArr as $key=>$value){

$statement->bindValue(':'.$key,$value);

}

 

Putting this altogether we now have a function that will build the binding prepared statements:

<?php
	ini_set('display_errors', '0');
	include_once('connect.php');
	include_once('error_handling.php');

//	include_once('pdoBinder.php');

	$team = 'Ducati';
	$status = 'Factory';

	//Named placeholder, note colon before variable & no quotation marks
	$sqlQuery = "SELECT * from riders where team = :team AND status = :status";

	//now use the prepare method & assign to $statement to create a prepared statement
	$statement = $dbh->prepare($sqlQuery);

	//create an associative array
	$myArr = array('team'=>$team, 'status'=>$status);

	//use above array to build the bind prepared statements
	foreach($myArr as $key=>$value){
		$statement->bindValue(':'.$key,$value);
	}
	try{
		$statement->execute();
	}
	catch(PDOException $error){
		echo errorHandling($error);
	}
	while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
		echo $row['name'] . " rides a " . $row['team'] . " " . $row['status'] . " MotoGP bike!<br>";
	}
?>

Save & refresh browser:

Connected to the motogp database
Andrea Dovizioso rides a Ducati Factory MotoGP bike!
Nicky Hayden rides a Ducati Factory MotoGP bike!

 

 

We can even take this one step further and place the binding function into an external file:

<?php
	function arrayBinder(&$pdoStatement, &$array) {

		foreach($array as $key=>$value){
			$pdoStatement->bindValue(':'.$key,$value);
		}
	}
?>

*note: use of & denoting that references are being passed in and therefore the original values will be modified

 

The above pdoBinder.php file can now be included in the main file, as can be seen on line 5, and it's arrayBinder() function called on line 23 passing in the prepared statement and the array:

<?php
	ini_set('display_errors', '0');
	include_once('connect.php');
	include_once('error_handling.php');
	include_once('pdoBinder.php');

	$team = 'Ducati';
	$status = 'Factory';

	//Named placeholder, note colon before variable & no quotation marks
	$sqlQuery = "SELECT * from riders where team = :team AND status = :status";

	//now use the prepare method & assign to $statement to create a prepared statement
	$statement = $dbh->prepare($sqlQuery);

	//create an associative array
	$myArr = array('team'=>$team, 'status'=>$status);

	//calling the arrayBinder function from the included pdoBinder.php file
	arrayBinder($statement, $myArr);

	try{
		$statement->execute();
	}
	catch(PDOException $error){
		echo errorHandling($error);
	}
	while($row = $statement->fetch(PDO::FETCH_ASSOC)) {
		echo $row['name'] . " rides a " . $row['team'] . " " . $row['status'] . " MotoGP bike!<br>";
	}
?>

Save & refresh browser:

Connected to the motogp database
Andrea Dovizioso rides a Ducati Factory MotoGP bike!
Nicky Hayden rides a Ducati Factory MotoGP bike!

Leave a Reply