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();