MySQLi Tutorial MySQLi References

mysqli_stmt bind_param() Method



The mysqli_stmt::bind_param() / mysqli_stmt_bind_param() function is used to bind variables for the parameter markers in the SQL statement prepared by mysqli_prepare() or mysqli_stmt_prepare().

Note: If data size of a variable exceeds max. allowed packet size (max_allowed_packet), specify types b and use mysqli_stmt_send_long_data() to send the data in packets.

Syntax

//Object-oriented style
public mysqli_stmt::bind_param(types, var, vars)

//Procedural style
mysqli_stmt_bind_param(statement, types, var, vars)

Parameters

statement Required. For procedural style only: Specify a mysqli_stmt object returned by mysqli_stmt_init().
types Required. Specify a string that contains one or more characters which specify the types for the corresponding bind variables. The allowed characters are:
  • i - corresponding variable has type integer
  • d - corresponding variable has type double
  • s - corresponding variable has type string
  • b - corresponding variable is a blob and will be sent in packets
var, vars Required. Specify variables. The number of variables and length of string types must match the parameters in the statement.

Return Value

Returns true on success or false on failure.

Example: Object-oriented style

The example below shows the usage of mysqli_stmt::bind_param() method.

<?php
//establishing connection to the database
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//creating a prepared statement
$stmt = $mysqli->stmt_init();
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
$stmt->prepare($query);

//binding parameters
$stmt->bind_param('ssd', $name, $city, $salary);

//set parameters and execute
$name = "John";
$city = "London";
$salary = 2800;
$stmt->execute();

$name = "Marry";
$city = "Paris";
$salary = 2850;
$stmt->execute();

echo "Records inserted successfully.";

//closing the connection
$mysqli->close();
?>

The output of the above code will be similar to:

Records inserted successfully.

Example: Procedural style

The example below shows the usage of mysqli_stmt_bind_param() function.

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//creating a prepared statement
$stmt = mysqli_stmt_init($mysqli);
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
mysqli_stmt_prepare($stmt, $query);

//binding parameters
mysqli_stmt_bind_param($stmt, 'ssd', $name, $city, $salary);

//set parameters and execute
$name = "John";
$city = "London";
$salary = 2800;
mysqli_stmt_execute($stmt);

$name = "Marry";
$city = "Paris";
$salary = 2850;
mysqli_stmt_execute($stmt);

echo "Records inserted successfully.";

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be similar to:

Records inserted successfully.

Example: using ... to provide arguments

The ... operator can be used to provide variable-length argument list, for example - in a WHERE IN clause. See the example below:

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//creating a prepared statement
$stmt = mysqli_prepare($mysqli, "SELECT Name, Salary FROM Employee WHERE City IN (?, ?)");

//using ... to provide arguments
mysqli_stmt_bind_param($stmt, 'ss', ...['London', 'Paris']);

//executing the SQL statement
mysqli_stmt_execute($stmt);

//storing result in internal buffer
mysqli_stmt_store_result($stmt);

printf("%d rows found.\n", mysqli_stmt_num_rows($stmt));

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be similar to:

23 rows found.

❮ MySQLi Functions Reference