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 MySQLi cannot be used with PHP 7 and its newer versions. However, developers can use MySQLi 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 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:



  • 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:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?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);
      
    ?>

    chevron_right

    
    

  • 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.
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    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:
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

    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.
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

  • 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.

    We can set these error modes as follow:

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    ?>

    chevron_right

    
    

    Now, try/catch method can be added as follows:

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php
      
    try {
          
        // Incorrect query
        $db->query('hello');
    }
      
    catch (PDOException $ex) {
          
        // $ex->getMessage();
          
         // Message to be displayed in
         // case of such an error
        echo "An Error has occurred";
    }
      
    ?>

    chevron_right

    
    

    An advantage of using the try/catch method is that we can set a normal error message to be displayed to the user, instead of an exception message, as that might be difficult for the general users to comprehend.

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.

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?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'];
    }
      
    ?>

    chevron_right

    
    

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

    edit
    close

    play_arrow

    link
    brightness_4
    code

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

    chevron_right

    
    

  • 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.

    The query is also used for data fetching, as it returns a PDOStatement object that can be used to fetch the data directly by using foreach and for loop.

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    <?php
      
    // Select query
    $stmt = $db->query('SELECT * FROM `data_table`');
      
    // fetchAll is used
    $my_results = $stmt->fetchAll(PDO::FETCH_ASSOC); 
    ?>

    chevron_right

    
    

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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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.

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.