PHP MySQL Prepared Statements



In this source code example, we will see how to use the prepared statements in PHP using MySQLi.

A prepared statement (also known as a parameterized statement) is simply a SQL query template containing a placeholder instead of the actual parameter values. These placeholders will be replaced by the actual values at the time of execution of the statement.

PHP MySQL Prepared Statements

Here is a PHP script that demonstrates the usage of prepared statements in PHP:

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$host= "localhost";
$username= "root";
$password = "";

$db_name = "demo_db";

$mysql_connection = mysqli_connect($host, $username, $password, $db_name);
 
// Check connection
if ($mysql_connection->connect_errno) {
    printf("connection failed: %s\n", $mysql_connection->connect_error());
    exit();
}
 
//Create SQL insert query and store in a variable
$sql = "INSERT INTO students (first_name, last_name, email) VALUES (?, ?, ?)";
 
if($stmt = mysqli_prepare($mysql_connection, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    /* Set the parameters values and execute
    the statement again to insert another row */
    $first_name = "tom";
    $last_name = "cruise";
    $email = "cruise@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Set the parameters values and execute
    the statement to insert a row */
    $first_name = "Umesh";
    $last_name = "Jadhav";
    $email = "umesh@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($mysql_connection);
}
 
// Close statement
mysqli_stmt_close($stmt);
 
// Close connection
mysqli_close($mysql_connection);
?>
Let's understand the above PHP script.

The mysqli_connect() function opens a new connection to the MySQL server:
$host= "localhost";
$username= "root";
$password = "";

$db_name = "demo_db";

$mysql_connection = mysqli_connect($host, $username, $password, $db_name);
Create SQL insert query with a placeholder:
//Create SQL insert query and store in a variable
$sql = "INSERT INTO students (first_name, last_name, email) VALUES (?, ?, ?)";
The mysqli_prepare() function is used to prepare an SQL statement for execution:

if($stmt = mysqli_prepare($mysql_connection, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
    
    /* Set the parameters values and execute
    the statement again to insert another row */
    $first_name = "tom";
    $last_name = "cruise";
    $email = "cruise@mail.com";
    mysqli_stmt_execute($stmt);
    
    /* Set the parameters values and execute
    the statement to insert a row */
    $first_name = "Umesh";
    $last_name = "Jadhav";
    $email = "umesh@mail.com";
    mysqli_stmt_execute($stmt);
    
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($mysql_connection);
}

Closing resources:

// Close statement
mysqli_stmt_close($stmt);
 
// Close connection
mysqli_close($mysql_connection);

Comments