PHP bind_param() and mysqli_stmt_bind_param()

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

• bind_param()
• mysqli_stmt_bind_param()

Both functions are used to bind variables to a prepared statement as parameters. The only difference is that bind_param() is used with object-oriented script, whereas 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 = "codescracker";
   
   $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 the above PHP example on bind_param() is shown in the snapshot given below:

Note: The mysqli() function 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 or return the error code (if any) from the last connect call in object-oriented style.

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

Note: The prepare() function 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() function is used to execute a prepared statement on the MySQL database in object-oriented style.

Note: The close() function 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", "", "codescracker");
   
   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 the above example, the sis refers to string integer string that is used to define the types of three parameters given to bind_param(): $name (s for string), $age (i for integer), and $email (s for string).

PHP bind_param() Syntax

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

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

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

• s: Used for string types
• i: Used for integer types
• d: Used for double types
• b: Used for blob to sent in packets

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

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   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 codescracker, at id number 2, the name Charlotte is stored. Therefore, the output should be:

Charlotte

Note: The bind_result() function is used to link or bind variables to a prepared statement so that the results can be stored in an object-oriented way.

Note: The fetch() function is used to fetch or get the results from a prepared statement into bound variables in an object-oriented style.

PHP mysqli_stmt_bind_param()

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

<?php
   $conn = mysqli_connect("localhost", "root", "", "codescracker");
   
   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() function is used to open a connection to the MySQL database server in procedural style.

Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.

Note: The mysqli_prepare() function 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() function is used to bind variables to a prepared statement as parameters in procedural style.

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

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

PHP mysqli_stmt_bind_param() Syntax

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

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

PHP Online Test