Compare mysqli or pdo what are the pros and cons

What's the main difference between these two other than methods and syntax.

Ultimately, PDO wins this battle with ease. With support for twelve different database drivers (eighteen different databases!) and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used (read: prepared statements). for more: https://code.tutsplus.com/tutorials/pdo-vs-mysqli-which-should-you-use--net-24059

PDO is much better than MYSQLI, because, in theory, you can use the database of your choice, i.e. postgress, sql server, etc.

And I say 'in theory', because always are pros and cons of using a specific driver for a Database

>> Prepared statements or PDO are better. They are more secure way to pass queries to DB. you will understand it once you learn how Prepared statements/PDO works.

jessica posted

what is difference between mysqli and PDO in PHP ?
which one is best to use mysqli or PDO in PHP ?

i mostly used mysql extension , never used PDO and mysqli is extended version of mysql and PDO is already know good alternative of mysql extension . so which one is good or bad between mysqli or PDO ? what is pros and cons of mysqli and PDO so i can avoid problem of database related in advance

Mitul Dabhi answered Nov 30 '-1 00:00

1. first of all mysqli specially designed for MySQL database connection with PHP,
PDO is database abstraction layer for PHP to connect with almost all type databases , so it is not for only MySQL but it will connect with other database like SQLite , Firebird .

class Student {

    public $id;
    public $first_name;
    public $last_name

    public function getFullName() {
        return $this->first_name.' '.$this->last_name
    }
}

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)

    $stmt = $dbh->query("SELECT * FROM students");

    /* MAGIC HAPPENS HERE */

    $stmt->setFetchMode(PDO::FETCH_INTO, new Student);


    foreach($stmt as $student)
    {
        echo $student->getFullName().'
';
    } 

    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

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

$connection_link = mysql_connect("host", "username", "password");

mysql_select_db("database_name", $connection_link);

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

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

?>

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

php

$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

$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

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.