Overview

This section provides a brief introduction to the options available when you need to interact with the Mysql database during PHP application development.

What is API?

An application programming interface (abbreviation for Application Programming Interface) defines the classes, methods, functions, variables, etc. that your application needs to call in order to complete specific tasks. The APIs required when a PHP application needs to interact with a database are usually exposed (called by the terminal PHP programmer) through PHP extensions.

APIs can be procedural or object-oriented. For procedural APIs, we complete tasks by calling functions, while for object-oriented APIs, we instantiate classes and call methods on the objects obtained after instantiation. Of the two interfaces, the latter is usually preferred because it is more modern and gives us a good code structure.

When building a PHP application that needs to connect to a MySQL server, there are several APIs to choose from. This document discusses the available APIs and discusses how to choose the best solution for your application.

What is a connector?

In the MySQL documentation, the term connector is explained as "a piece of software code that allows your application to connect to the MySQL database server". MySQL provides connectors for many languages, including PHP.

When your PHP application needs to interact with a database server, you need to write PHP code to complete a series of activities such as "connecting to the database server", "querying the database" and other database-related functions. Your PHP application will use the software that provides these APIs, or some intermediate library when needed, to handle the interaction between your application and the database server. This type of software is often thought of as a connector, which allows your reference to connect to a database server.

What is a driver?

A driver is a piece of software code designed to interact with a specific type of database server. The driver may call some libraries, such as the MySQL client library or the MySQL Native driver library. These libraries implement the low-level protocols for interacting with the MySQL database server.

By way of example, the PDO (abbreviation for PHP Database Object) database abstraction layer can use a variety of database-specific drivers. One of the drivers is the PDO MYSQL driver, which is the interface with the MySQL server.

Sometimes people use the terms connector and driver interchangeably. In MySQL related documentation the term "

driver" is used as a connector package that provides software code for a specific part of the database.

What is an extension?

You will also find many other

extensions in the PHP documentation. PHP code is composed of a core and some optional extensions that make up the core functionality. MySQL related extensions of PHP, such as mysqli, mysql are all implemented based on the PHP extension framework.

A typical function of extensions is to expose an API to PHP programmers, allowing extended functions to be used by programmers. Of course, there are also some extensions developed based on the PHP extension framework that do not expose API interfaces to PHP programmers.

For example, the PDO MySQL driver extension does not expose the API interface to PHP programmers, but provides an interface to the PDO layer above it.

The terms API and extension do not describe the same thing, because extensions may not need to expose an API interface to programmers.

What are the main APIs provided in PHP for MySQL?

When considering connecting to a MySQL database server, there are three main APIs to choose from:

MySQL extension for PHP
mysqli extension for PHP
PHP Data Object (PDO)

All three have their own advantages and disadvantages. The following discussion is intended to give a brief introduction to the key aspects of each API.

What is the MySQL extension for PHP?

This was an early extension designed and developed to allow PHP applications to interact with MySQL databases. The mysql extension provides a procedure-oriented interface and is designed for MySQL 4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or newer database servers, it does not support some features provided by later MySQL servers.

Note:

If you are using MySQL 4.1.3 or newer server version, strongly recommend that you use the mysqli extension instead.

The source code of the mysql extension is in the PHP extension directory ext/mysql.

For more information on the mysql extension, see MySQL.

What is the mysqli extension for PHP?

The mysqli extension, which we sometimes call the MySQL enhancement extension, can be used to take advantage of new advanced features in MySQL 4.1.3 or later. The mysqli extension is included in PHP 5 and later.

The mysqli extension has a series of advantages. Compared with the mysql extension, the main improvements are:

Object-oriented interface
Prepared statement support (Annotation: Please refer to mysql related documents for prepare)
Multi-statement execution support
Transaction support
Enhanced debugging capabilities
Embedded service support

Note:

If you use MySQL 4.1.3 or newer, it is strongly recommended that you use this extension.

While providing an object-oriented interface, it also provides a process-oriented interface.

The mysqli extension is built using the PHP extension framework, and its source code is in ext/mysqli in the PHP source code directory.

For more information on the mysqli extension, see Mysqli.

What is PDO?

PHP data object is a database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the specific database server system type to be connected. In other words, if you use PDO's API, you can seamlessly switch database servers whenever needed, such as from Firebird to MySQL, with only a small amount of PHP code modifications.

Other examples of database abstraction layers include JDBC in Java applications and DBI in Perl.

Of course, PDO also has its own advancements, such as a clean, simple, and portable API. Its main disadvantage is that it restricts you from using all the advanced database features provided by the later MySQL server. For example, PDO does not allow the use of multi-statement execution supported by MySQL.

PDO is implemented based on the PHP extension framework, and its source code is under ext/pdo in the PHP source code directory.

For more information about PDO, see PDO.

What is PDO’s MySQL driver?

PDO's MySQL driver is not an API, at least from a PHP programmer's perspective. In fact, PDO's MySQL driver is at the lower level of PDO itself and provides specific MySQL functions. Programmers directly call PDO's API, and PDO uses PDO's MySQL driver to complete interaction with the MySQL server.

PDO's MySQL driver is one of many PDO drivers. Other available PDO drivers include Firebird, PostgreSQL, etc.

PDO's MySQL driver is implemented based on the PHP extension framework. Its source code is in ext/pdo_mysql in the PHP source code directory. It exposes no API to PHP programmers.

For more information on the PDO MySQL extension, see MySQL (PDO).

What is the MySQL Native driver for PHP?

In order to interact with the MySQL database server, the mysql extension, mysqli extension, and the PDO MySQL driver all use underlying libraries that implement the necessary protocols. Previously, the only libraries available were the MySQL client library and libmysql.

However, libmysql contains interfaces that are not optimized for application interaction with PHP, and libmysql was originally designed for C applications. For this reason, the MySQL Native driver mysqlnd, was developed as a modified version of libmysql for PHP applications.

mysql, mysqli and PDO Mysql driver can be configured separately using libmysql or mysqlnd. mysqlnd is a library specially designed for PHP systems. It has a great improvement in memory and speed compared to libmysql. I really hope you try these improvements.

Note:

The MySQL Native driver can only be used when the MySQL server version is 4.1.3 and later.

The MySQL Native driver is implemented based on the PHP extension framework. The source code is located under ext/mysqlnd in the PHP source code directory. It exposes no interface to PHP programmers.

Feature comparison

The following table compares the functionality of the three main MySQL connection methods in PHP:

	PHP的mysqli扩展	PDO (使用PDO MySQL驱动和MySQL Native驱动)	PHP的mysql扩展
引入的PHP版本	5.0	5.0	3.0之前
PHP5.x是否包含	是	是	是
MySQL开发状态	活跃	在PHP5.3中活跃	仅维护
在MySQL新项目中的建议使用程度	建议 - 首选	建议	不建议
API character set support	Yes	Yes	No
Support of server-side prepare statement	Yes	Yes	No
Support of client prepare statement	No	Yes	No
Stored procedure support	Yes	Yes	No
Multiple statement execution support	Yes	Most	No
Does it support all MySQL4.1 or above functions?

Differences and connections between the three main APIs when php connects to the MySQL database server: mysql, mysqli, pdo_PHP tutorial

Overview