I was searching for a class which supports multiple calls to mysqli_stmt::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. And for me it works. In doBindParam() it seems to depend from the PHP version if referenceValues() must be used or if $params can be used directly. Please note that reference is required for PHP 5.3 an higher. If won`t work using type-safe executing, than see here (http://bugs.mysql.com/bug.php?id=48284) for fixing your PHP and MySQL version. 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 bind(…) functions or not. You may also use default bind_param and the execute will still work.
The GK_MySQLi class
/** * Wraps the mysqli class. * Represents a connection between PHP and a MySQL database. */ class GK_MySQLi extends mysqli { public function prepare($query) { return new GK_MySQLi_Stmt($this, $query); } }
The GK_MySQLi_Stmt class
/** * Wraps the mysqli_stmt class. * Represents a prepared statement. */ class GK_MySQLi_Stmt extends mysqli_stmt { const INTEGER = 'i'; const DOUBLE = 'd'; const STRING = 's'; const BLOB = 'b'; const NULL = null; private $bindTypes = ''; private $bindParams = array(); private $typeSafe = false; /** * @param string $link * @param string $query */ public function __construct($link, $query) { $this->resetBinding(); parent::__construct($link, $query); } /** * @return GK_Stmt */ public function resetBinding() { unset($this->bindParams, $this->bindTypes); $this->bindParams = array(); $this->bindTypes = ''; return $this; } /** * Use this one to bin variable by reference. * @param string $type * @param mixed $variable * @return GK_Stmt * @throws LogicException */ public function bindVariable($type, &$variable) { if (isset($variable) || $variable === null){ $this->bindTypes .= $type; $this->bindParams[] = &$variable; return $this; } throw new LogicException('variable passed by reference does not exist'); } /** * Use this one to bind value directly, can be mixed with bindVariable() * @param string $type * @param mixed $value * @return GK_Stmt */ public function bindValue($type, $value) { $this->bindTypes .= $type; $this->bindParams[] = $value; return $this; } /** * Bind directly by giving the value - the type will be found automatically. * @param mixed $value * @return GK_Stmt */ public function bind($value) { $this->bindTypes .= $this->findType($value); $this->bindParams[] = $value; return $this; } /** * @param mixed $value * @return null|string */ protected function findType($value) { if ($value === (string)$value) { return self::STRING; } if ($value === (int)$value) { return self::INTEGER; } if ($value === (boolean)$value) { return self::INTEGER; } if (is_float($value)) { return self::DOUBLE; } if ($value === null) { return self::NULL; } return self::STRING; } /** * @param bool $typeSafe * @return mixed */ public function doBindParam($typeSafe) { return call_user_func_array( array($this, 'bind_param'), $this->referenceValues($this->bindParams, $typeSafe) ); } /** * @param array $params * @param bool $typeSafe * @return array */ private function referenceValues(array $params, $typeSafe = false) { $stmtParams = array(); // reference is required for PHP 5.3+ if (strnatcmp(phpversion(), '5.3') >= 0) { foreach ($this->bindParams as $k => &$param) { $stmtParams[$k] = &$param; } } if ($typeSafe === true) { array_unshift($stmtParams, $this->bindTypes); } else { array_unshift($stmtParams, str_repeat('s', count($params))); } return $stmtParams; } /** * Type-safe binding. * It is possible that wont work, than see here for fixing your system * @link http://bugs.mysql.com/bug.php?id=48284 * @return GK_Stmt */ public function typeSafe() { $this->typeSafe = true; return $this; } /** * @return bool */ public function execute() { if (count($this->bindParams)) { $this->doBindParam($this->typeSafe); } return parent::execute(); } }
Create a database connection and a test table
$mysqli = new GK_MySQLi('database-name', 'user-name', 'pwd', 'test_db_name', 3306, '/tmp/mysql.sock'); $mysqli->query('DROP TABLE IF EXISTS bind_param_test_table'); $mysqli->query( 'CREATE TABLE `bind_param_test_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `label` text NOT NULL, `order` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8' );
Prepare the insert statement
$stmt = $mysqli->prepare( "INSERT INTO `bind_param_test_table` (`label`, `order`) VALUES (?, ?)" );
Let us bind variables by reference
$label = 'some label text here'; $order = 22; $stmt->bindVariable($stmt::STRING, $label)->bindVariable($stmt::INTEGER, $order); $stmt->execute();
Let us bind by values directly
$stmt->resetBinding() ->bindValue($stmt::STRING, 'the label') ->bindValue($stmt::INTEGER, 456); $stmt->execute();
Let us bind values directly and variables by reference
$stmt->resetBinding() ->bindValue($stmt::STRING, 'second-label-here') ->bindVariable($stmt::INTEGER, $order); $stmt->execute();
Let GK_MySQLi_Stmt bind our values and variables automatically
$stmt->resetBinding()->bind(11)->bind($order); $stmt->execute();
A interesting fix of that bug – but who in hell is working with mysqli nowadays?