Mysqli tutorial (how to use it properly)

  1. Connection
  2. Object and procedural interface
  3. Executing queries with PHP variables. Prepared statements
  4. Prepared SELECT queries
  5. Fetching query results
  6. Executing queries without variables
  7. Error handling
  8. Number of rows returned by SELECT statement
  9. Number of rows affected by data modification queries
  10. Comments (20)

Connection

The importance of proper connection code is often overlooked, and it's often being diminished to just a single line. Whereas connecting the right way can solve a multitude of problems, from weird characters to cryptic error messages.

Given your code is the usual procedural PHP, here is a simple mysqli connection code to be included in your scripts:

$host     = '127.0.0.1';
$db       = 'test';
$user     = 'root';
$password = '';
$port     = 3306;
$charset  = 'utf8mb4';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli($host, $user, $password, $db, $port);
$db->set_charset($charset);
$db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

The full explanation can be found in the dedicated article How to connect using mysqli (as well as many useful hints), but just a small citation to highlight most important parts:

  • setting the proper character set for the connection will eliminate the whole class of errors, such as weird characters/question marks instead of your data, empty json_encode() output, problems with storing emojis, etc.
  • setting the proper error reporting mode will eliminate the cryptic error messages like mysqli_fetch_assoc() expects parameter... / Call to a member function bind_param()..., giving you the actual error message from MySQL instead.

Object and procedural interface

One small but important note: mysqli bears one unique feature: all its functions can be accessed using both object and procedural syntax. Means each function can be called either as a function or as an object's method:

mysqli_query($mysqli, $query); // procedural syntax
$mysqli->query($query); // object syntax

The only difference is that for the object syntax we take the function's parameter ($mysqli for example), add the object operator (->) and then call the actual method name, cutting off the redundant "mysqli_" part. Note that you don't need to know OOP in order to use the object syntax: it's just the way the same function call is written.

Both methods are totally interchangeable, the difference is only in the style. You can use either. Both styles can be even mixed in the code but that would be a very bad practice, style-wise.

Given the object style is more concise, without constant repetitions (i.e. mysqli_stmt_get_result($stmt) vs. $stmt->get_result()) I highly recommend it. And so it will be used in this article.

Executing queries with PHP variables. Prepared statements

One of the main reasons why old mysql ext was removed from PHP is the fact it didn't support prepared statements, so PHP variables inevitably had to be added right into SQL. But there is absolutely no point in continuing this dangerous practice with mysqli. Means you ought to use prepared statements, which makes the transition a complete rewrite of the every database interaction.

Why should we use prepared statements? For such a simple reason that when the data is added directly to the SQL statement, it may corrupt the query. With consequences ranging from a syntax error to SQL injection. Unlike infamous "escaping" approach that works only for strings and can be often overlooked, forgotten or misused, prepared statements let us formulate a simple but bullet-proof three-step instruction:

  1. Prepare your sql query, adding ? marks where a variable would have been used
  2. Bind these variables to the previously prepared statement, setting the type for each
  3. Execute the statement

Here is a simple example for the INSERT query:

$stmt = $db->prepare("INSERT INTO users (email, password) VALUES (?,?)");
$stmt->bind_param("ss", $email, $password_hash);
$stmt->execute();

As you can see, nothing complex or confusing, just the three steps described above.

Let's take a closer look at the prepared statement, using UPDATE query for example

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";
$stmt= $conn->prepare($sql);
$stmt->bind_param("sssi", $name, $email, $password, $id);
$stmt->execute();

What is going on here?

$sql = "UPDATE users SET name=?, email=?, password=? WHERE id=?";

Like it was said above, first we are writing SQL, where all variables are substituted with question marks.

IMPORTANT! there should be no quotes around question marks, you are adding placeholders, not strings.

$stmt= $conn->prepare($sql);

