March 17, 2016 7:47 pm

Stop using PHP MySQL extension and start using MySQLi or PDO

Nowadays, PHP have several database extensions. In the past, there was only the MySQL extension. Later, MySQLi extension appeared. It enabled developers to write more neat and maintainable code. It had also the power of object-oriented and it was more secure and strongly recommended in its days. Now, we have PDO that literally has it all.

Stop using PHP MySQL extension and start using MySQLi or PDO

MySQL

Most of us started learning PHP by using this extension. But if some developer decided to go on using this driver, he will be in a big risk.

First, it’s outdated and not secure. PHP stopped the development for it and it has been deprecated since the release of PHP5. It’s removed now in PHP7. This extensions requires a lot of coding to prevent MySQL injection attacks from happening.

There is no reason of course to stop using it more than that it’s deprecated in the newer versions of PHP. Any code using it should be considered legacy code and must be updated.

Read Also: How to use MySQLi_connect in PHP

MySQLi

In addition to having the ability to be used in and object-oriented code or in a procedural one, MySQLi supports transactions, prepared statements and it has more debugging capabilities than MySQL extension. It was the best choice until PDO came out.

As we said, MySQLi supports both Object-Oriented approach as well as Procedural approach. To use the object-oriented way, follow this example:

And the following code does the same job but using the procedural way:

PDO

First it supports prepared statement so the risk of SQL injection attacks is minimized. It also supports stored procedures, named parameters and it has the best performance benchmarks over all the PHP MySQL extensions.

Read Also: How to use PDO database connection in PHP

One other big advantage is that PDO not only supports MySQL, but it also supports the following database drivers:

  • PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
  • PDO_FIREBIRD ( Firebird/Interbase 6 )
  • PDO_IBM ( IBM DB2 )
  • PDO_INFORMIX ( IBM Informix Dynamic Server )
  • PDO_MYSQL ( MySQL 3.x/4.x/5.x )
  • PDO_OCI ( Oracle Call Interface )
  • PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
  • PDO_PGSQL ( PostgreSQL )
  • PDO_SQLITE ( SQLite 3 and SQLite 2 )
  • PDO_4D ( 4D )

Please note that you need to have the database driver installed on your server in order to use its driver.

Conclusion

You can use each of MySQLi or PDO but if you want to write code that will be long maintained an available to be extended to support more database drivers. But if you still use MySQL extension, please update your code.

Author Amr Abdou

A full-stack web developer, Linux enthusiast, freelance tech writer & open-source supporter. Loves finding quit corners in public places or cafes accompanied by his laptop to try changing the world from there. You can check his latest works at social-evolvea.com or follow him on facebook, twitter, Linkedin or Google+


Tutorial Categories:
  • Nikhil Mahajan

    thanks for the guidance. I planned to update my website with PDO numerous times but so far could not get the time to do so. I wish I could do it soon.

  • Johnsie

    for some developers that’s a lot of work changing A LOT stuff that previously worked ok. Would it not be better if the makers of PHP found a way to make existing codesets work with the new technology?

    • Bik Byro

      This is my main gripe with PHP. I know the language has to develop but you don’t see C programmers having to go back over ten years of projects.