All About MYSQLI Prepared Statements in PHP

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.

What are MYSQLI 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.

Why are Prepared Statements Important?

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.

Prerequisites

Before you start you will need these things.

How To Use Prepared Statements in PHP

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).

1. SELECT - Selecting One Row


$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.

2. SELECT - Selecting Multiple Rows


$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:

3. SELECT - Getting Number of Selected Rows


$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.

4. SELECT - Get Results


$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'];
}


5. SELECT - With Wildcards

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)

6. SELECT - With An Array of IDs

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;
}


7. SELECT - LIMIT and OFFSET


$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;
}


8. SELECT - BETWEEN


$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;
}


9. INSERT - One Row


$stmt = $mysqli -> prepare('INSERT INTO users (name, email) VALUES (?,?)');

$name = 'Akhil';
$email = '[email protected]';

$stmt -> bind_param('ss', $name, $email);
$stmt -> execute();


10. INSERT - Getting Insert ID

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;


11. INSERT - Multiple Rows (Recursive)

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.

12. UPDATE


$stmt = $mysqli -> prepare('UPDATE users SET email = ? WHERE id = ? LIMIT 1');
	
$email = '[email protected]';
$id = 2;

$stmt -> bind_param('si', $email, $id);
$stmt -> execute();


13. UPDATE - Get Affected Rows

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;


14. DELETE


$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 Errors

It's always very good to know how to debug MYSQLI prepared statements. Here are some tips.

1. When Preparation Fails

Sometimes the $mysqli -> prepare() function fails because of wrong query.

How To Detect?


$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;


2. When Execution Fails

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".

Conclusion

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!

Tagged: PHP MYSQLi
You can connect with me on Twitter or Linkedin.
Latest on My Blog
PHP Beginner's Tutorial
Beginner's PHP Tutorial
Image for Laravel High CPU Usage Because of File-based Session Storage
Laravel High CPU Usage Because of File-based Session Storage
Image for Resizing Droplets: A Personal Experience
Resizing Droplets: A Personal Experience
Image for Moving our CDN (10+ GB images) to a new server
Moving our CDN (10+ GB images) to a new server
Image for Disqus, the dark commenting system
Disqus, the dark commenting system
Image for Creating a Real-Time Chat App with PHP and Node.js
Creating a Real-Time Chat App with PHP and Node.js
Related Articles
4001