Then, the query gets prepared. The idea is very smart. To avoid even a possibility of SQL injection or a syntax error caused by the input data, the query and the data are sent to database server separately. So it goes on here: with prepare() we are sending the query to database server ahead. A special variable contains a mysqli statement is created as a result. We would use this variable from now on.

$stmt->bind_param("sssi", $name, $email, $password, $id);

Then variables must be bound to the statement. The call consists of two parts - the string with types and the list of variables. With mysqli, you have to designate the type for each bound variable. It is represented by a single letter in the first parameter. The number of letters should be always equal to the number of variables. The possible types are

So you can tell now that "sssi" means "there would be 3 variables of string type and the last one of integer type". And then, naturally, four variables obediently follow.

a quick tip: MySQL will gladly accept all variables as strings, so don't go nuts finding the correct type for a certain variable, simply using "s" for all.

$stmt->execute();

Then the query finally gets executed. Means variables get sent to database server and the query is actually executed.

IMPORTANT! You shouldn't check the execution result. Given you have the proper connection code mentioned above, in case of error mysqli will raise an error automatically.

Just for sake of completeness, here is the DELETE query example, but I hope you've got the idea already:

$sql = "DELETE FROM users WHERE id=?";
$stmt= $conn->prepare($sql);
$stmt->bind_param("s", $id);
$stmt->execute();

Bind in execute

Note that starting from PHP 8.1 the bind_param part can be omitted and variables can be sent directly to execute (in the form of array):

$stmt = $db->prepare("INSERT INTO users (email, password) VALUES (?,?)");
$stmt->execute([$email, $password_hash]);

In this case all variables are bound as strings.

Mysqli_execute_query

Finally, starting from version 8.2, PHP got an excellent function, execute_query(), which lets to execute a prepared statement in one go:

$db->execute_query("INSERT INTO users (email, password) VALUES (?,?)", [$email, $password_hash]);

In this case all variables are also bound as strings.

It case your PHP version is less than 8.2, here you can learn how to add a temporary substitution for this indispensable function.

Prepared SELECT queries

SELECT queries executed exactly the same way as described above, except for one small addition: we need to get the query result so it can be used to fetch the returned data:

$stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();

here, the get_result() method returns an instance of the special mysqli_result class that allows us to fetch the returned rows as arrays or objects.

Note that if it says that get_result() is not found, you will need to tick some checkbox labeled php_mysqlnd in the PHP configuration section in your ISPMAnager.

Note that execute_query() method already returns the mysqli_result instance, so you don't have to call it explicitly (which makes it even more useful!):

$row = $db->execute_query("SELECT * FROM user WHERE email = ?", [$email])->fetch_assoc();

Fetching query results

To get the rows returned by SELECT query we are using a special variable being instance of mysqli_result class. All fetching functions work with this variable.

The generic method for either fetching a single row or multiple rows would be using one of mydsli fetch functions:

Getting a single row

If we need to fetch just a single row, then we should call just one of the above functions. For example:

$stmt = $db->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo $row['username'];

or, using execute_query():

$row = $db->execute_query("SELECT * FROM user WHERE email = ?", [$email])->fetch_assoc();
echo $row['username'];

Getting multiple rows in a loop

That's an interesting part, because to get multiple rows we will use the same function we were using to get a single row! All thanks to one simple detail: when the row is fetched, the internal pointer in the result set is moved one position further, so the next call to the same function will return the next row - and so on, until there are no rows in the resultset, when the fetch function will return null. So we can use the while loop to get all rows:

$users = [];
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT 0, 10";
$result = $db->query($sql);
while ($row = $result->fetch_assoc()) {
    $users[] = $row;
}

Here we are getting all the returned rows into array $users.

A tip: mysqli has a handy function that returns all the rows into array in a single call: mysqli_fetch_all(). By default it uses fetch_row() method with numeric indices, so if you want array elements become associative arrays, use the MYSQLI_ASSOC constant as a parameter:

$sql = "SELECT * FROM categories";
$result = $db->query($sql);
$data = $result->fetch_all(MYSQLI_ASSOC);

