Change language

What is the difference between MySQL, MySQLi and PDO?

Let’s get some more information about each of them:
  • MySQL:this was the main extension that was developed to help PHP applications send and receive data from the database MySQL data. However, the use of MySQL has been deprecated and removed since PHP 7 and newer versions. This is why this is not recommended for new projects, and why the MySQLi and PDO extensions are used more often nowadays.
  • MySQLi:The “i” in MySQLi stands for “Improved”. For this reason, it is also known as an improved version of MySQL. It has many new features that will be discussed later in the article.
  • PDO - PHP data objects . The main advantage of using PDO is that it supports and provides a single method of accessing 11 different databases.
Supported PDO databases:
  • MS SQL Server
  • Firebird / Interbase
  • IBM
  • Informix
  • MySQL
  • oracle
  • ODBC and DB2
  • PostgreSQL
  • SQLite
  • 4D
However, PDO does not allow all the features available in the current MySQL server version. For example, PDO does not support multiple MySQL statements.Comparing MySQL, MySQLi, and PDO:
  • Connecting to a Database
  • Error Handling
  • Data Fetching
  • API Support
  • Security
Database Connection:
  • MySQL:MySQL code for connecting to the database:  
    // Add hostname, database username and password $connection_link = mysql_connect ( "host" , "username" , "password" );  
    // Select a query for the database mysql_select_db ( "database_name" , $connection_link );  
    // Set UTF-8 encoding for use in projects mysql_set_charset ( ’UTF-8’ , $connection_link );  
  • MySQLi:in case of MySQLi there is only one line of code. User creates a MySQLi instance using username, password and database name.  
    // Database credentials $mysqli_db = new mysqli ( ’host ’ , ’ username’ , ’password’ , ’ database_name’ );  
  • PDO:in case of PDO a new PDO must be created.  
    // Credentials required to connect $pdo = new PDO ( ’ mysql: host = host; dbname = database_name; charset = utf8’ , ’ username’ , ’password’ );  
    The big advantage of using PDO is that it makes it easier to switch projects to another database. Therefore, the only thing to do is change the connection string and those queries that will not be supported by the new database.
Error handling:error handling - it is the detection and elimination of application, programming or communication errors. Error handling helps keep the program running smoothly because errors in the program are handled gracefully so that the program runs well.
  • MySQL: $my_result = mysql_query ( "SELECT * FROM table" , $connection_link ) or die (mysql_error ( $connection_link )); ?> Method ’ die ’ is used for error handling in MySQL, but it is not considered a good approach to error handling. This is because die abruptly exits the script and then displays an error on the screen. This can make the database prone to hackers.
  • MySQLi:Error handling in MySQLi, if a little simpler. Mysqli::$error (mysqli_error) returns a string description of the last error. if (! $mysqli -> query ( "SET a = 1" )) { printf ( "Errormessage:% s " , $mysqli -> error); }  
  • PDO:PDO has the best the error handling method of these three. This is due to the availability of the try-catch block. There are also several error modes that you can use to handle errors.
    • PDO::ERRMODE_SILENT:is used to validate each result and then validate $db-> errorInfo() for error details.
    • PDO::ERRMODE_WARNING:The warning does not stop the script. This provides run-time warnings, not fatal errors.
    • PDO::ERRMODE_EXCEPTION:it throwsexceptions that indicate an error thrownPDO. It shouldn’t throw PDOException with your code. It acts the same as or will die (mysql_error()) when not caught. But it can catch these PDOExceptions and handle them however we want.
    We can set these error modes like this: $db -> setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $db -> setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $db -> setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); ?> Now try / catch method can be added like this: try {   // Invalid request  $db -> query ( ’hello’ ); } catch (PDOException $ex ) { // $ex-> getMessage(); // Message to display in // case of such an error echo "An Error has occurred" ; }  
    The advantage of using the try / catch method is that we we can set a regular error message to be displayed to the user rather than an exception message, as it can be difficult for ordinary users to understand.
Data fetching:
  • MySQL:General programming loops such as for or while can be used for this purpose. Suppose we have a table named "data" in the database and we want to output the username from each row of the table. The while loop can be used as follows:  

    $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 also uses cycle for this purpose. The code, however, will be slightly different. while ( $row = $my_result -> fetch_assoc()) { echo $row [ ’username’ ]. ’’ ; }  
  • PDO:PDO has many built-in assertions to help in such cases.
    • PDOStatement::fetchAll():returns the result as an array containing all the rows of the result.
    • PDOStatement::fetchColumn():fetches one column from the next row of the result set.
    • PDOStatement::fetchObject():fetch the following rows first and then return as object.
    • PDOStatement::setFetchMode():sets the default fetch mode for the statement.
    The query is also used to fetch data because it returns a PDOStatement object that can be used to fetch data directly with a foreach and for loop.  
    // Select a query $stmt = $db -> query ( ’SELECT * FROM’ data_table’’ );  
    // fetchAll is used $my_results = $stmt -> fetchAll (PDO::FETCH_ASSOC); ?>
API support. When it comes to API support, PDO provides an object oriented approach. MySQLi provides a procedural approach very similar to MySQL. This is the reason why the MySQL developers prefer to use 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 securityis used to protect databases and the information they contain from hackers and their attacks. Hackers usually use SQL injection to destroy the database. Thus, pinpoint safety must be ensured.Both PDO and MySQLi provide SQL injection security.Suppose a hacker tries to inject SQL injection via the firstname HTTP request parameter with using the POST method: $_ POST [ ’ firstname’ ] = "’; DELETE FROM users; / * " If the injection escapes, it will be added to the query as is. This way it will remove all rows from the users table.In PDO, manual escaping is used to improve security. $name = PDO::quote ( $_ POST [ ’name’ ]); $pdo -> query ( "SELECT * FROM users WHERE name = $name" ); Difference between PDO::quote() and mysqli_real_escape_string() is that the first escapes the string and the quote, while the second escapes only the string, and the quotes must be added manually.


Best laptop for Sims 4


Best laptop for Zoom


Best laptop for Minecraft


Best laptop for engineering student


Best laptop for development


Best laptop for Cricut Maker


Best laptop for hacking


Best laptop for Machine Learning


Latest questions


psycopg2: insert multiple rows with one query

12 answers


How to convert Nonetype to int or string?

12 answers


How to specify multiple return types using type-hints

12 answers


Javascript Error: IPython is not defined in JupyterLab

12 answers


Python OpenCV | cv2.putText () method

numpy.arctan2 () in Python

Python | os.path.realpath () method

Python OpenCV | () method

Python OpenCV cv2.cvtColor () method

Python - Move item to the end of the list

time.perf_counter () function in Python

Check if one list is a subset of another in Python

Python os.path.join () method