MySQLi or Pdo - What Are the Pros and Cons

PDO vs MySQLi. What do you think? PHP

If you think there is a remote possibility you will need to use a different DB backend, then use PDO. If you know you will only be using MySQL and already know how to use it, or have a good library to wrap the mysqli_*() class of functions, just use that.

Advantages of pdo over mysql_* or mysqli_*

It's not about supporting multiple databases with one app (although you can, with care). It's not about making it easy to change databases in the future (although it helps). It's about having one consistent, sensible interface to use regardless of database. Not only does that benefit programmers (by making their skills more broadly applicable) and projects (by making it easier for programmers to jump in), it also makes it a lot easier to create libraries that sit one layer above the data access layer. Perl has had the DBI for 20 years and it's been a decidedly good thing. PDO is a very similar concept (in fact it steals at least half of DBI's interface).

Doubts of security: Mysqli vs PDO

There is no difference in security, but only in usability.
Mysqli is unusable as is, leaving PDO the only choice.

PHP PDO and MySQLi

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.

mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.

PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

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

The answer is fairly simple.

If, like majority of PHP users, you are going to use database API functions right in the application code, without any intermediate wrapper, then PDO is your only choice, as it's a sort of wrapper already, automating many operations that with mysqli have to be done manually.

No, there are no migration options, because the very approach is changed dramatically: instead of placing variables right in the query, they have to be substituted in the query with special marks. There is no way to automate this process.