There are a few different ways of accessing MySQL from PHP. Historically the mysql extension was the only way until PHP 5.0 (circa 2004). After that mysqli appeared, which is an improved version of the older mysql driver. And also PDO, which gives you an abstraction layer for different databases. Unfortunately there’s still a lot of legacy code around using the old mysql extension, and that must be updated to use the newer mysqli extension.
Starting with PHP 5.5 the old mysql extension is deprecated, that means you’ll receive E_DEPRECATED errors as a warning but the code will continue running ok. Starting with PHP 7.0 the extension is removed and your code won’t run at all.
The error/warning in PHP 5.5-5.6 will look like this:
Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in …
And in order for this to be even more confusing there are 2 ways to use the new mysqli extensions: procedural style and object oriented style.
Procedural style
- very similair to the old mysql extension, most functions have almost the same name
- easy to port old code to this procedural style
- mysqli_connect will return an object representing the connection, every other mysqli function will require it as an argumennt
Object oriented style
- my opinion – looks cleaner, makes it easier the understand the code
- you’ll work with objects and object methods
Let’s continue with some code examples that show connecting to the database and retrieving some data.
Old mysql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$link = mysql_connect('localhost', 'my_user', 'my_password'); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db('my_db', $link); if (!$db_selected) { die ('Could not select db : ' . mysql_error()); } $result = mysql_query("select id, username from users"); if (!$result) { die('Invalid query: ' . mysql_error()); } while ($row = mysql_fetch_assoc($result)) { echo $row['id']; echo $row['username ']; } |
New mysqli
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$link = mysqli_connect("localhost", "my_user", "my_password", "my_db"); // database name can be specified from the beginning if (!$link) { die('Could not connect: ' . mysqli_connect_errno() . ' - ' . mysqli_connect_error()); } $result = mysqli_query($mysqli, "select id, username from users"); // uses the $mysqli object, obtained from mysqli_connect if (!$result) { die('Invalid query: ' . mysql_error()); } while ($row = mysqli_fetch_assoc($result)) { // same usage as old mysql extension echo $row['id']; echo $row['username ']; } |
New mysqli, using objects
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db'); if ($mysqli->connect_error) { die('Could not connect: ' . $mysqli->connect_errno . ' - ' . $mysqli->connect_error); } $result = $mysqli->query("select id, username from users"); // uses the $mysqli object, obtained from the mysqli constructor if (!$result) { die('Invalid query: ' . mysql_error()); } while ($row = $result->mysqli_fetch_assoc()) { echo $row['id']; echo $row['username ']; } |
Persistent connections
In the old mysql extension, persistent connections are activated by using mysql_pconnect instead of mysql_connection.
In the new msqyli extension, the usage is different – you must prepend p: to the hostname when connecting. Instead of mysqli_connect(“localhost”, …) you’ll have mysqli_connect(“p:localhost”, …)
Converter tool
There’s actually a free converter tool available. It can be used to convert automatically all the old mysql code to the new mysqli code and it does a pretty good job.
Check it out at https://github.com/philip/MySQLConverterTool
Hello,
I’m wondering when it comes to queries performances, which is better PDO or MySQLi ? I’m hesitating between these 2 extensions, what I like with PDO is the possibility to choose another kind of database
@Pricereduc in theory PDO sould be a little slower because it adds anoter layer of code, but in practice the overhead is extremely low and shoudn’t be noticeable at all