Php: MySQL V MySQLi V Pdo

What is the difference between MySQLi and PDO?

PDO is an interface for accessing databases:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server. (source)

MySQLi is an extension for accessing MySQL databases:

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. (source)

Which one you should use is primarily opinion-based and not particularly well suited for the Stack Overflow format.

PHP: mysql v mysqli v pdo

The design of the mysql_query function is such that you've got to be careful to escape each and every bit of data you're injecting into it, and if you miss even one your entire application can be destroyed by an automatic SQL vulnerability exploit tool.

Both mysqli and PDO support placeholders which are required to ensure that your queries are safe from SQL injection bugs. Calling mysql_real_escape_string on everything is not only tedious, but error-prone, and that's where the problems arise.

The mysql functions are a product of the very early days of PHP and are significantly more limited than the new object-oriented features offered by both mysqli as an option, or PDO by design.

There's a number of very good reasons to use one of these two new interfaces, but the most important is that the mysql_query function is simply too hazardous to use in production code. With it you will always be one mistake away from some very serious problems.

There's a reason rips of databases full of passwords and credit card numbers keep showing up. Having an obvious SQL injection point makes it almost too easy to completely take over a site.

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.

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 MySQLi to PDO?

The most simple solution would be to change $pdo->fetch(PDO::FETCH_ASSOC) to $pdo->fetchAll(PDO::FETCH_ASSOC)

fetchAll returns ALL rows in the requested query, while fetch only gets 1 row (the first)

Example:

<?php

try {

    $PDO_result = $db_PDO->prepare("SELECT * FROM nnm_anime INNER JOIN nnm_anime_info ON nnm_anime.a_id = nnm_anime_info.a_id WHERE a_name LIKE ?");

    //Execute by inserting an array:
    if (!$PDO_result->execute([$pismenka[$i] . "%" ])) { //Added ."%" 
        die('Error!');
    }

    //Fetch rows:
    $rows = $PDO_result->fetchAll(PDO::FETCH_ASSOC);

    //Go trough each row:
    foreach ($rows as $row) {
        //Do something
    }

    //Catch exceptions thrown by PDO
} catch (PDOException $ex) {
    print_r($ex);
}