Overview
This section provides an introduction to the options available to you when developing a PHP application that needs to interact with a MySQL database.
An Application Programming Interface, or API, defines the classes, methods, functions and variables that your application will need to call in order to carry out its desired task. In the case of PHP applications that need to communicate with databases the necessary APIs are usually exposed via PHP extensions.
APIs can be procedural or object-oriented. With a procedural API, you call functions to carry out tasks, with the object-oriented API you instantiate classes and then call methods on the resulting objects. Of the two, the latter is usually the preferred interface, as it is more modern and leads to better-organized code.
When writing PHP applications that need to connect to the MySQL server there are several API options available. This document discusses what is available and how to select the best solution for your application.
In the MySQL documentation, the term connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL provides connectors for a variety of languages, including PHP.
If your PHP application needs to communicate with a database server you will need to write PHP code to perform such activities as connecting to the database server, querying the database, and other database-related functions. Software is required to provide the API that your PHP application will use, and also handle the communication between your application and the database server, possibly using other intermediate libraries where necessary. This software is known generically as a connector, as it allows your application to connect to a database server.
A driver is a piece of software designed to communicate with a specific type of database server. The driver may also call a library, such as the MySQL Client Library or the MySQL Native Driver. These libraries implement the low-level protocol used to communicate with the MySQL database server.
By way of an example, the PHP Data Objects (PDO) database abstraction layer may use one of several database-specific drivers. One of the drivers it has available is the PDO MYSQL driver, which allows it to interface with the MySQL server.
Sometimes people use the terms connector and driver interchangeably, this can be confusing. In the MySQL-related documentation the term driver is reserved for software that provides the database-specific part of a connector package.
In the PHP documentation you will come across another term — extension. The PHP code consists of a core, with optional extensions to the core functionality. PHP’s MySQL-related extensions, such as the mysqli extension, and the PDO MySQL driver extension, are implemented using the PHP extension framework.
An extension typically exposes an API to the PHP programmer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP programmer.
The PDO MySQL driver extension, for example, does not expose an API to the PHP programmer, but provides an interface to the PDO layer above it.
The terms API and extension should not be taken to mean the same thing, as an extension may not necessarily expose an API to the programmer.
What are the main PHP API offerings for using MySQL?
There are two main API options when considering connecting to a MySQL database server:
Each has its own advantages and disadvantages. The following discussion aims to give a brief introduction to the key aspects of each API.
What is PHP’s mysqli Extension?
The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.
- Object-oriented interface
- Support for Prepared Statements
- Support for Multiple Statements
- Support for Transactions
- Enhanced debugging capabilities
As well as the object-oriented interface the extension also provides a procedural interface.
The mysqli extension is built using the PHP extension framework, its source code is located in the directory ext/mysqli .
For further information on the mysqli extension, see MySQLi.
PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.
Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.
While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn’t allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL’s support for Multiple Statements.
PDO is implemented using the PHP extension framework, its source code is located in the directory ext/pdo .
For further information on PDO, see the PDO.
What is the PDO MYSQL driver?
The PDO MYSQL driver is not an API as such, at least from the PHP programmer’s perspective. In fact, the PDO MYSQL driver sits in the layer below PDO itself and provides MySQL-specific functionality. The programmer still calls the PDO API, but PDO uses the PDO MYSQL driver to carry out communication with the MySQL server.
The PDO MYSQL driver is one of several available PDO drivers. Other PDO drivers available include those for the Firebird and PostgreSQL database servers.
The PDO MYSQL driver is implemented using the PHP extension framework. Its source code is located in the directory ext/pdo_mysql . It does not expose an API to the PHP programmer.
For further information on the PDO MYSQL driver, see MySQL (PDO).
What is PHP’s MySQL Native Driver?
In order to communicate with the MySQL database server, mysqli and the PDO MYSQL driver each use a low-level library that implements the required protocol. In the past, the only available library was the MySQL Client Library, otherwise known as libmysqlclient .
However, the interface presented by libmysqlclient was not optimized for communication with PHP applications, as libmysqlclient was originally designed with C applications in mind. For this reason, the MySQL Native Driver, mysqlnd , was developed as an alternative to libmysqlclient for PHP applications.
Both, the mysqli extension and the PDO MySQL driver can each be individually configured to use either libmysqlclient or mysqlnd . As mysqlnd is designed specifically to be utilised in the PHP system it has numerous memory and speed enhancements over libmysqlclient . You are strongly encouraged to take advantage of these improvements.
The MySQL Native Driver is implemented using the PHP extension framework. The source code is located in ext/mysqlnd . It does not expose an API to the PHP programmer.
The following table compares the functionality of the main methods of connecting to MySQL from PHP:
| PHP’s mysqli Extension | PDO (Using PDO MySQL Driver and MySQL Native Driver) | |
|---|---|---|
| PHP version introduced | 5.0 | 5.0 |
| MySQL development status | Active development | Active development |
| API supports Charsets | Yes | Yes |
| API supports server-side Prepared Statements | Yes | Yes |
| API supports client-side Prepared Statements | No | Yes |
| API supports Stored Procedures | Yes | Yes |
| API supports Multiple Statements | Yes | Most |
| Supports all MySQL 4.1+ functionality | Yes | Most |
Choosing a library
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.
Example #1 Configure commands for using mysqlnd or libmysqlclient
// Recommended, compiles with mysqlnd $ ./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd // Alternatively recommended, compiles with mysqlnd $ ./configure --with-mysqli --with-pdo-mysql // Not recommended, compiles with libmysqlclient $ ./configure --with-mysqli=/path/to/mysql_config --with-pdo-mysql=/path/to/mysql_config
Library feature comparison
It is recommended to use the mysqlnd library instead of the MySQL Client Server library (libmysqlclient). Both libraries are supported and constantly being improved.
| MySQL native driver (mysqlnd) | MySQL client server library ( libmysqlclient ) | |
|---|---|---|
| Part of the PHP distribution | Yes | No |
| PHP version introduced | 5.3.0 | N/A |
| License | PHP License 3.01 | Dual-License |
| Development status | Active | Active |
| Lifecycle | No end announced | No end announced |
| Compile default (for all MySQL extensions) | Yes | No |
| Compression protocol support | Yes | Yes |
| SSL support | Yes | Yes |
| Named pipe support | Yes | Yes |
| Non-blocking, asynchronous queries | Yes | No |
| Performance statistics | Yes | No |
| LOAD LOCAL INFILE respects the open_basedir directive | Yes | No |
| Uses PHP’s native memory management system (e.g., follows PHP memory limits) | Yes | No |
| Return numeric column as double (COM_QUERY) | Yes | No |
| Return numeric column as string (COM_QUERY) | Yes | Yes |
| Plugin API | Yes | Limited |
| Automatic reconnect | No | Optional |
User Contributed Notes
Mysql client libraries php
The mysqli extension was introduced with PHP version 5.0.0. The MySQL Native Driver was included in PHP version 5.3.0.
Installation on Linux
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. Check the package manager that comes with your chosen distribution for availability.
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.
Alternatively, you can compile this extension yourself. Building PHP from source allows you to specify the MySQL extensions you want to use, as well as your choice of client library for each extension.
The MySQL Native Driver is the recommended client library option, as it results in improved performance and gives access to features not available when using the MySQL Client Library. Refer to What is PHP’s MySQL Native Driver? for a brief overview of the advantages of MySQL Native Driver.
The /path/to/mysql_config represents the location of the mysql_config program that comes with MySQL Server.
| PHP Version | Default | Configure Options: mysqlnd | Configure Options: libmysqlclient | Changelog |
|---|---|---|---|---|
| 5.4.x and above | mysqlnd | —with-mysqli | —with-mysqli=/path/to/mysql_config | mysqlnd is the default |
| 5.3.x | libmysqlclient | —with-mysqli=mysqlnd | —with-mysqli=/path/to/mysql_config | mysqlnd is supported |
| 5.0.x, 5.1.x, 5.2.x | libmysqlclient | Not Available | —with-mysqli=/path/to/mysql_config | mysqlnd is not supported |
Note that it is possible to freely mix MySQL extensions and client libraries. For example, it is possible to enable the MySQL extension to use the MySQL Client Library (libmysqlclient), while configuring the mysqli extension to use the MySQL Native Driver. However, all permutations of extension and client library are possible.
Installation on Windows Systems
On Windows, php_mysqli.dll DLL must be enabled in php.ini .
As with enabling any PHP extension (such as php_mysqli.dll ), the PHP directive extension_dir should be set to the directory where the PHP extensions are located. See also the Manual Windows Installation Instructions. An example extension_dir value is c:\php\ext .
Note:
If when starting the web server an error similar to the following occurs: «Unable to load dynamic library ‘./php_mysqli.dll'» , this is because php_mysqli.dll cannot be found by the system.