You are here:Home»KB»Programming»PHP»PHP MySQL Database Drivers, Extensions and APIs
Thursday, 08 February 2024 18:10

PHP MySQL Database Drivers, Extensions and APIs

Written by

Deciding on how to connect your software to the relevant database requires you to understand the relevant technologies. I struggled with this myself so I looked it up and made some notes.

TL;DR

  • Modern version of PHP are compiled by default with php-mysql which has in it:
    • mysqlnd
    • mysqli
    • pdo_mysql
  • Use PDO for your new PHP projects.

What I found

After going thourh my research I put together this list of answers and points.

  • php-mysql
    • This is a PHP extension and ia a binary file.
    • This provides the following API Extensions for PHP:
      • MySQLi
      • MYSQL PDO
      • MySQL - This is no longer provided
    • This provides the following extensions (in a normal build):
      • mysqlnd
      • mysqli
      • pdo_mysql
    • Compile notes
      • This can be compiled to use the 'MySQL Native Driver' (mysqlnd) or the MySQL Client Library (libmysqlclient - the external shared driver from Oracle).
      • Since PHP 5.6 MySQL Native Driver is the default at compile time.
      • If compiled with mysqlnd you will see this extension in the list of 'Compiled in modules'.
      • From PHP 8.3 onwards, the ability to compile with libmysqlclient has been removed.
    • This does not provide the MySQL server.
    • This extension works for MySQL and MariaDB.
    • I think that using mysqlnd and MySQL PDO will allow you to to talk to other SQL servers.
  • Legacy PHP Extensions
    • The following PHP extensions no longer exist as separate binaries:
    • I am not sure in which version of PHP they were removed, but they are now aliases (Virtual Packages) of php-mysql and they are now included directly in this binary.
  • libmysqlclient
    • A shared driver from Oracle.
    • MySQL database client library.
    • Ubuntu – Details of package libmysqlclient21 in jammy
    • This was an external library that the PHP extensions php-mysql and php-mysqli used to talk to SQL servers to allow their APIs they presented to PHP to work.
    • This is not a PHP extension but an external library
  • MySQL Native Driver (mysqlnd)
    • This is the PHP team's implementation of the MySQL API layer and requires no external libraries.
    • The MySQL native driver for PHP (mysqlnd) is a drop-in replacement for the MySQL Client Library (libmysql) for the PHP script language.
    • This has a lot of improvements over using the libmysqlclient library.
    • This no longer exists as a separate PHP extension.
    • This is now the default driver in the php-mysql extension.
  • Ubuntu
    • When the PHP extensions are installed they are automatically enabled.
    • You can enable, disable and view the status of PHP extensions with the following commands:
      • phpenmod – Used to enable modules in PHP
      • phpdismod – Used to disable modules in PHP
      • phpquery – Used to view status of modules of PHP
  • Windows (Xampp)
    • To enable or disable extensions you need to edit the php.ini and comment/uncomment the relevant line in the Dynamic Extensions section.
    • You might see the legacy extension here and that is fine as these extensions are compiled for Windows which are not always the latest (or there might be other reasons).
  • Mysql vs MySQLi vs PDO
    These are called APIs Extensions and are not separate binaries anymore.
    • PDO
      • Can work with many different databases
      • OOP only
      • Supports prepared statements
      • Current and secure
      • Use this for all your new Projects
      • This API also adds its own abstraction layer to allow communication to the different types of databases.
      • You can wrote code once that will work on different databases with little or no alterations.
    • MySQLi
      • Only works with MySQL and MariaDB
      • Procedural and OOP
      • Supports prepared statements
      • Current and secure
    • MySQL
      • Only works with MySQL and MariaDB
      • Procedural only
      • Doesn't support prepared statements
      • Legacy and insecure
      • Removed in PHP7
      • Don't use
  • CMS
    • WordPress
      • Only uses MySQLi
    • Joomla
      • Has a database abstraction layer so it can use both MySQLi and PDO

Research

