Bind Param with array of parameters

Question

I have a function that does this:

function registerUser($firstName, $lastName, $address, $postcode, $email, $password)
{
    $params = array($firstName, $lastName, $address, $postcode, $email, $password);
    $result = $this->db->bind("INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?)", 'ssssss', $params);
}

Which sends off to my database class, which does this:

public function bind($query, $type, $params)
{
    $this->query = $query;
    $stmt = $this->mysqli->prepare($this->query);
    $stmt->bind_param($type, $param);
    $stmt->execute;
}

The problem is this doesn't work.

What I was hoping to do, was to take the $params list and have it list them after the $type, so that the query would resemble:

$stmt->bind_param('ssssss', $firstName, $lastName, $address, $postcode, $email, $password);

But obviously I'm going about it the wrong way.

is there a way to make the array...transform as it were, into a list to be printed out at the bind_param query stage?

Answer 1

call_user_func_array "Call a callback with an array of parameters"

call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), $params));

should do the job

UPDATE: you have also to change your params array:

$params = array(&$firstName, &$lastName, &$address, &$postcode, &$email, &$password);

as mysqli_stmt::bind_param expects the second and the following parameters by reference.

---

EDIT: Your query seems to be wrong. Maybe you have less fields than you have variables there. Do:

"INSERT INTO Users (field1, field2, field3, field4, field5, field6) VALUES (?, ?, ?, ?, ?, ?)"

where you replace the name of the fields by the correct names

Answer 2

As of PHP 5.6 you can utilize argument unpacking as an alternative to call_user_func_array, and is often 3 to 4 times faster.

<?php
function foo ($a, $b) {
     return $a + $b;
}

$func = 'foo';
$values = array(1, 2);
call_user_func_array($func, $values);
//returns 3

$func(...$values);
//returns 3
?>

Taken from here.

So your code should look something like this:

public function bind($query, $type, $params)
        {

            $this->query = $query;
            $stmt = $this->mysqli->prepare($this->query);
            $stmt->bind_param($type, ...$params);
            $stmt->execute;
        }

Answer 3

The easiest way would be apparently switching from mysqli to PDO

It will let you to do it the way you want, and even without any additional functions:

function registerUser($firstName, $lastName, $address, $postcode, $email, $password)
{
    $sql  = "INSERT INTO Users VALUES (NULL, ?, ?, ?, ?, ?, ?)";
    $stmt = $this->db->prepare($sql);
    $stmt->execute(func_get_args());
}

Answer 4

You get your error "Call to a member function bind_param() on a non-object" most likely, because your $this->mysqli->prepare encounters some kind of error. (see http://php.net/manual/de/mysqli.prepare.php - it returns FALSE on error, which seems to be the case here)

After you have resolved that problem, try this instead of your call of $stmt->bind_param:

call_user_func_array(array($stmt, 'bind_param'), array_merge($type, $params));

Answer 5

It is important to note that mysqli_stmt_bind_param() requires parameters to be passed by reference, so the parameters for call_user_func_array() must be a reference. An example taken from class context:

function execute( string $query, string $type, array $params )
{
    if ( !$stmt = $this->mysqli->prepare( $query ) ) 
        throw new \Exception( 'Prepare failed: ' . $query . PHP_EOL . $this->mysqli->error );

    // call stmt->bind_param() with variables to bind passed as a reference 
    call_user_func_array( 
        array( $stmt, 'bind_param' ), 
        array_merge( 
            array( $type ), 
            array_map( function( &$item ) { return $item; }, $params ) 
        ) 
    );

    if ( !$stmt->execute() ) 
        throw new \Exception( 'Execute failed: ' . PHP_EOL . $stmt->error );

}

}

Answer 6

Today, I did some research myself in order to create a shorter method of using the prepare statement. The answer @bwoebi is very helpful but not working for an unknown amount of parameters so this is an addition to his answer.

For instance:

public function bind($query, $type, &...$params)
    {

        $this->query = $query;
        $stmt = $this->mysqli->prepare($this->query);
        call_user_func_array(array($stmt, "bind_param"), array_merge([$type], $params));
        $stmt->execute();
    }

using this thread: PHP: variable-length argument list by reference?

I managed to accomplish to pass on an unknown amount of parameters to the bind function inside the class. i then call upon the bind param function using call_user_func_array(...) with an array merge of the $type variable... (must be placed inside array for the merge)

Now i can call upon this function with $email and $password now being references:

$myClass->bind($query, "ss", $email, $password);

Answer 7

Not beautiful, but thats what I use.

function _BindParam(&$stmt, &$values){
    $types="";
    foreach($values as $val){
      if(is_string($val)){$types.="s";continue;}
      if(is_int($val)){$types.="i";continue;}
      if(is_float($val) || is_double($val)){$types.="d";continue;}
      throw new Exception('Invalid value type');
    }
   $stmt->bind_param($types,...$values);
}