PHP prepare() and mysqli_prepare()

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

• prepare()
• mysqli_prepare()

Both the functions are used to prepare an SQL statement before/for execution on the database. The only difference is, prepare() is used with PHP MySQLi object-oriented script, whereas the mysqli_prepare() is used with PHP MySQLi procedural script.

PHP prepare()

The PHP prepare() function is used to prepare an SQL statement before its execution against the database, 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 = "Martin";
   $age = 35;
   $email = "martin@xyz.com";
   
   if($stmt -> execute())
   {
      echo "Data inserted successfully.";
      // block of code, to process further
   }
   $conn->close();
?>

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

In above example, the "sis" refers to, string integer string, the types of three parameters given to bind_param(), that are $name, $age, and $email.

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 bind_param() is used to bind variables to a prepared statement, as parameters, in object-oriented style.

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, in object-oriented style.

The above example can also be created in this way:

<?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 = "Martin";
      $age = 35;
      $email = "martin@xyz.com";
   
      $stmt -> execute();
   }
   $conn->close();
?>

PHP prepare() Syntax

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

$mysqli_stmt -> prepare(SQLstatement)

Use prepare() to Prepare SELECT Statement with WHERE Clause

<?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() == true)
         {
            $stmt->bind_result($res);
            $stmt->fetch();
      
            echo $res;
         }
      }
   }
   $conn->close();
?>

Since in the table customer, Charlotte is available in the name field/column, at row with id 2. 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.

Use prepare() to Prepare SELECT Statement without WHERE Clause

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn->connect_errno)
   {
      $stmt = $conn->prepare("SELECT name, email FROM customer");
      $stmt->execute();
      $stmt->bind_result($x, $y);
      while($stmt->fetch())
      {
         echo "Name: ", $x, "<BR>";
         echo "Email: ", $y, "<HR>";
      }
   }
   $conn->close();
?>

The output produced by this example is shown in the snapshot given below:

Use prepare() to Prepare SELECT Statement that Selects All Rows

<?php
   $conn = new mysqli("localhost", "root", "", "fresherearth");
   
   if(!$conn->connect_errno)
   {
      $stmt = $conn->prepare("SELECT * FROM customer");
      $stmt->execute();
      $result = $stmt->get_result();
      while($row = $result->fetch_array())
      {
         echo "Name: ", $row['name'];
         echo "<BR>";
         echo "Email: ", $row['email'];
         echo "<BR>";
         echo "Age: ", $row['age'];
         echo "<HR>";
      }
   }
   $conn->close();
?>

The output produced by above PHP example, is:

Note - The get_result() is used to get result set from a prepared statement.

Note - The fetch_array() is used, when we need to fetch and get the result as an enumerated array or as an associative array, or as both, in object-oriented style.

PHP mysqli_prepare()

The PHP mysqli_prepare() function is used to prepare an SQL statement before its execution against database, 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 = "Noah";
      $age = 35;
      $email = "noah@xyz.com";
      
      if(mysqli_stmt_execute($stmt))
      {
         echo "Data inserted successfully.";
         
         // block of code, to process further
      }
   }
   mysqli_close($conn);
?>

The SQL statement (statement template) can contain zero, one or more placeholders (using question marks, ?). The parameter markers (?) must be bound to the application variable using the function named mysqli_stmt_bind_param() before executing the statement.

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_prepare() Syntax

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

mysqli_prepare(connectionVariable, SQLstatement)

PHP Online Test