Parameters in MySQLi

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

When is it necessary to bind parameters with MySQLi?

Binding parameters is a good idea in any INSERT statement as it will prevent SQL injection, and will also sanitize your strings for free.

I usually get it working using question mark in prepare statement like this:

//Prepare insert statement.
if ($InsertEventQuery = $mysqli->prepare("INSERT into events(eventname, eventdesc, eventmonth, eventdate, eventyear, eventstart, eventend) VALUES (?, ?, ?, ?, ?, ?, ?)"))
{
    //Bind parameters of insert statement.
    $InsertEventQuery->bind_param('ssiiiii', $EventName, $EventDesc, $EventMonth, $EventDate, $EventYear, $EventStart, $EventEnd);

Why doesn't MySQLi library natively support named parameters?

MYSQLi doesn't support named parameters for two main reasons:

1. It is "intended" (I use this term loosely) to be used with a wrapper and
2. It's counterpart, PDO, does - and there is no point re-inventing the wheel

To elaborate on point 1: mysqli, despite its many downfalls when compared to PDO, becomes easily comparable with a good wrapper - that is, named parameters (among others) are supported by the wrapper rather than mysqli itself. This is by design for one sole reason:

1. Mysqli is designed to be a fast and flexible library.

If the developers incorporated many more features into the base library, it becomes, counter intuitively, less flexible and requires longer load/execution times.

Both mysqli and pdo were released with PHP 5 (PDO with version 5.3, I believe) and as such are intended for different uses.

You want faster execution times? use mysqli without a wrapper. You want named parameters? use PDO or build a mysqli wrapper to handle such - but be warned, this will hinder your execution times.

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   

Mysqli - Correct parameter for date

You can use "s" this could be used for date, datetime.
Its treated as just any other string.

$stmt->bind_param('s', $date);

using prepared mysqli statements to bind parameters into the SELECT section of a query

Prepared statements only allow you to bind values, other constructs (such as fields, tables or functions, let alone whole bits of SQL) are not allowed.

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>';
}

Using one parameter multiple times in prepared mysqli-statement

Just to close the question:

The answer is no.

If you want to bind a parameter only one time and using it multiple times in a query you have to use PDO and this maybe also needs a special configuration.

But there seems to be more reasons to use PDO instead of mysqli, according to this great answer or this.

But sure there are workarounds. See the other answers to this question.

---

Related Topics