Using mysqli_bind_param with an unknown number of variables

[Darkmeerkat]

I'm trying to write a little wrapper for executing MySQL statements, and I've run into a wall trying to use mysqli_bind_param without knowing the number of variables I'm binding. Here's what I've got:

/**
   * Note:
   *   . execSQL is variadic, just like bind_param
   *   . prepareTypeStr is another custom function that generates
   *     the type string `mysqli_bind_param` requires through variadic
   *    parameters as well
   */

private function execSQL( $prepStmt ) {
  $variables = func_get_args();
  array_shift( $variables ); // first arg is prepStmt, not a variable

  $query = $this->db->prepare( $prepStmt );
  if( $query ) {
    /**
       * here's the problem: I've now got an array full of the
       * variables passed to execSQL, but bind_param only
       * accepts multiple parameters.
       */
    $query->bind_param(
                prepareTypeStr( /* variable1, variable2, ... */ ),
                /* variable1, variable2, ... */ );

    // ... rest of the function ...
  }
}

Any ideas?

[Darkmeerkat]

I've hacked a solution out to my own problem; it's a really messy hack, but it seems to work. Instead of calling bind_param directly, I use create_function so I can dynamically generate the call to bind_param within a string. That way, it calculates the number of variables in the prepared statement, makes a list of that many comma-separated variables names, and includes that in the string passed to create_function . After that, it's just a matter of calling the lambda function (and passing the query object as a reference).

/**
  * This creates the string of unique variable names
  */
for( $i = 0; $i < $numVars; ++$i ) {
        $thisVar = "v$i";
        $variadicParams .= '$' . $thisVar;
        if( $i != $numVars - 1 ) // don't append a comma on the last variable
          $variadicParams .= ', ';

        $variadicExtVars[ $thisVar ] = $variables[ $i ];
}

/**
  * Now $variadicExtVars is an associative array that we
  * can neatly extract. ($variadicExtVars[ 'v0' ] == 'whatever-got-passed-here',
  * for example, and when we extract that within the lambda function,
  * $v0 will equal 'whatever-got-passed-here.'
  */

   /**
       * prepFunc -- hooks $variadicExtVars into prepareTypeStr, called
       * only by $bindParamFunc();
       * This is just for my own prepareTypeStr function, this would have
       * to change depending on how you generate the
       * mysqli_bind_param type string.
       */
      $prepFunc = create_function( '$vars', "extract( \$vars ); return prepareTypeStr( $variadicParams );" );

      /**
       * bindParamFunc -- links $variadicExtVars and $prepFunc() together to use in a dynamic bind_param call
       */
      $bindParamFunc = create_function( '$vars, &$query',
        'extract( $vars ); $query->bind_param( \'' . $prepFunc( $variadicExtVars ) . '\', ' . $variadicParams . ' );' );

      $bindParamFunc( $variadicExtVars, $query );

[Darkmeerkat]

Aha! I learned of a much cleaner and quicker way to do this. It turns out PHP provides a function just for circumstances in which you need to call these kinds of functions variably, call_user_func_array . So now, instead of all that string-crafting junk, I can just do:

$typeStr = call_user_func_array( 'prepareTypeStr', $variables );
array_unshift( $variables, $typeStr );
call_user_func_array( array( $query, 'bind_param' ), $variables );

Much better!