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.
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
- Switch to PDO. You can make one bind per function call with that
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);
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');
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.
Related Topics
How to Prevent Form Resubmission When Page Is Refreshed (F5/Ctrl+R)
In_Array() and Multidimensional Array
Reference: What Is a Perfect Code Sample Using the MySQL Extension
PHP - Floating Number Precision
How to Compare Two Dates in PHP
How to Log Errors and Warnings into a File
When Should I Use Prepared Statements
PHP Sessions Across Sub Domains
How to With MySQLi Make a Query With Like and Get All Results
PHP Sort a Multidimensional Array by Element Containing Y-M-D H:I:S Date
Getting the Location from an Ip Address
Displaying HTML With Blade Shows the HTML Code
How to Create Friendly Urls With .Htaccess
How Can One Use Multi Threading in PHP Applications
Simplest PHP Example For Retrieving User_Timeline With Twitter API Version 1.1
How to Search by Key=≫Value in a Multidimensional Array in PHP