note 110363 modified in mysqli-stmt.bind-param by danbrown
| From: | danbrown@php.net | Date: | Thu, 06 Dec 2012 14:45:35 +0000 |
| Subject: | note 110363 modified in mysqli-stmt.bind-param by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-192354@lists.php.net to get a copy of this message | ||
Dear all,
I was searching for a class which supports multiple calls to bind_param, because I have scenarios
where I build huge SQL statements over different functions with variable numbers of parameters. But
I didn't found one. So I have just written up this little piece of code I would like to share
with you. There is enough room to optimize these classes, but it shows the general idea. And for me
it works. In mbind_param_do() it seems to depend from the PHP version if makeValuesReferenced() must
be used or if $params can be used directly. In my case I have to use it.
The cool thing about this solution: You don't have to care about a lot if you are using my
mbind_ functions or not. You may also use default bind_param and the execute will still work.
<?php
class db extends mysqli {
public function prepare($query) {
return new stmt($this,$query);
}
}
class stmt extends mysqli_stmt {
public function __construct($link, $query) {
$this->mbind_reset();
parent::__construct($link, $query);
}
public function mbind_reset() {
unset($this->mbind_params);
unset($this->mbind_types);
$this->mbind_params = array();
$this->mbind_types = array();
}
//use this one to bind params by reference
public function mbind_param($type, &$param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = &$param;
}
//use this one to bin value directly, can be mixed with mbind_param()
public function mbind_value($type, $param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = $param;
}
public function mbind_param_do() {
$params = array_merge($this->mbind_types, $this->mbind_params);
return call_user_func_array(array($this, 'bind_param'),
$this->makeValuesReferenced($params));
}
private function makeValuesReferenced($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
public function execute() {
if(count($this->mbind_params))
$this->mbind_param_do();
return parent::execute();
}
private $mbind_types = array();
private $mbind_params = array();
}
$search1 = "test1";
$search2 = "test2";
$_db = new db("host","user","pass","database");
$query = "SELECT name FROM table WHERE col1=? AND col2=?";
$stmt = $_db->prepare($query);
$stmt->mbind_param('s',$search1);
//this second call is the cool thing!!!
$stmt->mbind_param('s',$search2);
$stmt->execute();
//this would still work!
//$search1 = "test1changed";
//$search2 = "test2changed";
//$stmt->execute();
...
$stmt->store_result();
$stmt->bind_result(...);
$stmt->fetch();
?>
--was--
Dear all,
I was searching for a class which supports multiple calls to bind_param, because I have scenarios
where I build huge SQL statements over different functions with variable numbers of parameters. But
I didn't found one. So I have just written up this little piece of code I would like to share
with you. There is enough room to optimize these classes, but it shows the general idea. And for me
it works. In mbind_param_do() it seems to depend from the PHP version if makeValuesReferenced() must
be used or if $params can be used directly. In my case I have to use it.
The cool thing about this solution: You don't have to care about a lot if you are using my
mbind_ functions or not. You may also use default bind_param and the execute will still work.
<?php
class db extends mysqli {
public function prepare($query) {
return new stmt($this,$query);
}
}
class stmt extends mysqli_stmt {
public function __construct($link, $query) {
$this->mbind_reset();
parent::__construct($link, $query);
}
public function mbind_reset() {
unset($this->mbind_params);
unset($this->mbind_types);
$this->mbind_params = array();
$this->mbind_types = array();
}
//use this one to bind params by reference
public function mbind_param($type, &$param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = &$param;
}
//use this one to bin value directly, can be mixed with mbind_param()
public function mbind_value($type, $param) {
$this->mbind_types[0].= $type;
$this->mbind_params[] = $param;
}
public function mbind_param_do() {
$params = array_merge($this->mbind_types, $this->mbind_params);
return call_user_func_array(array($this, 'bind_param'),
$this->makeValuesReferenced($params));
}
private function makeValuesReferenced($arr){
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
public function execute() {
if(count($this->mbind_params))
$this->mbind_param_do();
return parent::execute();
}
private $mbind_types = array();
private $mbind_params = array();
}
$search1 = "test1";
$search2 = "test2";
$_db = new db("host","user","pass","database");
$query = "SELECT name FROM table WHERE col1=? AND col2=?";
$stmt = $_db->prepare($query);
$stmt->mbind_param('s',$search1);
//this second call is the cool thing!!!
$stmt->mbind_param('s',$search2);
$stmt->execute();
//this would still work!
//$search1 = "test1changed";
//$search2 = "test2changed";
//$stmt->execute();
...
$stmt->store_result();
$stmt->bind_result(...);
$stmt->fetch();
http://php.net/manual/en/mysqli-stmt.bind-param.php