How to write SQL queries using MySQLi in PHP

MySQLi is the “MySQL Improved Extension” for PHP. You might have seen simple examples of it being used like this:

$sql = "SELECT * FROM persons";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons in the database\n";

This particular example works. In my case, the output is “There are: 2 persons in the database”. Many queries are not so simple, however. Suppose you wanted info about a particular person named “Smith”. The following code will work (assuming $name has been set in the code somewhere above):

$name = "Smith";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";

The output is “There are: 1 persons named Smith”.

Because it works, it may lull you into a false sense of security; you might think that’s all there is to it. And it may work for quite a while before you run into a problem. The problem is that some names contain special characters that will break the code above. One example is the name O’Hara. This is what happens when “Smith” is replaced by “O’Hara” in the above code:

$name = "O'Hara";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";

When I run this code, I see an error:

PHP Fatal error:  Uncaught TypeError: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/fullstackdev/tests/single-select-sql-apostrophe.php:29
Stack trace:...

This error might be confusing because the error is thrown from the line with mysqli_num_rows. That’s because the call to mysqli_query returned false, a sign that there was an error. Let me call mysqli_error before trying to use the $result, and print out the error, if there was one:

$name = "O'Hara";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
$error = mysqli_error($conn);
if ($error) {
        echo "$error\n";
} else {
        echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";
}

Now, the output looks like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hara'' at line 1

This error is more helpful than the previous one, but if you’re new to SQL, it might still be confusing. The problem is that there’s an apostrophe in the name O’Hara, but the apostrophe is a special character with special meaning in SQL. It is used to signify the beginning or ending of a string. If I print out my SQL statement from the above code, it looks like this:

SELECT * FROM persons WHERE lastname = 'O'Hara'

That’s three apostrophes. There’s an unmatched apostrophe, and that means MySQL was expecting more characters; it doesn’t know what to do with this statement.

The way to handle this type of query is to “escape” the apostrophe in the SQL by putting a backslash character in front of it, like this:

SELECT * FROM persons WHERE lastname = 'O\'Hara'

That backslash notifies MySQL to treat the next character as part of the string, not as a special character delimiting a string.

Of course you don’t want to have to write special code to replace all instances of apostrophe in your string with backslash followed by apostrophe. There’s already a special PHP method which will do this for you. It’s called mysqli_real_escape_string.

Let’s use this in our code above:

$name = "O'Hara";
...
$esc_name = mysqli_real_escape_string($conn, $name);
$sql = "SELECT * FROM persons WHERE lastname = '$esc_name'";
echo "$sql\n";
$result = mysqli_query($conn, $sql);
$error = mysqli_error($conn);
if ($error) {
        echo "$error\n";
} else {
        echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";
}

The output looks like this:

SELECT * FROM persons WHERE lastname = 'O\'Hara'
There are: 1 persons named O'Hara

As you can see, the SQL which I printed out has a properly “escaped” apostrophe in it. The function mysqli_real_escape_string should always be used to escape strings in cases like this.

Once you get to this point, you might think everything is hunky-dory and start writing all your code this way. However, it’s generally considered a really bad idea to write code like this, because it’s not safe against a SQL injection attack. To prevent this type of black-hat hacking on your website and database, you should always use prepared statements. It’s slightly more labor intensive, but it’s safe:

$sql = "SELECT * FROM persons WHERE lastname = ?";
$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, 's', $name);
mysqli_stmt_bind_result($stmt, $id, $firstname, $lastname);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);
echo "Person $firstname $lastname was found\n";

The output here is Person Karen O'Hara was found.

As a final note, while MySQLi is fine to work with, IMHO, but you’ll also want to become familiar with PDO because it’s also quite popular (and different).

PS if you want to play around with the sample code above, you’ll need a MySQL database installed. Here are my dead simple database commands:

CREATE DATABASE testing;
USE testing;
CREATE TABLE persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstname varchar(128),
    lastname varchar(128)
);
INSERT INTO persons (firstname, lastname) VALUES ("John", "Smith");
INSERT INTO persons (firstname, lastname) VALUES ("Karen", "O'Hara");

Subscribe to get new posts