Prepared Statement in PHP With MySQLi

A prepared statement is one of the popular methods in PHP for database management. As a backend developer, it is necessary to learn about prepared statements in PHP. Here, we are going to see what is the prepared statement, what are the advantages, and how to use it with examples.

Must Read: MySQL Queries and Some Operators

Prepared Statement

A prepared statement or parameterized statement is one type of template which contains the placeholders instead of having actual parameter values. During execution, these parameters are replaced with actual parameter values.

The parameters differ for different databases. For example:
MySQL database supports “?” as an anonymous positional placeholder.

Normal insert query:

INSERT INTO employee (fname, lname, email) VALUES ($fname, $lname, $email);

Query with placeholders in MYSQL:

INSERT INTO employee (fname, lname, email) VALUES (?, ?, ?);

The execution of the prepared statement consists of two stages:

1. Prepare
2. Execute

Which Activities Are Performed During the Prepare State?

During the prepare state, the MySQL statement template is created and sent to the database server. The server checks the prepared template for syntax errors or query optimization. If everything is perfect, then it stores the template for future use.

Which Activities Are Performed During the Execute State?

During the executing state, the parameters are sent to the MySQL server, and the server creates a query from the prepared template executes it for these values.

The prepare method has various advantages. We can use it when we repeatedly execute a single statement, like multiple insert queries with different values.

Let’s see some of the advantages of the prepared statement:

• They are handy against SQL injections. (SQL injection can be used by an attacker to access your database illegally)
• They reduce the parsing time as a preparation for the query is done only once.
• Bound parameters minimize bandwidth to the server as we need to send only the parameters each time, not the whole query.

Example

We want to insert the details of employees in the database using prepared statements.

Let’s see how it can be performed.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create connection with database
$con = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($con->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$query = "INSERT INTO employee (fname, lname, email) VALUES (?, ?, ?)";

if($query_check = mysqli_prepare($con, $query)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($query_check, "sss", $fname, $lname, $email);

/* Set the parameters values and execute
the statement again to insert another row */
$fname = "Jack";
$lname = "Starc";
$email = "[email protected]";
mysqli_stmt_execute($query_check);

/* Set the parameters values and execute
the statement to insert a row */
$fname = "Rony";
$lname = "Hudsen";
$email = "[email protected]";
mysqli_stmt_execute($query_check);

echo "Employee details inserted successfully.";
} else{
echo "Error occured. Please try again." . mysqli_error($con);
}

// Close statement
mysqli_stmt_close($query_check);

// Close connection
mysqli_close($con);
?>

Explanation

• First, we initiate a connection with our database named as mydatabase.
• Next, we write a query with an anonymous positional placeholder(“?”).
• Then we check for syntax errors and query optimization and prepare templet using mysqli_prepare() function.
• Then after we use mysqli_stmt_bind_param() function.It binds the actual values with anonymous positional placeholders.

Note: here we use “sss” as a type definition. It defines string for all types of definition variables.
The bind variables can have the following four values as a type definition:

• b — binary value (such as image, PDF file, etc.)
• d — double value (a floating-point number)
• i — integer value (whole number)
• s — string value (text)

Read Also: [SOLUTION] Fatal Error: Uncaught Error: Call to Undefined Function Mysql_connect()

Conclusion

This is all about the prepared statement in PHP using MySQLi.

I hope you found this blog informative.

Thanks for reading 🙂