Should I have to upgrade my website to PHP MySQLi or PDO?

Question

I have designed a website before 5 years using PHP MySQL and still works fine without any issues. I heard MySQL is officially deprecated as of PHP 5.5 and has been removed as of PHP 7. Also, PHP offers three different APIs to connect to MySQL (like MySQL, MySQLi, and PDO). My web server is updated frequently.

I understood, I have to move to newer API like MySQLi or PDO for safety. Now I am confused whether to choose MySQLi or PDO. Also, Is there any compatibility/migrating options available for such case?

Answer 1

Lets take a look at both of these extensions.

PDO

PDO is database neutral - You only need to learn one API to work with dozens of databases

So if you decide to move to another database, the only thing you would be changing is the DSN(data source name).

Support of both name and '?' placeholders for prepared statements.

Drupal uses PDO.

Mysqli

On the other hand Mysqli is designed specifically for Mysql databases and is recommended by Mysql. Mysqli works with Mysql and MariaDB databases.

Support of only '?' placeholders for prepared statements.

Joomla uses Mysqli

Conclusion

There are many conflicting arguments weather Mysqli or PDO runs faster. Both Mysqli and PDO use the same underlying drivers to access the database making the performance not worth comparing.

So there is no clear winner when working with Mysqli or PDO. But let's say you use Mysqli and then later you want to use another database, it would be a difficult transition.

The main strength of PDO over Mysqli is name placeholders for prepared statements and which is why I chose PDO over Mysqli.