Using query results

Please note that echoing the query results right away is frowned upon nowadays. You are supposed to collect all the necessary data first, and only then start with output. Which is going to be just a regular foreach, unrelated to any database stuff:

// get the data
$data = $result->fetch_all(MYSQLI_ASSOC);
// close the PHP tag to avoid all the hassle with echoing HTML
?>
<head>
<body>
<?php if ($data): ?>
  <table>
    <tr><th>Name</th><th>Score</th></tr>
<?php foreach ($data as $row): ?>
      <tr>
        <td><?= htmlspecialchars($row['name'])</td>
        <td><?= htmlspecialchars($row['score'])</td>
      </tr>
<?php endforeach ?>
  </table>
<?php elseif: ?>
  No data!
<?php endif ?>

Executing queries without variables

When a query is entirely hardcoded, i.e. no PHP variables are used in it, one can use the query() function. It already returns the mysqli_result object and any fetch function can be chained to it:

$menu = $db->query("SELECT * FROM menu")->fetch_all();
$count = $db->query("SELECT count(*) FROM users")->fetch_row()[0];

Error handling

Error handling is the most important yet somewhat surprising part. Despite what numerous articles and examples say, as a rule, you shouldn't write any error handling code at all. It sounds very unusual but that's exactly how things must be done. Most of time all you need to do is just report the error. And mysqli/PHP already can do it for you, no help required. Therefore, you shouldn't write any code that verifies the query execution result - in case of error mysqli will report it automatically, thanks to the mysqli_report() function call mentioned above. Thus, all database interaction errors will be processed uniformly, exactly the same way as all other PHP errors, which is extremely convenient, allowing to process all errors in a single place, as opposed to writing the dedicated handling code for the every single query. Again, the full explanation of this principle can be found in another article, dedicated to PHP error reporting.

On a rare occasion when you really need to handle the error, that is, to perform some action in case of error instead of just reporting it, then wrap your query(es) in a try..catch.

Number of rows returned by SELECT statement

There is no use for the familiar mysqli_num_rows() function. If you think of it, you can always use the data itself, to see whether your query returned any rows:

$user = $result->fetch_assoc();
if ($user) {
    // found!
}

the same goes for the multiple rows, thanks to a handy function mysqli_fetch_all() that can get you an array of all selected rows in one go.

And of course you should never select more rows then can be processed on a single page. This applies either to the query that selects the actual rows and - especially - to the query that is used specifically to select the number of rows. In the latter case, a SELECT count(*) must be used instead.

Number of rows affected by data modification queries

Unlike the above, the number of rows affected by the INSERT, UPDATE and DELETE query could be quite useful. What is interesting, mysqli has not one but two facilities that can return such a number.

One of them is familiar affected_rows property:

$db->query("DELETE FROM users");
echo $db->affected_rows();

But one must remember that MySQL will return here only the number of rows that were actually changed. If a row were found but the new data is the same as old, MySQL won't count such a row towards that number. Luckily, mysqli has an unique mysqli_info() function which returns separate results for the the number of rows found and affected by a DML query. In any other driver, including PDO, you can have either one or another but not both at once. Although it returns a string, a simple code could be written to parse its result into a neat array.

So, a bit more readable version of this code can be made into a simple function like this

function mysqli_info_array($mysqli) {
    preg_match_all('~: (\d+)~', $mysqli->info, $matches); 
    return [
        'matched' => (int)$matches[1][0],
        'changed' => (int)$matches[1][1],
        'warnings' => (int)$matches[1][2],
    ];
}

and after that one will be able check the number of found and affected rows separately. For example, given we are updating just a single row,

$db->query("update test set i=2");
$info = mysqli_info_array($db);
if ($info['matched'] === 0) {
    $result = "No rows were matched";
} elseif($info['changed'] === 0) {
    $result = "Rows were found but not updated";
} else {
    $result = "Rows were found and updated";
}

Related articles: