How to properly connect to Mysql database using mysqli

  1. Procedural code
  2. Object-oriented code
  3. Credentials explained
  4. Functions explained
  5. Handling errors
  6. Accessing the newly created connection
  7. Comments (1)

In this example we will learn how to properly connect to Mysql database using mysqli.

Unfortunately, the example code in the PHP manual lacks essential information, namely:

So, the correct example would be

Procedural code

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

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

Please note that contrary to the popular delusion, there is not a single technical problem in the intermixing the procedural and OOP code. Although such intermixing is giving your code a bad look and you should always keep to just one style, mysqli allows you to mix the styles freely without producing any error.

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 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 this one to 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 re-throwing thit. So, now the stack trace begins on the throw line and doesn't contain the database credentials.

However, if your connection code is wrapped in a function, this function's parameters will be shown in the stack trace as well. To avoid that, either send the credentials into this function in the form of array or object, or fetch them inside this 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($db$id) {
    
$stmt $db->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 $db variable as a parameter as well.


Related articles: