I've written some code today to be able to dynamically execute prepared statements/parameterized queries.
I need a fresh pair of eyes to look through my code for eventual performance gains that can be made.
I've tested the code on up to 20 parameters without problems.
For formatting I use this: {0:i}
, with position of argument and type of argument
class p_mysqli extends mysqli
{
public function __construct($host = 'localhost', $user = null, $pass = null, $db = null, $port = null, $socket = null)
{
@parent::__construct($host, $user, $pass, $db, $port, $socket);
}
public function QueryParam($sql)
{
// Find all parameters to be bound
preg_match_all("/{(\d{1,}:.)}/", $sql, $matches);
$noStripped = $matches[0];
$sql = str_replace($noStripped, "?", $sql);
$params = $matches[1];
$queryTypes = Array();
$queryParams = Array();
$statement = $this->stmt_init();
$statement = $this->prepare($sql);
if(!$statement)
die("Error on prepare: " . $this->error);
if(count($params) > 0)
{
// Get params and pop SQL-query from array
// This is also the part I'm wondering about..
$arguments = func_get_args();
$arguments = array_reverse($arguments);
array_pop($arguments);
$arguments = array_reverse($arguments);
for($i = 0; $i < count($params); $i++)
{
$lp = preg_split("/:/", $params[$i]);
$queryTypes[] = $lp[1];
$queryParams[] = &$arguments[$lp[0]];
}
$query = Array();
$query[0] = join("", $queryTypes);
$query = array_merge($query, $queryParams);
$stateRet = call_user_func_array(array(&$statement, 'bind_param'), $query);
if(!$stateRet)
die("Error on parameterbindning: " . $statement->error);
}
$statement->execute() or die("Error on execute: " . $statement->errno);
$ret = $statement->get_result();
$statement->close();
return $ret;
}
}
Example usage:
$indb->queryParam("SELECT COUNT(*) counted_column_test FROM users WHERE userid != {0:i} AND disabled = {2:b} AND email = {1:s}", 1, "[email protected]", false);
Updated code:
class ParamMysqli
{
public function __construct(mysqli $inDb)
{
$this->db = $inDb;
}
public $db = null;
public function queryParam($sql)
{
preg_match_all("/{(\d{1,}:.)}/", $sql, $matches);
$noStripped = $matches[0];
$sql = str_replace($noStripped, "?", $sql);
$params = $matches[1];
$statement = $this->db->stmt_init();
$statement = $this->db->prepare($sql);
if(!$statement)
die("Error at prepare: " . $this->db->error);
if(count($params) > 0)
{
$queryTypes = '';
$queryParams = Array();
$arguments = func_get_args();
array_shift($arguments);
foreach($params as $param)
{
$lp = explode(":", $param);
$queryTypes .= $lp[1];
$queryParams[] = &$arguments[$lp[0]];
}
$query = Array();
$query[0] = $queryTypes;
$query = array_merge($query, $queryParams);
$stateRet = call_user_func_array(array($statement, 'bind_param'), $query);
if(!$stateRet)
die("Error at parameterbindning: " . $statement->error);
}
$statement->execute() or die("Error at execute: " . $statement->error);
$ret = $statement->get_result();
$statement->close();
return $ret;
}
}
//Example usage is as before
$__db = new mysqli(HOST, USER, PASS, DB);
$indb = new ParamMysqli($__db);
$result = $indb->queryParam("SELECT * FROM users WHERE userid = {0:i}", 1);