Overview
From; https://www.php.net/manual/en/mysqli.overview.php 





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.




What is an API?
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.




What is a Connector?
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.




What is a Driver?
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.




What is an Extension?
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.

The mysqli extension has a number of benefits, the key enhancements over the
mysql extension being:


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.




What is PDO?
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.




Comparison of Features
The following table compares the functionality of the main methods of
connecting to MySQL from PHP:




Comparison of MySQL API options for 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






User Contributed Notes 2 notes




guatebus at dot gmail dot com

The text: "PDO does not allow you to use MySQL's support for Multiple Statements"
is outdated.

Since v5.3, PHP intoduced multiple statement support into PDO (by
PDO_MYSQLND driver replacing the previous PDO_MYSQL).





php-includer at gmail dot com

mysqli can be great in some circumstances but much work has been put into PHP
Portable Data Objects (PDO) which you should also consider when choosing a way
to connect to your database using php. For example, PDO supports MySQL with 
minimal performance hit and the code your write for it will support many other
databases with little or no changes. That said, the database connection code, 
even if you have to change a lot of it to use another database will be much
less work than coding your actual database data entry and report apps. When I
started creating PHP/MySQL apps years ago, I used php's native support for PHP
then moved to PEAR:DB and MDB/MDB2 and finally to wizzyweb which is basically
like "phpMyAdmin for Apps" to create apps as it automatically generates the PHP
PDO connection code and the application code. Sure, I could code it all from
scratch but I save about 90% of the time it used to take. The point is look at
the total amount of time you will save by using native code vs. an abstraction
layer. Most people find that programmer time is the most valuable part of the
equation so anything than can save programmer time should be heavily weighted.