note 109256 modified in mysqli-stmt.bind-param by aharvey

From:	aharvey@php.net	Date:	Thu, 19 Jul 2012 00:59:59 +0000
Subject:	note 109256 modified in mysqli-stmt.bind-param by aharvey
References:	1	Groups:	php.notes
Request:	Send a blank email to php-notes+get-189293@lists.php.net to get a copy of this message

When dealing with a dynamic number of field values while preparing a statement I find this class useful.

<?php
class BindParam{
	private $values = array(), $types = '';
	
	public function add( $type, &$value ){
		$this->values[] = $value;
		$this->types .= $type;
	}
	
	public function get(){
		return array_merge(array($this->types), $this->values);
	}
}
?>

Usage is pretty simple. Create an instance and use the add method to populate. When you're ready to execute simply use the get method.

<?php
$bindParam = new BindParam();
$qArray = array();

$use_part_1 = 1;
$use_part_2 = 1;
$use_part_3 = 1;

$query = 'SELECT * FROM users WHERE ';
if($use_part_1){
	$qArray[] = 'hair_color = ?';
	$bindParam->add('s', 'red');
}
if($use_part_2){
	$qArray[] = 'age = ?';
	$bindParam->add('i', 25);
}
if($use_part_3){
	$qArray[] = 'balance = ?';
	$bindParam->add('d', 50.00);
}

$query .= implode(' OR ', $qArray);

//call_user_func_array( array($stm, 'bind_param'), $bindParam->get());

echo $query . '<br/>';
var_dump($bindParam->get());
?>

This gets you the result that looks something like this:

SELECT * FROM users WHERE hair_color = ? OR age = ? OR balance = ?
array(4) { [0]=> string(3) "sid" [1]=> string(3) "red" [2]=> int(25) [3]=> float(50) }

[Editor's note: changed BindParam::add() to accept $value by reference and thereby prevent a warning in newer versions of PHP.] --was-- When dealing with a dynamic number of field values while preparing a statement I find this class useful. <?php class BindParam{ private $values = array(), $types = ''; public function add( $type, $value ){ $this->values[] = $value; $this->types .= $type; } public function get(){ return array_merge(array($this->types), $this->values); } } ?> Usage is pretty simple. Create an instance and use the add method to populate. When you're ready to execute simply use the get method. <?php $bindParam = new BindParam(); $qArray = array(); $use_part_1 = 1; $use_part_2 = 1; $use_part_3 = 1; $query = 'SELECT * FROM users WHERE '; if($use_part_1){ $qArray[] = 'hair_color = ?'; $bindParam->add('s', 'red'); } if($use_part_2){ $qArray[] = 'age = ?'; $bindParam->add('i', 25); } if($use_part_3){ $qArray[] = 'balance = ?'; $bindParam->add('d', 50.00); } $query .= implode(' OR ', $qArray); //call_user_func_array( array($stm, 'bind_param'), $bindParam->get()); echo $query . '<br/>'; var_dump($bindParam->get()); ?> This gets you the result that looks something like this: SELECT * FROM users WHERE hair_color = ? OR age = ? OR balance = ? array(4) { [0]=> string(3) "sid" [1]=> string(3) "red" [2]=> int(25) [3]=> float(50) } http://php.net/manual/en/mysqli-stmt.bind-param.php