What are they and what do they do? Which is best for performance

  • php-mysqlnd
    • The following command will install php-mysql because php-mysqlnd is an alias.
      sudo apt install php-mysqlnd
    • MySQL native driver for PHP
    • There is no extension file for this in the Ubuntu repo.
    • MySQL :: MySQL native driver for PHP - mysqlnd
      • The MySQL native driver for PHP (mysqlnd) is a drop-in replacement for the MySQL Client Library (libmysql) for the PHP script language.
      • The PHP MySQL extensions are lightweight wrappers on top of a C client library. There are 3 PHP MySQL extensions:
        1. ext/mysql (not recommended)
        2. ext/mysqli
        3. PDO_MySQL
      • The extensions can either use the mysqlnd or libmysql library to connect from PHP to MySQL. Choosing one or the other library is a compile time decision. Both libraries are supported and constantly being improved.
      • MySQL recommends using the MySQL native driver for PHP (mysqlnd) together with ext/mysqli or PDO_MySQL.
      • As of PHP 5.4, the mysqlnd library is a php.net compile time default to all PHP MySQL extensions. Also, the php.net Windows team is using mysqlnd for the official PHP Windows distribution since mysqlnd became available in PHP 5.3.
    • Mysqlnd - MySQL Native Driver - Manual | PHP Manual (php.net)
      • Introduction
        • MySQL Native Driver is a replacement for the MySQL Client Library (libmysqlclient). MySQL Native Driver is part of the official PHP sources as of PHP 5.3.0.
        • The MySQL database extensions MySQL extension, mysqli and PDO MYSQL all communicate with the MySQL server. In the past, this was done by the extension using the services provided by the MySQL Client Library. The extensions were compiled against the MySQL Client Library in order to use its client-server protocol.
        • With MySQL Native Driver there is now an alternative, as the MySQL database extensions can be compiled to use MySQL Native Driver instead of the MySQL Client Library.
        • MySQL Native Driver is written in C as a PHP extension.
      • Overview
        • Although MySQL Native Driver is written as a PHP extension, it is important to note that it does not provide a new API to the PHP programmer. The programmer APIs for MySQL database connectivity are provided by the MySQL extension, mysqli and PDO MYSQL. These extensions can now use the services of MySQL Native Driver to communicate with the MySQL Server. Therefore, you should not think of MySQL Native Driver as an API.
        • Using the MySQL Native Driver offers a number of advantages over using the MySQL Client Library.
        • Because MySQL Native Driver is written as a PHP extension, it is tightly coupled to the workings of PHP. This leads to gains in efficiency, especially when it comes to memory usage, as the driver uses the PHP memory management system. It also supports the PHP memory limit. Using MySQL Native Driver leads to comparable or better performance than using MySQL Client Library, it always ensures the most efficient use of memory. One example of the memory efficiency is the fact that when using the MySQL Client Library, each row is stored in memory twice, whereas with the MySQL Native Driver each row is only stored once in memory.
        • Reporting memory usage: Because MySQL Native Driver uses the PHP memory management system, its memory usage can be tracked with memory_get_usage(). This is not possible with libmysqlclient because it uses the C function malloc() instead.
        • MySQL Native Driver also provides some special features not available when the MySQL database extensions use MySQL Client Library.
    • Easy Read/Write Splitting with PHP’s MySQLnd
      • This gives a bit of the history about this extension
      • MySQL Native Drive (mysqlnd) was added in PHP 5.3 and has been the default since PHP 5.4 (though you can still compile against libmysqlclient). It brings extra features, better performance, and better memory usage than libmysqlclient.
    • How to Install MYSQLND: mysqli Extension for php in Ubuntu? [SOLVED] - TheQuickBlog
      • The mysqli extension is missing. Please check your PHP configuration. See our documentation for more information.
      • this shows installation command using 
        sudo apt install php7-mysqlnd
    • PHP: What is mysqlnd, do I need it? | 
      • Mysqlnd is neither a new PHP extension nor a new API! mysqlnd is new C-level library code. The mysqlnd library provides almost the same functionality as libmysql does. Both C-libraries implement the MySQL communication protocol and can be used to connect to the MySQL Server.
      • mysqlnd has been designed as a drop-in replacement for libmysql. PHP extensions that use libmysql to connect to MySQL, can be modified to support both libmysql and mysqlnd. This adoption has been finished for ext/mysql and ext/mysqli. Of course, an extension can only use one C-library at a time: either libmysql or mysqlnd. Which one gets used is decided at compile time.
      • Nice diagram.
  • Ubuntu
    • How To Enable/Disable PHP Modules In Ubuntu - TecAdmin - How do I Enable/Disable PHP Modules on Ubuntu 22.04, 20.04 & 18.04 systems? Enable or disable a PHP module for specific PHP versions.
      • phpenmod – Used to enable modules in PHP
      • phpdismod – Used to disable modules in PHP
      • phpquery – Used to view status of modules of PHP
  • ubuntu - php-mysqlnd - Virtual Packages
  • What is the difference between mysqli and mysqlnd php extensions?
    • database - What is the difference between mysqli and mysqlnd php extensions? - Joomla Stack Exchange
      • I'm considering upgrading from PHP7.4 to PHP8.0 on a Joomla 3.10.8 installation. I was surprised to find that my hosting provider only offers mysqlnd as a PHP extension in PHP 8.0, but not mysqli.
      • Postscript: After testing the PHP extensions available, it appears that Joomla will not allow $dbtype to be changed. The solution appears to be to select nd_mysqli extension (as well as mysqlnd) in the PHP8.0 extension list. In configuration.php $dbtype stays as mysqli.
      • The mysqli extension can be compiled against mysqlnd (PHP native driver for MySQL) or libmysql (the shared driver from Oracle). It's a compilation time option. PHP 8.2 will only allow mysqli to be compiled with mysqlnd.
      • The mysqli extension isn't deprecated, but it can be considered obsolete due to the existence of PDO extension. There's no reason to use mysqli if you can use PDO. Joomla allows you to use PDO extension for connecting to the database. PDO_MySQL can also be compiled against libmysql and mysqlnd.
      • Therefore, when setting Joomla, the best setting should be PDO. But the option to use mysqli is still there.
      • When you are using cPanel, the extension page is a non-standard list of PHP extensions. It is confusing because there are two variants for mysqli and PDO, as well as mysqlnd itself. You want to have mysqlnd enabled and the native driver variants of mysqli and PDO, i.e. nd_mysqli and nd_pdo_mysql
  • mysqli
    • There is no extension file for this in the Ubuntu repo.
    • MySQL vs MySQLi in PHP - Tutsplanet - MySQL and MySQli are APIs to connect a MySQL database server in PHP. Each has it’s advantages and disadvantages. Note: By now MySQL extension is deprecated and it is not safe to use in future applications. It will be there for sometime for backward compatibility.
    • MySQLi can no longer be compiled with `libmysqli` - PHP 8.2 • PHP.Watch
      • Since PHP 5.4, mysqlnd is the default library, but it was possible to compile mysqli and pdo_mysql extensions with libmysql with a compile-time configuration flag.
      • Since PHP 8.2 and later, compiling mysqli extension with libmysql is no longer supported.
    • Enable the MySQLi Extension on Linux | Baeldung on Linux
      • Explore the MySQLi extension on a Linux system and learn how to enable it.
      • This article has various grep tests that you can use to verify status.
    • PHP MySQLi Functions | W3Schools
      • For the MySQLi functions to be available, you must compile PHP with support for the MySQLi extension.
      • The MySQLi extension was introduced with PHP version 5.0.0. The MySQL Native Driver was included in PHP version 5.3.0.
    • MySQLi - Wikipedia
  • PDO_MySQL
  • General
    • PHP: Choosing a library - PHP Manual (php.net)
      • The mysqli and PDO_MySQL PHP extensions are lightweight wrappers on top of a C client library. The extensions can either use the mysqlnd library or the libmysqlclient library. Choosing a library is a compile time decision.
      • The mysqlnd library is part of the PHP distribution. It offers features like lazy connections and query caching, features that are not available with libmysqlclient, so using the built-in mysqlnd library is highly recommended. See the mysqlnd documentation for additional details, and a listing of features and functionality that it offers.
  • PDO Tutorials
  • PHP Official Manual
    • PHP: Overview of the MySQL PHP drivers - Manual | php.net
      • There are several PHP APIs for accessing the MySQL database. Users can choose between the mysqli or PDO_MySQL extensions.
      • This guide explains the terminology used to describe each API, information about choosing which API to use, and also information to help choose which MySQL library to use with the API.
    • PHP: Choosing an API - Manual | php.net - PHP offers different APIs to connect to MySQL. Below we show the APIs provided by the mysqli and PDO extensions.
    • PHP: Installation - Manual | php.net - No meta description
      • The common Unix distributions include binary versions of PHP that can be installed. Although these binary versions are typically built with support for the MySQL extensions, the extension libraries themselves may need to be installed using an additional package.
      • The mysqli extension was introduced with PHP version 5.0.0. The MySQL Native Driver was included in PHP version 5.3.0.
      • For example, on Ubuntu the php5-mysql package installs the ext/mysql, ext/mysqli, and pdo_mysql PHP extensions. On CentOS, the php-mysql package also installs these three PHP extensions.
      • On Windows, php_mysqli.dll DLL must be enabled in php.ini.
  • Mysql vs MySQLi vs PDO
    • database - What is the difference between mysqli and mysqlnd php extensions? - Joomla Stack Exchange
      • The mysqli extension can be compiled against mysqlnd (PHP native driver for MySQL) or libmysql (the shared driver from Oracle). It's a compilation time option. PHP 8.2 will only allow mysqli to be compiled with mysqlnd.
      • The mysqli extension isn't deprecated, but it can be considered obsolete due to the existence of PDO extension. There's no reason to use mysqli if you can use PDO. Joomla allows you to use PDO extension for connecting to the database. PDO_MySQL can also be compiled against libmysql and mysqlnd.
      • Therefore, when setting Joomla, the best setting should be PDO. But the option to use mysqli is still there.
      • When you are using cPanel, the extension page is a non-standard list of PHP extensions. It is confusing because there are two variants for mysqli and PDO, as well as mysqlnd itself. You want to have mysqlnd enabled and the native driver variants of mysqli and PDO, i.e. nd_mysqli and nd_pdo_mysql
    • [4.0] Installation DB Order by brianteeman · Pull Request #19780 · joomla/joomla-cms · GitHub
      • Q: I guess PDO is for specific needs and those who know that will choose it.
      • A: No, not really. It's almost the same as MySQL versus MySQLi on PHP 5 (before the former got deprecated). It's just a different underlying API being used to process database transactions. With our abstraction layer the only thing that should matter is whether you have the dependencies for each driver installed. PDO's not some "super advanced special case" system (quite the opposite, most of the PHP ecosystem only uses or supports PDO based database connections, CMS' like Joomla and WordPress are the exception to this pattern where the non-PDO based PHP extensions are used more dominantly).
    • Why You Should Use PDO Instead of MySQLi | Conetix - In this post, I want to look at why you should consider migrating away from MySQLi to PDO in your PHP applications.
    • MySQL vs MySQLi vs PDO Performance Benchmark, Difference and Security Comparison | by Haseeb A. Basil | Medium
    • PDO vs. MySQLi: Which Should You Use? | Envato Tuts+ - When accessing a database in PHP, we have two choices: MySQLi and PDO. So what should you know before choosing one? The differences, database support, stability, and performance concerns will be outlined in this article.
    • PDO vs. MySQLi: The Battle of PHP Database APIs - Long gone are the days of using the mysql_ extension, as its methods have been deprecated since PHP 5.5 and removed as of PHP 7. Alas, the internet is still plagued with a ton of old tutorials that beginners will simply copy/paste and use on a shared hosting platform with an older PHP version, thus continuing its legacy.
    • MySQLi vs PDO vs MySQLnd: PHP Database Extensions Compared - Learn how to choose the best PHP database extension for your web application. Compare MySQLi, PDO, and MySQLnd features, pros, and cons.
    • php - What the different between MySQL Native Driver and MySQL Client Library - Stack Overflow - I want to know the different between MySQL Native Driver and MySQL Client Library and when to use both of them.
  • cPanel PHP Extensions
  • ADOdb vs PDO
    • ADOdb - Database Abstraction Layer for PHP [ADOdb] - ADOdb is a database abstraction layer for PHP. Compatible with most supported databases, it provides an easy way to use the same code against all databases, Portable code can be written that runs unchanged. It also simplifies PHP database access, and includes an extended date/time library, database managed session control, XML database management, and an active record library
    • GitHub - ADOdb/ADOdb: - ADOdb is a PHP database class library that provides powerful abstractions for performing queries and managing databases. ADOdb also hides the differences between DB engines so you can easily switch them without changing your code.
    • PDO (new) [ADOdb]
      • Using ADOdb effectively eliminates the need to use the PHP PDO driver as it hides the PHP level command:
Read 94 times Last modified on Sunday, 11 February 2024 16:02
Published in PHP
back to top