PHP execute() and mysqli_stmt_execute()

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

• execute()
• mysqli_stmt_execute()

Both functions are used to execute a prepared statement. The only difference is that execute() uses PHP mysqli object-oriented script, whereas mysqli_stmt_execute() uses 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 the above PHP example on the 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 fields name, age, and email has been inserted into the table named customer, available in the database named codescracker.

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

Note: In the above example, the "sis" as the first parameter of bind_param() refers to string integer string, the type of parameters given to the function, which are $name, $age, and $email.

The above PHP mysqli object-oriented script of the 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 or 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 the execute() function in PHP is:

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

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

PHP mysqli_stmt_execute()

When we want to run a prepared statement in PHP mysqli procedural style, we use the mysqli_stmt_execute() function. 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() 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_close() function is used to close an opened connection to the MySQL database in procedural style.

PHP mysqli_stmt_execute() Syntax

The syntax of the 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