How to connect properly using mysqli

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:

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.

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.

mode will eliminate the cryptic error messages like / , giving you the instead. security is not a laughing matter, there should be not a chance to leak your database details to the outside

is not a laughing matter, there should be not a chance to leak your database details to the outside ease of use of prepared statements

Object syntax

$host = '127.0.0.1' ;

$port = 3306 ;

$db = 'test' ;

$user = 'root' ;

$pass = '' ;

$charset = 'utf8mb4' ;



mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );

try {

$mysqli = new mysqli ( $host , $user , $pass , $db , $port );

$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

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

$host stands for the database host. In case of the local development, it is most likely be 127.0.0.1 or localhost . In case of the live site, the actual hostname should be provided by the site admin / hosting provider. Note that connecting through IP address could save you a headache or two, so if you have a trouble with "localhost", try to use 127.0.0.1 instead.

stands for the database host. In case of the local development, it is most likely be or . In case of the live site, the actual hostname should be provided by the site admin / hosting provider. Note that connecting through IP address could save you a headache or two, so if you have a trouble with "localhost", try to use 127.0.0.1 instead. $db is the name of the database in MySQL (the value that you were passing into mysql_select_db() ). On your local server it could be anything, while on a live site again it should be given to you by the admin / provider.

is the name of the database in MySQL (the value that you were passing into ). On your local server it could be anything, while on a live site again it should be given to you by the admin / provider. $user - a database user

- a database user $pass - a database password

- a database password $port is the port number on the database host that listens for connections. It is reported that some PHP dev environments such as MAMP are using non-default port number, so you have to check it out and change if necessary.

is the port number on the database host that listens for connections. It is reported that some PHP dev environments such as MAMP are using non-default port number, so you have to check it out and change if necessary. $charset is a very important option. It is telling the database in which encoding you are sending the data in and would like to get the data back. Note that due to initially limited support of Unicode in the utf8 MySQL charset, it is now recommended to use utf8mb4 instead.

Functions explained

Beside new mysqli() we are using two additional functions here:

mysqli_report() is very important as it sets the proper error reporting mode for mysqli, telling it to throw an exception in case of error. Which is good in so many ways, which are explained in the article on PHP error reporting.

mysqli::set_charset is the proper way to set the character set for the connection, as opposed to running the SET NAMES query.

Procedural syntax

Previously here was an example for the procedural mysqli syntax. But some time ago I decided to remove it for the following reasons:

procedural syntax is much more verbose than object syntax, just compare $stmt -> bind_param (..);

// vs.

mysqli_stmt_bind_param ( $stmt , ..);

object syntax is very simple. You don't have to learn OOP to use it. It's just a syntax which only slightly different from procedural. Just a simple rule: type a variable name, then an arrow and then a function name: $stmt->bind_param() it is really simple.

it is really simple. most important of all: procedural syntax is deceptive in regard of error reporting. Even with error reporting properly set, procedural calls fail to report errors, ending up with cryptic error messages such as "invalid object or resource mysqli_stmt"

Just adapt the object syntax, it will take you just two minutes, but your code will start looking much cooler immediately!

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.

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.

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

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

Storing database credentials

Having credentials hardcoded in the same file quickly proves inconvenient. When the site goes live, it would need different credentials for sure, hence you would have to rewrite this file. And when you will need to work a bit more on the code, you will have to rewrite it back... and so on. Which is far from being convenient.

There are many advanced techniques for providing the settings but the simplest one would be just storing them in a separate file. This way you'll be able to keep different configuration files on different servers. So instead of hardcoding the credentials, just use variables defined in a separate file:

<? php

$host = '127.0.0.1' ;

$port = 3306 ;

$db = 'test' ;

$user = 'root' ;

$pass = '' ;

$charset = 'utf8mb4' ;

and then just include this file in your database connection script

Include files

To sum everything up, let's create two files, one named mysqli.php with the following code inside,

<? php



mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );

try {

require __DIR__ . '/db_credentials.php' ;

$mysqli = new mysqli ( $host , $user , $pass , $db , $port );

$mysqli -> set_charset ( $charset );

} catch (\ mysqli_sql_exception $e ) {

throw new \ mysqli_sql_exception ( $e -> getMessage (), $e -> getCode ());

} finally {

unset( $host , $db , $user , $pass , $charset );

}



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 ;

}