How to Bind Multiple Parameters to MySQLi Query

how to bind multiple parameters to MySQLi query

This is the correct syntax for binding params in mysqli

$SQL = "SELECT 
              users.email,
              users.handle,
              userprofile.mobile 
        FROM users,userprofile      
        WHERE users.email =? OR users.handle =? OR userprofile.mobile=?";

$stmt = $mysqli->prepare($SQL);
   
$stmt->bind_param("sss", $one,$two,$three);
$stmt->execute();

//do stuff

Bind multiple parameters into mysqli query

Unfortunately, by default, bind_param() doesn't accept an array instead of separate variables. However, since PHP 5.6 there is a magnificent improvement that will do the trick.

To bind an arbitrary number of variables into mysqli query you will need an argument unpacking operator. It will make the operation as simple and smooth as possible.

For example, to use a PHP array with a mysql's IN() operator, you will need the following code

// our array
$array = ['a','b','c']; 

// create an SQL query with placeholders and prepare it
$in    = str_repeat('?,', count($array) - 1) . '?'; //  returns ?,?,?...
$sql   = "SELECT name FROM table WHERE city IN ($in)"; 
$stmt  = $mysqli->prepare($sql);

// create the types string dynamically and bind an array
$types = str_repeat('s', count($array)); // returns sss...
$stmt->bind_param($types, ...$array); 

// execute and fetch the rows
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch the data   

How to bind multiple parameters to MySQLi prepared statement

You can only call bind_param once, so you'll have to add all the params you want into an array, then call it via call_user_func_array.

Try this:

$params = array('');
foreach( $_POST as $name => $value ) {
    $params[0] .= 'sss';
    array_push($params, $id, $name, $value);
}

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

if( $stmt->execute()) {
    echo '<h1>OK</h1>';
}

Can I bind multiple values as a single parameter using MYSQLI and PHP?

Can I bind multiple values as a single
parameter using MYSQLI and PHP?

No you cannot.

For your situation, you should build the query string programmatically. If you are guaranteed it will always be three values, you could add three markers to the SQL then bind via looping over the array.

How to bind mysqli parameters using loop and store results in array?

You can't bind an array to an SQL parameter. You can use a parameter in SQL in place of a single literal value. Not a list of values, or an expression, or a column name or table name.

To solve the task in your case, you can use either of two solutions:

First solution: loop over $genre array, bind each value one at a time and execute the SQL query for each value.

$stmt->prepare($selectGenre_sql);
$genre = array();
foreach ($gengre as $genreID) {
    $stmt->bind_param('s', $genreID);
    $stmt->execute();
    $stmt->bind_result($genres);
    while ($stmt->fetch()) {
        $genre[] = $genres;
    }
}

Second solution: execute the query once, with multiple parameters, one for each value in the array. This requires some tricky code to build a variable number of ? placeholders in the SQL query, separated by commas.

$selectGenre_sql = 'SELECT genreID FROM genres WHERE dbGenre IN ('
 . join(',', array_fill(0, count($genre), '?')) . ')';

Also you need to get tricky calling bind_param() with a variable number of arguments based on the elements in your $genre array:

$stmt->prepare($selectGenre_sql);
$temp = array();
foreach ($genre as $key => $value) {
    $temp[] = &$genre[$key];
}

array_unshift($genre, str_repeat('i', count($genre)));
call_user_func_array(array($stmt, 'bind_param'), $genre);

$stmt->execute();

$stmt->bind_result($genres);

$array1 = array();
while ($stmt->fetch()) {
    $array1[] = $genres;
}

You might want to consider using PDO_MYSQL because it's easier to bind parameters from an array. The MySQLi interface is pretty awkward for this case.

Can you pass multiple params using OR to an SQL/PHP single bind statement?

What is the proper way to bind with multiple parameters on one bind.

Think of this rule: You can use a parameter in an SQL query in place of one single scalar value.

That is, where you would normally use in your SQL statement one numeric constant, one quoted string constant, or one quoted date constant, you can replace that one query element with one parameter.

Parameters can not be used in place of:

• Lists of multiple values
• SQL expressions
• SQL keywords
• Identifiers like table names, column names, or database names

If you want to compare your color column to multiple values, you need multiple parameter placeholders.

$posts = DB::select('SELECT * FROM cars
WHERE color IN (?, ?, ?, ?)');

It doesn't work to pass a string containing a comma-separated list of values to a single placeholder. You end up with a query that works as if you had written it this way:

SELECT * FROM cars WHERE color IN ('12,34,56,78');

This query will run without error, but it won't give you want you want. In a numeric context, the string '12,34,56,78' has a numeric value of 12. It ignores all the rest of the characters in the string after the first non-numeric character ,. So it will succeed in searching for color 12, but it will fail to find the other colors.

---

PDO makes it easy to deal with lists of values, because when it is time to supply the values for a parameterized query, you can simply pass an array to the PDOStatement::execute() function.

If you don't know how many color values you need to search for, you can use PHP builtin functions to make a list of question mark placeholders that is the same length as your array of color values:

$list_of_question_marks = implode(',', array_fill(1, count($color_values), '?'));
$sql = "SELECT * FROM cars WHERE color IN ($list_of_question_marks)" 
$stmt = $pdo->prepare($sql);
$stmt->execute($color_values);

multiple calls to $stmt- bind_param

Sadly mysqli doesn't support this. Calling the function over and over again overwrites the values, so you're only binding one param when you clearly have more.

There's a couple of ways to get around this

1. Switch to PDO. You can make one bind per function call with that

2. Bind the params as one aggregate using call_user_func_array

   $sqltype = '';
   $sqldata = [];
   foreach($bindParams as $type => $data) {
       $sqltype .= $type;
       $sqldata[] = &$data; // MUST be a reference
   }
   array_unshift($sqldata, $sqltype); // prepend the types
   call_user_func_array([$stmt, 'bind_param'], $sqldata);
   

Dynamically bind mysqli_stmt parameters and then bind result (PHP)

Okay, here is a way to do it:

Edited, to fix bug when fetching multiple rows

$sql = "SELECT `first_name`,`last_name` FROM `users` WHERE `country` =? AND `state`=?";
$params = array('Australia','Victoria');

/*
    In my real app the below code is wrapped up in a class 
    But this is just for example's sake.
    You could easily throw it in a function or class
*/

// This will loop through params, and generate types. e.g. 'ss'
$types = '';                        
foreach($params as $param) {        
    if(is_int($param)) {
        $types .= 'i';              //integer
    } elseif (is_float($param)) {
        $types .= 'd';              //double
    } elseif (is_string($param)) {
        $types .= 's';              //string
    } else {
        $types .= 'b';              //blob and unknown
    }
}
array_unshift($params, $types);

// Start stmt
$query = $this->connection->stmt_init(); // $this->connection is the mysqli connection instance
if($query->prepare($sql)) {

    // Bind Params
    call_user_func_array(array($query,'bind_param'),$params);

    $query->execute(); 

    // Get metadata for field names
    $meta = $query->result_metadata();

    // initialise some empty arrays
    $fields = $results = array();

    // This is the tricky bit dynamically creating an array of variables to use
    // to bind the results
    while ($field = $meta->fetch_field()) { 
        $var = $field->name; 
        $$var = null; 
        $fields[$var] = &$$var; 
    }

    $fieldCount = count($fieldNames);

// Bind Results                                     
call_user_func_array(array($query,'bind_result'),$fields);

$i=0;
while ($query->fetch()){
    for($l=0;$l<$fieldCount;$l++) $results[$i][$fieldNames[$l]] = $fields[$fieldNames[$l]];
    $i++;
}

    $query->close();

    // And now we have a beautiful
    // array of results, just like
    //fetch_assoc
    echo "<pre>";
    print_r($results);
    echo "</pre>";
}

Mysqli and binding multiple value sets during insert

Simple:

$stmt = $mysqli->prepare("INSERT INTO some_names (firstName, lastName) VALUES (?, ?),(?,?),(?,?)")
$stmt->bind_param('ssssss', 'Joe', 'Smith','Fred','Sampson','Lisa','Pearce');

---

Related Topics