how to bind multiple parameters to MySQLi query

Viewed 478 times

I have a mysql query, but I can't bind param for it

SELECT users.email,users.handle,userprofile.mobile FROM users,userprofile WHERE users.email =? OR users.handle =? OR userprofile.mobile=?

I've tried below line

$query = "SELECT users.email,users.handle,userprofile.mobile FROM users,userprofile WHERE users.email =? OR users.handle =? OR userprofile.mobile=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("sss",$email,$username,$mobile);
if ($stmt->execute()) {
if($stmt->num_rows){
   echo '......';
    }
}

but I've received and error :

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

Answers

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

Sunday, August 14, 2022

saranyams-9cf4b207bbcd

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Sunday, November 6, 2022

shuhamluvuns-e7688432bcec

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Wednesday, August 24, 2022

fred_read

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

Monday, August 8, 2022

lucio_paiva

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

Saturday, September 10, 2022

melih_mucuk

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysqli

how to bind multiple parameters to MySQLi query

Answers

php,mysqli