How to connect properly using mysqli

  1. Procedural code
  2. Object-oriented code
  3. Credentials explained
  4. Functions explained
  5. Handling connection errors
  6. Accessing the newly created connection
  7. Making prepared queries less verbose
  8. Include file
  9. Comments (3)

Having answered thousands questions on Stack Overflow, I was able to determine the most common problems PHP developers stuck into when working with mysqli. So I decided to write this tutorial emphasizing on the following matters:

Procedural code

Mysqli supports both procedural and object-oriented syntax, and the difference is only a matter of style. OOP syntax is much cleaner and more concise and therefore generally recommended. However, if you cannot wrap your head at the moment, you can use the procedural approach as well:

$host '127.0.0.1';
$db   'test';
$user 'root';
$pass '';
$charset 'utf8mb4';

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
try {
    
$mysqli mysqli_connect($host$user$pass$db);
    
mysqli_set_charset($mysqli$charset);
} catch (\
mysqli_sql_exception $e) {
     throw new \
mysqli_sql_exception($e->getMessage(), $e->getCode());
}
unset(
$host$db$user$pass$charset); // we don't need them anymore

Object-oriented code

$host '127.0.0.1';
$db   'test';
$user 'root';
$pass '';
$charset 'utf8mb4';

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
try {
    
$mysqli = new mysqli($host$user$pass$db);
    
$mysqli->set_charset($charset);
} catch (\
mysqli_sql_exception $e) {
     throw new \
mysqli_sql_exception($e->getMessage(), $e->getCode());
}
unset(
$host$db$user$pass$charset); // we don't need them anymore

If your application code is mostly procedural, it would be a good idea to put the above code in a separate file called mysqli.php and then just include in all scripts that require a database connection.

In case your code is OOP, you most likely would put it in the constructor of your own database wrapper class. In this case you may want to check whether your class has any of the common mistakes, just in case.

Credentials explained

First of all we are defining variables that contain connection credentials. This set is familiar to anyone who were using the old mysql_connect() function, save for $charset may be, which was rarely used (although it should have been).

Functions explained

Beside mysqli_connect() we are using two additional functions here:

Handling connection errors

An uncaught exception is converted to a PHP fatal error. It is not a problem by itself, errors are for the good, and we desperately need then get the idea what's wrong with our database. But such a converted error contains a stack trace added to the error message, which in case of mysqli connection error would include the constructor parameters which happen to be the database credentials. Again, it shouldn't be a problem, as on a live site displaying errors should be always turned off anyway, but we are humans and we make mistakes. So, to avoid even a chance to reveal the credentials, we are catching the Exception and immediately throwing a brand new one with the same error information, effectively erasing the stack trace. Usually it's a bad move but in this case it's considered a good trade-off between security and convenience.

Just keep in mind that if your connection code is wrapped in a function, this function's parameters will be shown in the stack trace as well. So, to avoid the credentials leak in this place, either send the credentials into this function in the form of an array or an object, or fetch them inside the function.

Accessing the newly created connection

There is one thing that makes mysqli a bit more complex to use than old mysql_connect related stuff. Although one was able to call mysql_query() anywhere in the code, without taking care of the connection, which was magically supplied by PHP, with mysqli one should always make sure that once created mysqli instance is available in each part of their script. In a nutshell, it's all about accessing a $mysqli variable inside functions and object's methods.

So, to use mysqli in the global scope, just create a PHP file with the code above, and then include it in the every PHP script that needs a database connection. Whereas to access it in the functions/methods simply pass it as a parameter:

function getUserData($mysqli$id) {
    
$stmt $mysqli->prepare("SELECT * FROM user WHERE id=?");
    
$stmt->bind_param("s"$id);
    
$stmt->execute();
    return 
$stmt->get_result()->fetch_assoc();
}

remember that if a function is called inside another function, this outer function should take the $mysqli variable as a parameter as well.

Making prepared queries less verbose

Given a rather verbose syntax of mysqli prepared statements (and the fact you must always use them if variables are going to be used in the query), it would be quite useful to have a function like this added to the file with your connection code (you can keep it a function if your code is procedural or make it a method of your database wrapper class if you are using OOP)

function prepared_query($mysqli$sql$params$types "")
{
    
$types $types ?: str_repeat("s"count($params));
    
$stmt $mysqli->prepare($sql);
    
$stmt->bind_param($types, ...$params);
    
$stmt->execute();
    return 
$stmt;
}

A detailed description for this code you can read in the, Mysqli helper function

Now you can tell the difference:

$stmt $mysqli->prepare("SELECT * FROM user WHERE id=?");
$stmt->bind_param("s"$id);
$stmt->execute();
$user $stmt->get_result()->fetch_assoc();

vs.

$stmt prepared_query($mysqli"SELECT * FROM user WHERE id=?", [$id]);
$user $stmt->get_result()->fetch_assoc();

Other usage examples can be found on the Mysqli examples page

Include file

To sum everything up, let's create a file named mysqli.php and put the following code there. Just edit the database credentials there and then include this file in every PHP script that needs the database connection:

<?php

$host 
'127.0.0.1';
$db   'test';
$user 'root';
$pass '';
$charset 'utf8mb4';

mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
try {
    
$conn = new mysqli($host$user$pass$db);
    
$conn->set_charset($charset);
} catch (\
mysqli_sql_exception $e) {
     throw new \
mysqli_sql_exception($e->getMessage(), $e->getCode());
}
unset(
$host$db$user$pass$charset); // we don't need them anymore

function prepared_query($mysqli$sql$params$types "")
{
    
$types $types ?: str_repeat("s"count($params));
    
$stmt $mysqli->prepare($sql);
    
$stmt->bind_param($types, ...$params);
    
$stmt->execute();
    return 
$stmt;
}

Related articles: