In this comprehensive tutorial, we will delve into the world of MYSQLI prepared statements in PHP, equipping you with the knowledge to perform various database operations like SELECT, UPDATE, DELETE, and more while maintaining the utmost security. Before we embark on this journey, let’s take a moment to introduce prepared statements.
Safeguarding sensitive user data is crucial for PHP forms security, and one key aspect to consider is implementing MYSQLi statements for PHP database security.
What Are MYSQLI Prepared Statements?
MYSQL is a widely-used relational database system, and MYSQLI stands as a robust PHP extension for seamless interaction with MYSQL databases. Prepared statements are queries that are precompiled and executed later, with the inclusion of data.
Why Are Prepared Statements Essential?
In a nutshell, prepared statements serve as a shield for websites against the nefarious SQL Injection attacks that can compromise their security. Additionally, prepared statements can offer improved performance compared to conventional queries, as cited by various sources. From my own experience, for straightforward queries, their speed might be comparable, but for recurring queries, prepared statements shine with remarkable efficiency. Another noteworthy advantage is their superior readability, making them easy to comprehend and manage.
Prerequisites for Using Prepared Statements in PHP
Before diving into the world of prepared statements in PHP, there are a few essential prerequisites you must have in place:
- A computer with PHP and MYSQL installed;
- A PHP script that has successfully connected to a database, with the database connection saved in the variable $mysqli, like so:
<?php
$mysqli = new mysqli( 'hostname', 'username', 'password', 'database' );In this tutorial, we will be working with a “user” table in our database, structured as follows:
id name email
1 Teodor [email protected]
2 Christ [email protected]
3 Austin [email protected]
4 Ayush [email protected]
Now, let’s proceed to explore how to use prepared statements in PHP.
Using MYSQLI Prepared Statements in PHP
Let’s explore prepared statements in PHP with MYSQLI, focusing on fundamental concepts and techniques for executing various query types, including SELECT and UPDATE.
To begin, we’ll outline the basic steps:
- Preparing the Query: We start by preparing a query and storing it in a variable named $stmt. This variable becomes a mysqli_stmt object. The query contains placeholders denoted by a question mark (?), indicating empty boxes to be filled in later. For instance:
$stmt = $mysqli -> prepare(‘SELECT * FROM users WHERE id = ?’);
- Binding Data: After preparing the query, we bind data to the placeholders, effectively filling in the empty boxes. For example, let’s bind the value 2 as the user’s ID:
$userId = 2;
$stmt -> bind_param('i', $userId);In the bind_param() method, the first parameter specifies the data types of the variables being bound. If you had multiple variables with different data types to bind, you could use a string like ‘iisi’ (integer, integer, string, integer).
It’s important to note that binding a literal value directly, as shown below, is not valid in PHP. The arguments for the bind_param function should be variables, with the exception of the first one:
$stmt -> bind_param('i', 2);Next, we execute the query
$stmt -> execute();- Executing the Query: After binding the data, we execute the query using the following code:
$stmt->execute();The subsequent steps in the process will vary depending on the type of query you intend to perform. In the following sections, we’ll explore examples and techniques for different types of MYSQLI prepared statements in PHP.
Retrieving a Single Row Using SELECT Statement
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE id = ?');
$userId = 1; // or $_GET['userId'];
$stmt -> bind_param('i', $userId);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
$stmt -> fetch();
echo $name; // Teodor
echo $email; // [email protected]echo $email; // [email protected]
- store_result() stores the result.
- bind_result() binds the values to variables.
- fetch() fetches results to variables.
Initially, this might seem challenging, especially for beginners. However, as you progress through the subsequent steps, you’ll grasp the concept more clearly. Keep in mind that the fetch() function stores the result of the current row into the variables specified in bind_result(). By default, the initial current row is the first one in the result set. When we invoke fetch() once, the current row becomes the second one in the results. However, it’s worth noting that in this particular query, we’re dealing with only a single row.
Retrieving Multiple Rows Using the SELECT Statement
$stmt = $mysqli -> prepare('SELECT name, email FROM users');
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
while ($stmt -> fetch()) {
echo $name;
echo $email;
}The bind_param() function is unnecessary when no variables need to be passed. The following code retrieves all users’ data and displays their names and emails.
The fetch() function returns true when it succeeds and false when it fails or when no rows are found. Therefore, we can directly employ it as the condition for the while loop.
Each time fetch() is invoked, the data from the current row is stored in the $name and $email variables, and the cursor advances to the next row. So, when fetch is called again, it retrieves the data from the subsequent row.
Obtaining the Count of Selected Rows with SELECT Statement
$stmt = $mysqli -> prepare('SELECT name, email FROM users');
$stmt -> execute();
$stmt -> store_result();
// 4
echo $stmt -> num_rows;Keep in mind that you should call store_result() before accessing the num_rows property.
Retrieving Results from a SELECT Query
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE id > ?');
$greaterThan = 1;
$stmt -> bind_param('i', $greaterThan);
$stmt -> execute();
$result = $stmt -> get_result();Now, the $result variable is equivalent to using $mysqli->query(…). You can employ it as shown below to work with the results.
while ($row = $result -> fetch_assoc()) {
echo $row['name'];
echo $row['email'];
}Exploring Wildcards in SELECT Statements
In the realm of MYSQL, wildcards play a pivotal role in pattern matching. They are instrumental in searching for specific patterns within your data.
<?php
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE name LIKE ?');
$like = 'a%';
$stmt -> bind_param('s', $like);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
while ($stmt -> fetch()) {
echo $name;
echo $email;
}In this instance, we will retrieve all users whose names commence with the letter “a,” which includes “Austin” and “Ayush.”
Selecting Data Based on an Array of IDs
When working with prepared statements, retrieving data based on an array of IDs can be a challenging task. It involves dynamically incorporating question marks into the query to accommodate the varying number of IDs in the array.
// array of user IDs
$userIdArray = [1,2,3,4];
// number of question marks
$questionMarksCount = count($userIdArray);
// create a array with question marks
$questionMarks = array_fill(0, $questionMarksCount, '?');
// join them with ,
$questionMarks = implode(',', $questionMarks);
// data types for bind param
$dataTypes = str_repeat('i', $questionMarksCount);
$stmt = $mysqli -> prepare("SELECT name, email FROM users WHERE id IN ($questionMarks)");
$stmt -> bind_param($dataTypes, ...$userIdArray);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
while ($stmt -> fetch()) {
echo $name;
echo $email;
}Utilizing LIMIT and OFFSET in SELECT Statements
$stmt = $mysqli -> prepare("SELECT name, email FROM users LIMIT ? OFFSET ?");
// limit of rows
$limit = 2;
// skip n rows
$offset = 1;
$stmt -> bind_param('ii', $limit, $offset);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
while ($stmt -> fetch()) {
echo $name;
echo $email;
}Using the BETWEEN Clause in SELECT Statements
In this example, we employ the BETWEEN clause in a prepared SELECT statement to retrieve data from the “users” table. We specify a range of IDs between $betweenStart and $betweenEnd.
$stmt = $mysqli -> prepare("SELECT name, email FROM users WHERE id BETWEEN ? AND ?");
$betweenStart = 2;
$betweenEnd = 4;
$stmt -> bind_param('ii', $betweenStart, $betweenEnd);
$stmt -> execute();
$stmt -> store_result();
$stmt -> bind_result($name, $email);
while ($stmt -> fetch()) {
echo $name;
echo $email;
}Inserting a Single Row Using Prepared Statements
In this example, we demonstrate how to insert a single row of data into the “users” table using prepared statements.
$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');
$name = 'Akhil';
$email = '[email protected]';
$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();Retrieving the Inserted ID After an INSERT Operation
In scenarios where you have an auto-incremental column to store user IDs, it’s often crucial to determine the ID of the user you’ve just added to the database. You can achieve this by utilizing the $stmt->insert_id property.
Here’s an example:
$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');
$name = 'Akhil';
$email = '[email protected]';
$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();
echo 'Your account id is ' . $stmt -> insert_id;Bulk INSERT Operations Using Prepared Statements
Performing recursive insertions with prepared statements is a robust technique for adding multiple rows of data to a database efficiently. With this approach, you prepare a single statement and utilize it to insert multiple rows in a streamlined manner.
Here’s an example illustrating this technique:
$newUsers = [
[ 'sulliops', '[email protected]' ],
[ 'infinity', '[email protected]' ],
[ 'aivarasco', '[email protected]' ]
];
$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');
foreach ($newUsers as $user) {
$name = $user[0];
$email = $user[1];
$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();
echo "{$name}'s account id is {$stmt -> insert_id}";
}As you observe, with each iteration of the loop, the $stmt->insert_id property is updated to reflect the ID of the newly inserted row, making it straightforward to track the IDs for all the added records.
Updating User Email Using Prepared Statement
$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE id = ? LIMIT 1');
$email = '[email protected]';
$id = 2;
$stmt -> bind_param('si', $email, $id);
$stmt -> execute();Determining the Number of Affected Rows After an UPDATE
Sometimes you will need to know how many rows are affected by our UPDATE query.
$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE name = ? LIMIT 1');
$email = '[email protected]';
$name = 'teodor';
$stmt -> bind_param('ss', $email, $name);
$stmt -> execute();
// 1
echo $stmt -> affected_rows;Deleting User by ID
$stmt = $mysqli -> prepare('DELETE FROM users WHERE id = ?');
$userId = 4;
$stmt -> bind_param('i', $userId);
$stmt -> execute();
// number of deleted rows
echo $stmt -> affected_rows;Handling MYSQLI Prepared Statement Errors
Dealing with errors in MYSQLI prepared statements is a crucial aspect of database interaction. Here are some valuable tips for debugging and handling errors:
Detecting Preparation Failures
Sometimes, the $mysqli->prepare() function fails due to an incorrect query. You can identify this by checking if $stmt is a boolean (false) instead of an object.
How to Identify Preparation Failures:
$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
$id = 1;
$stmt -> bind_param('i', $id);If you encounter a PHP error message such as “Call to a member function bind_param() on boolean” while attempting to use methods on the $stmt variable, it indicates a failure in the preparation of the statement. In the event of a preparation failure, $mysqli->prepare() returns false, making $stmt a boolean value rather than an object. You can leverage $mysqli->error to pinpoint the specific error within your query.
$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
echo $mysqli -> error;Handling Execution Failures
Execution failures typically do not trigger error messages. Consequently, it is essential to incorporate a conditional check to verify the success of the execution. If the execution was not successful, you can rely on $stmt->error to reveal the nature of the error.
Here’s an example:
$stmt = $mysqli -> prepare('INSERT INTO stmt_users (name) VALUES (?)');
$name = 'User';
$stmt -> bind_param('i', $name);
if (! $stmt -> execute()) {
echo $stmt -> error;
}In the context of our sample table, the encountered error message is “Field ’email’ does not have a default value.”
Conclusion
The objective of this tutorial was to comprehensively explore the various methods of implementing prepared statements. We delved into their usage in SELECT, INSERT, UPDATE, and DELETE operations. It is my aspiration that this article has provided a thorough understanding of MYSQLI prepared statements for those seeking to enhance their knowledge in this area.