MYSQL vs MYSQLI vs PDO in PHP (Which One To Use!?)

Welcome to a quick tutorial on the difference between MySQL, MySQLi, and PDO in PHP. So you may have just started working with MySQL in PHP, and noticed that there are 3 different extensions to work with the database.

The main difference between the MySQL, MySQLi, and PDO extensions is:

  1. MySQL – The early PHP-MySQL extension, currently defunct and removed.
  2. MySQLi (MySQL Improved) – An improved version of the earlier MySQL extension.
  3. PHP Data Objects (PDO) – The modern database extension. Has better support for not just MySQL, but also other databases such as Firebird, SQLite, Postgre, and more.

Yes, the so-called “difference” is actually the development history of MySQL extensions in PHP itself. But just which one is correct, which one is “better”, and which should you use? Read on to find out!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes MySQL Extensions Useful Bits & Links
The End

 

DOWNLOAD & NOTES

First, here is the download link to the example source code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Please make sure that the respective extension=mysql, extension=mysqli, or extension=pdo_mysql is enabled in php.ini.
  • Create a test database and import 0-dummy.sql.
  • 1-mysql.php2-mysqli.php, and 3-pdo.php will show the respective examples of each extension. Just remember to change the database settings in the scripts.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

PHP MYSQL EXTENSIONS

All right, let us now get into the examples of using each MySQL database extension. Also, a simple “non-scientific non-professional” test to see which one performs the best.

 

0) DUMMY DATABASE

0-dummy.sql
-- (A) USERS TABLE
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
 
-- (B) DUMMY USERS
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'Bevis'),
(2, 'Cally'),
(3, 'Harrison'),
...

If you are interested to run the “non-conclusive performance test”, here is the dummy table we are using, download the zip file above.

 

1) LEGACY MYSQL EXTENSION

1-mysql.php
<?php
// (TEST) START TIME
$taken = microtime(true);
 
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (B) DATABASE CONNECTION
$mysql = new mysql(DB_HOST, DB_USER, DB_PASSWORD);
if (!$mysql) { exit(mysql_error()); }
mysql_select_db(DB_NAME, $mysql);
mysql_set_charset(DB_CHARSET, $mysql);

// (C) SELECT USERS
$result = mysql_query("SELECT * FROM `users`");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) { print_r($row); }
mysql_free_result($result);
mysql_close($mysql);

// (TEST) TOTAL TIME TAKEN
$taken = microtime(true) - $taken;
echo "Total time taken $taken";

Now, the (original) MySQL extension has been totally removed in PHP 7. As such, I will just leave this here as an example for the people who may need to support legacy systems. Otherwise, please don’t use this outdated extension anymore.

 

 

2) MYSQLI EXTENSION

2-mysqli.php
<?php
// (TEST) START TIME
$taken = microtime(true);
 
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (B) ATTEMPT DATABASE CONNECTION
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) { exit($mysqli->connect_error); }
$mysqli->set_charset(DB_CHARSET);
 
// (C) SELECT USERS
$result = $mysqli->query("SELECT * FROM `users`");
while ($row = $result->fetch_assoc()) { print_r($row); }
$result->close();
$mysqli->close();
 
// (TEST) TOTAL TIME TAKEN
$taken = microtime(true) - $taken;
echo "Total time taken $taken";

That’s right, this is pretty much the same as the traditional MySQL, the only difference is that we are using new mysqli() instead. As for the usage, just trace through the script, it is straightforward as can be.

 

 

3) PDO EXTENSION

3-pdo.php
<?php
// (TEST) START TIME
$taken = microtime(true);
 
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (B) ATTEMPT DATABASE CONNECTION
try {
  $pdo = new PDO(
    "mysql:host=". DB_HOST .";dbname=". DB_NAME .";charset=". DB_CHARSET,
    DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (C) SELECT USERS
$stmt = $pdo->prepare("SELECT * FROM `users`");
$stmt->execute();
while ($row = $stmt->fetch()) { print_r($row); }
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }

// (TEST) TOTAL TIME TAKEN
$taken = microtime(true) - $taken;
echo "Total time taken $taken";

Now, this PDO example should be very straightforward too. But the beauty of PDO actually lies with its compatibility with a wide range of databases – That is, this extension works with many other databases apart from MySQL. Also, there are many smart ways to fetch entries using PDO – fetch(), fetchColumn(), fetchAll(), and even down to setting how the array is arranged.

 

 

USEFUL BITS & LINKS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

 

WHICH ONE IS “BETTER”?

For a simple test, I ran 2-mysqli.php and 3-pdo.php on my development server, fetching 100 users 10 times. The average timing is as follow:

  • MySQLi – 0.0096925020217896 seconds
  • PDO – 0.015011477470398 seconds

So yep, there is a split-second difference in performance. I personally won’t make a huge fuss out of it, but if you are into “scientific professional performance tests” – Feel free to devise your own set of tests (and let us know the results).

 

MYSQL, MYSQLI, PDO – WHICH ONE SHOULD I USE?

I will highly recommend using PDO. Simply for its support for a range of databases, and the abstraction it provides. We only need to enable the respective PDO extension in php.ini, and the project is ready to work with databases other than MySQL. For example, “upgrading” from MySQL to Oracle Database.

 

REFERENCES & LINKS

  • PDO – PHP
  • MySQLi – PHP
  • MySQL – PHP
  • phpMyAdmin – A free PHP database manager. If you installed the XAMPP package, it is already installed at http://localhost/phpmyadmin.
  • MySQL Workbench – The so-called official MySQL database manager by Oracle themselves.

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *