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.

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!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

PRELUDE) DUMMY DATABASE

-- (A) USERS TABLE
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `users`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
 
-- (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.

 

 

PART 1) LEGACY MYSQL EXTENSION

<?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", "utf8mb4");
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.

 

 

PART 2) MYSQLI EXTENSION

<?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", "utf8mb4");
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.

 

 

PART 3) PDO EXTENSION

<?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", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (B) ATTEMPT DATABASE CONNECTION
$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
]);

// (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.

 

 

 

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

• MySQLi – 0.0096925020217896 seconds
• PDO – 0.015011477470398 seconds

So yep, there is a less-than-one-split-second difference in performance. I 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.