Bind multiple parameters into mysqli query

by

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

More Related Contents:

  1. mysqli bind_param for array of strings
  2. How to bind an array of strings with mysqli prepared statement?
  3. How to prepare statement for update query? [duplicate]
  4. How can I bind an array of strings with a mysqli prepared statement?
  5. Object of class mysqli_result could not be converted to string
  6. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  7. Strict Standards: mysqli_next_result() error with mysqli_multi_query
  8. Why does mysqli num_rows always return 0?
  9. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  10. What is the difference between MySQL, MySQLi and PDO? [closed]
  11. parameters in MySQLi
  12. Retrieving Multiple Result sets with stored procedure in php/mysqli
  13. How to connect to MySQL database in PHP using mysqli extension?
  14. How do I count query result rows?
  15. How do you use IN clauses with mysqli prepared statements [duplicate]
  16. What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such
  17. How to fetch all in assoc array from a prepared statement?
  18. How to bind LIKE values using the PDO extension?
  19. Fatal error: Call to undefined function mysqli_result()
  20. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  21. Why I am getting an error when using w3school tutorial? [duplicate]
  22. Using wildcards in prepared statement
  23. mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it
  24. How to remove the fatal error when fetching an assoc array
  25. Fatal error: Call to undefined method mysqli_stmt::get_result()
  26. PHP MySQLi Multiple Inserts
  27. Fatal error: Call to a member function prepare() on a non-object in
  28. Set character set using MySQLi
  29. Cannot figure out how to run a mysqli_multi_query and use the results from the last query
  30. How to get a list of databases?

Leave a Comment