A prepared statement is excelled feature provided by PHP-MySQL to increase the speed and execution of the query. It is one of the best ways to execute a similar query or a statement with high efficiency. However, it seems a little bit tricky for PHP-MySQL beginners. However, it’s quite simple and only requires a couple of additional steps than the MySQLi method.
In this tutorial, we will learn what is prepared statement, why we need it, and the program to insert the data into the table using prepared statements. Let’s get started!
Also read: PHP MySQL- Insert Multiple Records
What is a Prepared Statement?
A prepared statement, also known as a parameterized statement, is a feature in database management systems (DBMS) that allows users to execute the same or similar database statements again with high efficiency. The prepared statement, which is commonly used with SQL statements like queries and updates, is a template into which specific constant values are inserted throughout each run.
Because parameter values that are transferred later using a separate protocol do not need to be correctly escaped, prepared statements are highly beneficial against SQL injections. SQL injection is impossible if the original statement template is not generated from external input.
How Prepared Statements work?
Prepared statements work in the following flow-
- First, an SQL statement template is created and sent to the database without any values. Note that, we do not send the values in the SQL statement early. We leave them unspecified which are denoted by the ? sign. For example, INSERT INTO table VALUES(???). Here, we will send three values to the table which are kept blank.
- The database parses, compiles, and optimises the SQL statement template and stores the result without executing it.
- Finally, the application binds the values to the parameters that we kept unspecified and the database executes the statement. We can execute the statement as many times as we want with different values.
Why We Need Prepared Statements?
You may have come across the term “SQL injection”. Prepared statements provide excellent security against SQL injection attacks.
Also, as compared to regular SQL statements, prepared statements are faster, because it only executes the query multiple times whereas it prepares it only once. On the other hand, the regular SQL statements will prepare the query every time and execute it.
PHP MySQL Prepared Statements Examples
Here, we will see how to use prepared statements in PHP program by three methods- MySQLi procedural method, MySQLi object-oriented method and using PDO. But before that, we have to create a table to insert values in it. For that, we will use the table of the following description.
Prepared Statements using MySQLi (Procedural)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$query = "INSERT INTO emp(name,city) VALUES(?, ?)";
$insertStatement = mysqli_prepare($conn, $query);
mysqli_stmt_bind_param($insertStatement, "ss", $name, $city);
$name = "John";
$city = "New York";
if (mysqli_stmt_execute($insertStatement)) {
echo "New record created successfully<br>";
}
$name = "Mary";
$city = "Boston";
if (mysqli_stmt_execute($insertStatement)) {
echo "New record created successfully<br>";
}
mysqli_close($conn);
?>Code language: PHP (php)Let’s understand how to program runs step by step.
First, we create a valid SQL query statement and assign it to the variable. Note that, we do not specify the values inside the VALUES parenthesis. Instead, we insert a question mark (?) where we want to put the actual values.
Second, we prepare the SQL statement. This statement is executed only once no matter how many times you are going to execute the insert query later in the program.
Third, we bind the parameter/variables to the prepared statement. Here, we specify the types of values that we want to insert into the table along with the variable names that hold the actual values. In the above program, we want to insert string values, that’s why we specified “ss” as a variable type.
There are four types available that you can specify in the prepared statements.
- s = variable of type string
- i = variable of type integer
- d = variable of type double
- b = variable of type blob and will be sent in packets
Now, the only step remaining is to execute our actual query. The mysqli_stmt_execute() function executes our query and inserts the values into the MySQL table.
If we execute the above PHP program, new data will get inserted into the table. Let’s check the table from CLI if the data is really inserted in it or not.
SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)
Prepared Statements using MySQLi (Object Oriented)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . mysqli_connect_error());
}
$stmt = $conn->prepare("INSERT INTO emp(name,city) VALUES(?, ?)");
$stmt->bind_param("ss", $name, $city);
$name = "Saitama";
$city = "Tokyo";
$stmt->execute();
$name = "Kanagawa";
$city = "Yokohama";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
?>Code language: PHP (php)SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)
Prepared Statements using MySQLi (PDO)
<!-- insert values using prepared statement in PDO -->
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO emp (name, city) VALUES (:name, :city)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':city', $city);
$name = "Smith";
$city = "London";
$stmt->execute();
$name = "August";
$city = "Paris";
$stmt->execute();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>Code language: PHP (php)SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)
Conclusion
In this tutorial, we learned what is a prepared statement, why we need it and how it works. We also went through some examples to insert the values into the MySQL table using a prepared statement. This is an introductory tutorial for beginners and we will be covering in-detail guides for the PHP MySQL prepared statements in the future. So, stay tuned with mysqlcode.com.