PHP connection to MySQL database server three major api:mysql,mysqli,pdo differences and links _php Tutorials

Source: Internet
Author: User
Tags php mysql php mysql extension


This section provides a brief introduction to the choices that are available when you need to interact with the MySQL database during the PHP application development process.

What is an API?

An application interface (application programming interface abbreviation) that defines the classes, methods, functions, variables, and so on everything in your application that needs to be called in order to complete a particular task. The API required for PHP applications to interact with the database is usually exposed via PHP extensions (called to the terminal PHP programmer).

The API can be either process-oriented or object-oriented. For process-oriented APIs, we perform tasks by calling functions, and for object-oriented APIs, we instantiate classes and invoke methods on objects that are obtained after instantiation. For both interfaces, the latter is usually preferred because it is more modern and provides us with a good code structure.

There are several APIs to choose from when building a PHP application that needs to be connected to the MySQL server. This document discusses these 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 interpreted as "a piece of software code that allows your app to connect to the MySQL database server." MySQL provides a number of language connectors, 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 "Connect to the database server", "Query Database", and other database-related functions. Your PHP application will use the software that provides these APIs, or use some intermediate libraries when needed to handle the interaction between your application and the database server. This software is often considered a connector that allows your references to connect to the database server.

What is a driver?

A driver is a piece of software code designed to interact with a particular 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 underlying protocol for interacting with the MySQL database server.

The database abstraction layer can be driven by a number of specific databases using the example PDO (abbreviated PHP object). One driver is the PDO MySQL driver, which is the interface to the MySQL server.

Sometimes people will use the two terms of the connector and drive without distinction. The term "driver" in a MySQL-related document is used as a software code that provides a specific database section in a connector package.

What is an extension?

You will also find many other extensions in the PHP documentation. PHP code is made up of a core, and some optional extensions make up the core functionality. MySQL-related extensions for PHP, such as mysqli, are implemented based on the PHP extension framework.

An extension of a typical function is to expose an API to a PHP programmer, allowing the extension of its own functionality to be used by programmers. Of course, there are also a subset of extensions that are developed based on the PHP extension framework that will not expose the API interface to PHP programmers.

For example, the PDO MySQL driver extension does not expose the API interface to a PHP programmer, but it provides an interface to the PDO layer on its upper level.

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

What are the main APIs available in PHP for MySQL?

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

    • MySQL Extension for PHP

    • PHP mysqli Extensions

    • PHP Data Objects (PDO)

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

What is the MySQL extension for PHP?

This is a design development that allows PHP applications to interact with the MySQL database in an early extension. The MySQL extension provides a process-oriented interface and is designed for MySQL4.1.3 or earlier versions. Therefore, although this extension can interact with MySQL4.1.3 or the updated database server, it does not support some of the features provided by the late MySQL server.


If you are using MySQL4.1.3 or an updated version of the server, it is strongly recommended that you use the mysqli extension instead.

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

For more information on MySQL extensions, see MySQL.

What is the php mysqli extension?

mysqli Extensions, which we sometimes call MySQL enhanced extensions, can be used for new advanced features in MySQL4.1.3 or later versions. The mysqli extension is included in PHP 5 and later.

The mysqli extension has a number of advantages, as compared to the MySQL extension, the main improvements are:

  • Object-oriented interface

  • Prepared statement support (see MySQL related documentation for prepare)

  • Multi-statement execution support

  • Transaction support

  • Enhanced debugging capabilities

  • Embedded service Support


If you use MySQL4.1.3 or newer versions, it is highly recommended that you use this extension.

It provides a process-oriented interface while providing an object-oriented interface.

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

For more information about the mysqli extension, see mysqli.

What is PDO?

PHP Data Objects are a Database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the type of database server system you want to connect to. In other words, if you use the PDO API, you can seamlessly switch the database server whenever you need it, such as from Firebird to MySQL, just to modify very little PHP code.

Examples of other database abstraction layers include JDBC in Java applications and DBI in Perl.

Of course, PDO also has its own advancement, such as a clean, simple, portable API, and its main drawback is that you cannot use the late MySQL server to provide all the database advanced features. For example, PDO does not allow multi-statement execution with MySQL support.

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

For more information about PDO, see PDO.

What is the PDO MySQL drive?

The MySQL driver for PDO is not a set of APIs, at least from a PHP programmer's point of view. In fact, the PDO MySQL driver is in the PDO's own lower layer, providing specific MySQL functionality. The programmer calls the PDO API directly, and PDO uses the PDO MySQL driver to complete the interaction with the MySQL server side.

The MySQL driver for PDO is one of the many PDO drivers. Other available PDO drivers include firebird,postgresql and more.

The MySQL driver for PDO is based on the PHP extension framework. It's source code in the PHP source directory ext/pdo_mysql . It does not expose the API to PHP programmers.

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

What is the MySQL Native driver for PHP?

To interact with the MySQL database server,mysql extensions,mysqli extensions, and PDO MySQL drivers use the underlying libraries that implement the necessary protocols. Previously, the available libraries were only the MySQL client library and Libmysql.

However,Libmysql contains interfaces that are not optimized for interaction with PHP applications, andLibmysql was earlier designed for C applications. For this reason, MySQL native driver mysqlnd, as a modified version of Libmysql for PHP applications, was developed.

MySQL,mysqli , and PDO MySQL drivers can be configured to use libmysql or mysqlndrespectively. Mysqlnd , a library designed for use in PHP systems, has a much higher memory and speed than libmysql . I really want you to try these boosts.


MySQL native driver can only be used on MySQL server version for 4.1.3 and later versions.

MySQL native drivers are implemented based on the PHP extension framework. The source code is located under the PHP source directory ext/mysqlnd . It does not expose the interface to PHP programmers.

Feature comparison

The following table compares the features of the three main MySQL connection modes in PHP:

php mysqli extension pdo (using PDO mysql driver and MySQL native driver) php mysql extension
introduced PHP version 5.0 5.0 3.0 before
php5.x contains
mysql development status active active in PHP5.3
Recommended use in MySQL new project Recommendation-Preferred Suggestions not recommended
api character set supports no
support for server-side prepare statements no
client Prepare statement support case no no
stored procedure support no
multi-statement execution support most no
Do you support all MySQL4.1 features above true techarticle Overview This section provides a brief introduction to the choices that are available when you need to interact with the MySQL database during the PHP application development process. What is an API? An application interface (Application P ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.