PHP execute() and mysqli_stmt_execute()

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

• execute()
• mysqli_stmt_execute()

Both functions are used to execute a prepared statement. The only difference is, the execute() uses with PHP MySQLi object-oriented script, whereas the mysqli_stmt_execute() uses with PHP MySQLi procedural script.

PHP execute()

The PHP execute() function is used when we need to execute a prepared statement 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 = "Jonas";
   $age = 41;
   $email = "jonas@xyz.com";
   
   if($stmt -> execute())
   {
      echo "Data inserted successfully.";
      // block of code, to process further
   }
   $conn->close();
?>

The output of above PHP example on execute() function is shown in the snapshot given below:

After executing the above PHP script, one record/row with values Jonas, 41, and jonas@xyz.com for the field name, age, and email, has been inserted into the table named customer, available in the database named codescracker.

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 close() is used to close an opened connection, in object-oriented style.

Note - In above example, the "sis" as first parameter of bind_param() refers to string integer string, the type of parameters given to the function, that are $name, $age, and $email. The detailed description is given in its separate tutorial.

The above PHP MySQLi object-oriented script of execute() function, 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 = "Jonas";
      $age = 35;
      $email = "jonas@xyz.com";
   
      $stmt -> execute();
   }
   $conn->close();
?>

To insert multiple records/rows, change the value of parameters and use execute() 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 = "Louis";
      $age = 31;
      $email = "louis@xyz.com";
      $stmt -> execute();
      
      $name = "Fynn";
      $age = 27;
      $email = "fynn@xyz.com";
      $stmt -> execute();
   }
   $conn->close();
?>

The two records will be inserted after executing the above PHP MySQLi object-oriented script.

PHP execute() Syntax

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

$stmt -> execute(parameters...);

The parameters refers to list of parameters. The parameters parameter is optional, and is used to provide array with that number of elements as of bound parameters available in the statement to execute, where all values treated as of string type.

PHP mysqli_stmt_execute()

The PHP mysqli_stmt_execute() function is used when we need to execute a prepared statement 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 = "Jonas";
      $age = 35;
      $email = "jonas@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_close() is used to close an opened connection to the MySQL database, in procedural style.

PHP mysqli_stmt_execute() Syntax

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

mysqli_stmt_execute($stmt, parameters...);

Similar to execute(), here too, the parameters parameter is optional.

PHP Online Test