Updating mysql code to mysqli in PHP

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

New mysqli

New mysqli, using objects

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

Leave a Reply

Your email address will not be published. Required fields are marked *