PHP bind_param() and mysqli_stmt_bind_param()

This article is created to cover the two functions in PHP, that are:

• bind_param()
• mysqli_stmt_bind_param()

Both functions are used to bind variables to a prepared statement, as parameters. The only difference is, the bind_param() is used with object-oriented script, whereas the mysqli_stmt_bind_param() is used with procedural script.

PHP bind_param()

The PHP bind_param() function is used to bind variables to a prepared statement, as parameters, in PHP MySQLi object-oriented style. For example:

<?php
   $server = "localhost";
   $user = "root";
   $pass = "";
   $db = "fresherearth";
   
   $conn = new mysqli($server, $user, $pass, $db);
   
   if($conn->connect_errno)
   {
      echo "Database connection failed!<BR>";
      echo "Reason: ", $conn->connect_error;
      exit();
   }
   
   $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)";
   
   $stmt = $conn -> prepare($sql);
   $stmt -> bind_param("sis", $name, $age, $email);
   
   $name = "Susan";
   $age = 35;
   $email = "susan@xyz.com";
   
   if($stmt -> execute())
   {
      echo "Record inserted successfully.";
      // block of code, to process further
   }
   $conn->close();
?>

The output produced by above PHP example on bind_param(), is shown in the snapshot given below:

Note - The mysqli() is used to open a connection to the MySQL database server, in object-oriented style.

Note - The new keyword is used to create a new object.

Note - The connect_errno is used to get/return the error code (if any) from last connect call, in object-oriented style.

Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.

Note - The prepare() is used to prepare an SQL statement before its execution on the MySQL database, in object-oriented style, to avoid SQL injection.

Note - The execute() is used to execute a prepared statement on the MySQL database, in object-oriented style.

Note - The close() is used to close an opened connection to the MySQL database, in object-oriented style.

The above example can also be written as:

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn->connect_errno)
   {
      $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)";
   
      $stmt = $conn -> prepare($sql);
      $stmt -> bind_param("sis", $name, $age, $email);
   
      $name = "Susan";
      $age = 35;
      $email = "susan@xyz.com";
   
      $stmt -> execute();
   }
   $conn->close();
?>

Note - In above example, the sis refers to string integer string, used to define the type of three parameters, given to bind_param(), that are $name (s for string), $age (i for integer), and $email (s for string).

PHP bind_param() Syntax

The syntax of bind_param() function in PHP, is:

$mysqli_stmt -> bind_param(types, variables..);

The types parameter is used to specify the parameters(s) types. Here are the characters, used to specify the type:

• s - Used for string type
• i - Used for integer type
• d - Used for double type
• b - Used for blob to sent in packets

The variables are basically the set of parameters, used to replace question marks (?) from the prepared SQL statement. For example:

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn->connect_errno)
   {
      $stmt = $conn->prepare("SELECT name FROM customer where id=?");
      
      if($stmt==true)
      {
         $stmt->bind_param('i', $id);
         $id = 2;
         
         if($stmt->execute())
         {
            $stmt->bind_result($res);
            $stmt->fetch();
      
            echo $res;
         }
      }
   }
   $conn->close();
?>

Since in the table named customer, available in the database fresherearth, at id number 2, the name Charlotte is stored. Therefore the output should be:

Charlotte

Note - The bind_result() is used to bind variables to a prepared statement for result storage, in object-oriented style.

Note - The fetch() is used to fetch results from a prepared statement into bound variables, in object-oriented style.

PHP mysqli_stmt_bind_param()

The PHP mysqli_stmt_bind_param() function is used to bind variables to prepared statement as parameters in PHP MySQLi procedural style. For example:

<?php
   $conn = mysqli_connect("localhost", "root", "", "fresherearth");
   
   if(!mysqli_connect_errno())
   {
      $sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES (?, ?, ?)";
   
      $stmt = mysqli_prepare($conn, $sql);
      mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email);
   
      $name = "Susan";
      $age = 35;
      $email = "susan@xyz.com";
   
      mysqli_stmt_execute($stmt);
   }
   mysqli_close($conn);
?>

Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.

Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.

Note - The mysqli_prepare() is used to prepare an SQL statement before its execution on the MySQL database, in procedural style, to avoid SQL injection.

Note - The mysqli_stmt_bind_param() is used to bind variables to a prepared statement, as parameters, in procedural style.

Note - The mysqli_stmt_execute() is used to execute a prepared statement on the MySQL database, in procedural style.

Note - The mysqli_close() is used to close an opened connection to the MySQL database, in procedural style.

PHP mysqli_stmt_bind_param() Syntax

The syntax of mysqli_stmt_bind_param() function in PHP, is:

mysqli_stmt_bind_param($mysqli_stmt, types, parameters...);

PHP Online Test