In this tutorial, we will learn how to perform queries like SELECT, UPDATE, DELETE, etc. with different conditions with MYSQLI prepared statements in PHP. Before we start I'll briefly introduce you prepared statements.
MYSQL is a popular relational database system. MYSQLI is a powerful PHP extension to connect with MYSQL. Prepared statements as queries that are previously prepared and executed later with data.
Simply, prepared statements protect websites from SQL Injection which can be used to attack a website. Also, prepared statements can be faster than normal queries according to some sources (But, in my experience, they are almost the same when performing simple queries. But, for recurring queries prepared statements are super fast than normal queries). The best thing about prepared statements is readability. They can easily be read, understood and managed.
Before you start you will need these things.
<?php
$mysqli = new mysqli( 'hostname', 'username', 'password', 'database' );
| id | name | |
|---|---|---|
| 1 | Teodor | [email protected] |
| 2 | Christ | [email protected] |
| 3 | Austin | [email protected] |
| 4 | Ayush | [email protected] |
Let's see how to perform a prepared statement in PHP, using MYSQLI. This is the basic concept. In various queries (SELECT, UPDATE etc.) we will use different ways (and tricks).
$stmt = $mysqli -> prepare('SELECT * FROM users WHERE id = ?');
Here we store the statement in $stmt. This is a mysqli_stmt object. In the next steps, we will call methods of this class to perform actions.
Note that ? (question mark) in the query is used as a placeholder. We say to MYSQL that this is an empty box which we will fill in the future.
$userId = 2;
$stmt -> bind_param('i', $userId);
Here we will bind 2 as the id of the user. In the bind_param() method, the first parameter indicates the data types of each variable. For instance, if you had three variables (empty boxes) to bind which are an integer, integer, string, integer respectively, you may use 'iisi'.
$stmt -> bind_param('i', 2);
$stmt -> execute();
After this step, the procedure differs according to the query you will perform. Let's see some examples.
$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]
At first, this is tricky if you are a beginner. But, you will understand when you do other steps. Just remember that fetch() saves the result of the current row in the variables in bind_result(). By default, the current row is the first one in the result set. When we call fetch() one time, the current row is the second one in the results. However, we only have one row in this query.
$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;
}
bind_param() function is not needed when you don't have any variable to pass. This will select all the users and echo out the name and email of all the users.
Special Note:
$stmt = $mysqli -> prepare('SELECT name, email FROM users');
$stmt -> execute();
$stmt -> store_result();
// 4
echo $stmt -> num_rows;
One thing to remember, store_result() should be called before using the num_rows property.
$stmt = $mysqli -> prepare('SELECT name, email FROM users WHERE id > ?');
$greaterThan = 1;
$stmt -> bind_param('i', $greaterThan);
$stmt -> execute();
$result = $stmt -> get_result();
Now $result is the same as doing $mysqli -> query(...). You can use something like following to use results.
while ($row = $result -> fetch_assoc()) {
echo $row['name'];
echo $row['email'];
}
Wildcards are used to match patterns in MYSQL.
<?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 example, we will select all the users whose name starts from the letter a. (austin and ayush)
This is a pretty hard thing to do when using with prepared statements. We will need to dynamically add the question marks into the query.
// 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;
}
$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;
}
$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;
}
$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');
$name = 'Akhil';
$email = '[email protected]';
$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();
If you had an auto incremental column for saving the ID, in many cases we need to know what's the ID of the user who we have just inserted into the database. The $stmt -> insert_id property is used in this case.
$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;
Recursive insertions are very powerful when done with prepared statements. We prepare one statement and use it to insert multiple rows.
$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}";
}
You will see that $stmt -> insert_id updates each time you insert a new row.
$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE id = ? LIMIT 1');
$email = '[email protected]';
$id = 2;
$stmt -> bind_param('si', $email, $id);
$stmt -> execute();
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;
$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;
It's always very good to know how to debug MYSQLI prepared statements. Here are some tips.
Sometimes the $mysqli -> prepare() function fails because of wrong query.
$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
$id = 1;
$stmt -> bind_param('i', $id);
If you see an error in PHP like, "Call to a member function bind_param() on boolean" when calling methods of $stmt, then the preparation has failed. When $mysqil -> prepare() fails it returns false. Therefore $stmt is a boolean, not an object. Now we can use $mysqli -> error to find the error in the query.
$stmt = $mysqli -> prepare('SELECT * FROM no_table WHERE id = ?');
echo $mysqli -> error;
Execution failures normally do not throw errors. Therefore, you should add a condition to check if the execution was successful. If it wasn't, $stmt -> error will show you what's the error.
$stmt = $mysqli -> prepare('INSERT INTO stmt_users (name) VALUES (?)');
$name = 'User';
$stmt -> bind_param('i', $name);
if (! $stmt -> execute()) {
echo $stmt -> error;
}
For our example table, the error is "Field 'email' doesn't have a default value".
My target was to cover all the ways to perform prepared statements in this tutorial. We discussed how to use prepared statements for SELECT, INSERT, UPDATE and DELETE. So, I hope I covered everything. If you are willing to learn the best way to perform MYSQLI prepared statements, this article is for you. I'm waiting to see your feedback in the comments section. Thank you for reading!
