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:
- MySQL – The early PHP-MySQL extension, currently defunct and removed.
- MySQLi (MySQL Improved) – An improved version of the earlier MySQL extension.
- PHP Data Objects (PDO) – The modern database extension. Supports 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!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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
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.
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.
EXTRAS
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 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.
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!
What about the new mysqli_execute_query function and mysqli::execute_query method (PHP 8.2)? Are they a relevant difference in favour of MySQLi?
https://www.php.net/manual/en/mysqli.execute-query.php
Pretty much a shortcut for prepare > bind > execute > get results. Nothing wrong with using MySQLi, if you are sure the project will be “MYSQL only”.