PHP prepare() and mysqli_prepare()

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

• prepare()
• mysqli_prepare()

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

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 = "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 = "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 the above PHP example using the prepare() function is shown in the snapshot given below:

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

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

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

The above example can also be created in this way:

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

PHP prepare() Syntax

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

$mysqli_stmt -> prepare(SQLstatement)

Prepare a SELECT statement with a WHERE clause using prepare()

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

Since in the table customer, Charlotte is available in the name field or column at row with id 2. Therefore, the output should be:

Charlotte

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

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

Prepare a SELECT statement without a WHERE clause with prepare()

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   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 screenshot provided below displays the output this example produced:

Use prepare() to prepare a SELECT statement that selects all rows

<?php
   $conn = new mysqli("localhost", "root", "", "codescracker");
   
   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 the above PHP example is:

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

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

PHP mysqli_prepare()

The PHP mysqli_prepare() function is used to prepare an SQL statement before its execution against a database 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 = "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() 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_prepare() Syntax

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

mysqli_prepare(connectionVariable, SQLstatement)

PHP Online Test