What is the difference between MySQL, MySQLi and PDO?

To understand the difference between MySQL, MySQLi, and PDO, we must know about each one of them individually. These are nothing but the APIs of PHP that is used to access the MySQL databases and tables. The developers can choose either one of them for their project, however, it must be known that MySQL cannot be used with PHP 7 and its newer versions. However, developers can use MySQL with PHP 5, which is now deprecated. Let’s have some more information about each of them:

• MySQL: This was the main extension that was designed to help PHP applications send and receive data from the MySQL database. However, use of MySQL has been deprecated and removed as of PHP 7 and its newer versions. This is why it is not recommended for new projects, and that’s the reason why MySQLi and PDO extensions are used more nowadays.
• MySQLi: The ‘i’ in MySQLi stands for Improved. Therefore, this is also known as the improved version of MySQL. It has many new features that will be covered later in the article.
• PDO – PHP Data Objects: The main advantage of using PDO is that it supports, and provides a uniform method of access to 11 different databases.

PDO-supported databases are as follows:

• CUBRID
• MS SQL Server
• Firebird/Interbase
• IBM
• Informix
• MySQL

• Oracle
• ODBC and DB2
• PostgreSQL
• SQLite
• 4D

    However, PDO does not allow the usage of all the features available in the present version of the MySQL server. For example, PDO doesn’t allow the support of MySQL’s multiple statements. 

Comparing MySQL, MySQLi, and PDO:

• Connection to the Database
• Error Handling
• Data Fetching
• API Support
• Security

Connection to the database:

MySQL: The MySQL code to connect to the database is: 

php

<?php

// Add the hostname, username and password of the database
$connection_link = mysql_connect("host", "username", "password");

// Select query for the database
mysql_select_db("database_name", $connection_link);

// Set the charset, UTF-8 to be used for projects
mysql_set_charset('UTF-8', $connection_link);

?>

MySQLi: In case of MySQLi, there is just a one-line code. The user instantiates a MySQLi instance using the username, password, and name of the database. 

php

<?php

// Database credentials
$mysqli_db = new mysqli('host', 'username', 'password', 'database_name');

?>

PDO: In case of PDO, a new PDO object must be created. 

php

<?php

// Credentials required for connection
$pdo = new PDO('mysql:host=host; dbname=database_name; charset=utf8',
            'username', 'password');

?>

A big advantage of using PDO is that it makes switching the project to another database simpler. Therefore, the only thing to do is to change the connection string and those queries that will not be supported by the new database.

Error Handling: Error handling is the detection, and resolution of application, programming or communication errors. Error handling helps in maintaining the normal flow of program execution, as the errors in the program are deal gracefully, thus making the program run well.

MySQL:

php

<?php

$my_result = mysql_query("SELECT * FROM table", $connection_link)
        or die(mysql_error($connection_link));
?>

The ‘die’ method is used for error handling in MySQL but it is not considered to be a good approach of error handling. This is because die abruptly ends the script and then display the error to the screen. This can make the database prone to hackers.

MySQLi: The error handling in MySQLi if a bit easier. The mysqli::$error (mysqli_error) returns a string description of the last error. 

php

<?php

if (!$mysqli->query("SET a=1")) {
    printf("Errormessage: %s\n", $mysqli->error);
}

?>

PDO: PDO has the best error handling method out of these three. This is because of the availability of the try-catch block. Also, there are some error modes that can be used for error handling.

• PDO::ERRMODE_SILENT: It is used to check each result and then check $db->errorInfo() to get error details.
• PDO::ERRMODE_WARNING: Warning does not halt the script. This provides run-time warnings and not fatal errors.
• PDO::ERRMODE_EXCEPTION: It throws exceptions that show an error raised by PDO. It should not throw a PDOException with your code. It acts much like or die(mysql_error()) when it isn’t caught. But it can catch these PDOException and handle as we want.

Data Fetching:

MySQL: General programming loops such as for, or while loop can be used for such a purpose. Suppose there is a table named ‘data’ in the database and we want to output the username from each row of the table. While loop can be used in the following way to do the work. 

php

<?php

$my_result = mysql_query('SELECT * from data')
        or die(mysql_error());

$num_rows = mysql_num_rows($my_result);

while($row = mysql_fetch_assoc($my_result)) {
    echo $row['field1'];
}

?>

MySQLi: MySQLi uses a loop for this purpose as well. The code, however, will be a bit different. 

php

<?php

while($row = $my_result->fetch_assoc()) {
    echo $row['username'] . '\n';
}

?>

PDO: PDO has many in-built statements that help in such cases.

• PDOStatement::fetchAll(): It returns the result in the form of an array, containing all of the result rows.
• PDOStatement::fetchColumn(): It fetches a single column from the next row of a result set.
• PDOStatement::fetchObject(): This first fetches the next rows and then returns it as an object.
• PDOStatement::setFetchMode(): It sets the default fetch mode for the statement.

API Support: When it comes to the API support, PDO provides an object-oriented approach. MySQLi provides a procedural way, much similar to the MySQL. This is the reason why developers coming from a MySQL background prefers using MySQLi. However, object-oriented programmers prefer PDO because of its compatibility with a large number of databases. Thus, object-oriented programmers prefer PDO, while procedural programmers prefer MySQL and MySQLi. Security: Database security is used to protect databases and the information they contain from the hackers and their attacks. Hackers generally use SQL injections to disrupt the database. Thus, security from the injections must be ensured. Both PDO and MySQLi provide SQL injection security. Suppose a hacker is trying to inject an SQL injection through the ‘firstname’ HTTP query parameter using the POST method: 

php

$_POST['firstname'] = "'; DELETE FROM users; /*"

If the injection escapes, it will be added in the query “as it is”. Thus, it will delete all rows from the users table. In PDO, manual escaping is there to add security. 

php

$name = PDO::quote($_POST['name']);
$pdo->query("SELECT * FROM users WHERE name = $name");

The difference between PDO::quote() and mysqli_real_escape_string() is that the former escapes the string and the quote, while the latter will only escape the string and the quotes will have to be added manually.