Chapter 2 Overview of the MySQL PHP drivers

Copyright 1997-2018 the PHP Documentation Group.

2.1 Introduction

Abstract

Depending on the version of PHP, there are either two or three PHP APIs for accessing the MySQL database. PHP 5 users can choose between the deprecated mysql extension, mysqli, or PDO_MySQL. PHP 7 removes the mysql extension, leaving only the latter two options.

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.

2.2 Terminology overview

Copyright 1997-2018 the PHP Documentation Group.

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 mysql 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.

2.3 Choosing an API

Copyright 1997-2018 the PHP Documentation Group.

PHP offers three different APIs to connect to MySQL. Below we show the APIs provided by the mysql, mysqli, and PDO extensions. Each code snippet creates a connection to a MySQL server running on "example.com" using the username "user" and the password "password". And a query is run to greet the user.

Example 2.1 Comparing the three MySQL APIs


<?php
// mysqli
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("example.com", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);
?>


Recommended API

It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development, as it was deprecated in PHP 5.5.0 and was removed in PHP 7. A detailed feature comparison matrix is provided below. The overall performance of all three extensions is considered to be about the same. Although the performance of the extension contributes only a fraction of the total run time of a PHP web request. Often, the impact is as low as 0.1%.

Feature comparison

 ext/mysqliPDO_MySQLext/mysql
PHP version introduced5.05.12.0
Included with PHP 5.xYesYesYes
Included with PHP 7.xYesYesNo
Development statusActiveActiveMaintenance only in 5.x; removed in 7.x
LifecycleActiveActiveDeprecated in 5.x; removed in 7.x
Recommended for new projectsYesYesNo
OOP InterfaceYesYesNo
Procedural InterfaceYesNoYes
API supports non-blocking, asynchronous queries with mysqlndYesNoNo
Persistent ConnectionsYesYesYes
API supports CharsetsYesYesYes
API supports server-side Prepared StatementsYesYesNo
API supports client-side Prepared StatementsNoYesNo
API supports Stored ProceduresYesYesNo
API supports Multiple StatementsYesMostNo
API supports TransactionsYesYesNo
Transactions can be controlled with SQLYesYesYes
Supports all MySQL 5.1+ functionalityYesMostNo

2.4 Choosing a library

Copyright 1997-2018 the PHP Documentation Group.

The mysqli, PDO_MySQL and 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 since 5.3.0. 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 2.2 Configure commands for using mysqlnd or libmysqlclient


// Recommended, compiles with mysqlnd
$ ./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

// Alternatively recommended, compiles with mysqlnd as of PHP 5.4
$ ./configure --with-mysqli --with-pdo-mysql --with-mysql

// Not recommended, compiles with libmysqlclient
$ ./configure --with-mysqli=/path/to/mysql_config --with-pdo-mysql=/path/to/mysql_config --with-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 distributionYesNo
PHP version introduced5.3.0N/A
LicensePHP License 3.01Dual-License
Development statusActiveActive
LifecycleNo end announcedNo end announced
PHP 5.4 and above; compile default (for all MySQL extensions)YesNo
PHP 5.3; compile default (for all MySQL extensions)NoYes
Compression protocol supportYes (5.3.1+)Yes
SSL supportYes (5.3.3+)Yes
Named pipe supportYes (5.3.4+)Yes
Non-blocking, asynchronous queriesYesNo
Performance statisticsYesNo
LOAD LOCAL INFILE respects the open_basedir directiveYesNo
Uses PHP's native memory management system (e.g., follows PHP memory limits)YesNo
Return numeric column as double (COM_QUERY)YesNo
Return numeric column as string (COM_QUERY)YesYes
Plugin APIYesLimited
Read/Write splitting for MySQL ReplicationYes, with pluginNo
Load BalancingYes, with pluginNo
Fail overYes, with pluginNo
Lazy connectionsYes, with pluginNo
Query cachingYes, with pluginNo
Transparent query manipulations (E.g., auto-EXPLAIN or monitoring)Yes, with pluginNo
Automatic reconnectNoOptional

2.5 Concepts

Copyright 1997-2018 the PHP Documentation Group.

These concepts are specific to the MySQL drivers for PHP.

2.5.1 Buffered and Unbuffered queries

Copyright 1997-2018 the PHP Documentation Group.

Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once.

Note

When using libmysqlclient as library PHP's memory limit won't count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd the memory accounted for will include the full result set.

Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".

Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results.

Because buffered queries are the default, the examples below will demonstrate how to execute unbuffered queries with each API.

Example 2.3 Unbuffered query example: mysqli


<?php
$mysqli  = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

if ($uresult) {
   while ($row = $uresult->fetch_assoc()) {
       echo $row['Name'] . PHP_EOL;
   }
}
$uresult->close();
?>


Example 2.4 Unbuffered query example: pdo_mysql


<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
   while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
       echo $row['Name'] . PHP_EOL;
   }
}
?>


Example 2.5 Unbuffered query example: mysql


<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db   = mysql_select_db("world");

$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
   while ($row = mysql_fetch_assoc($uresult)) {
       echo $row['Name'] . PHP_EOL;
   }
}
?>


2.5.2 Character sets

Copyright 1997-2018 the PHP Documentation Group.

Ideally a proper character set will be set at the server level, and doing this is described within the Character Set Configuration section of the MySQL Server manual. Alternatively, each MySQL API offers a method to set the character set at runtime.

The character set and character escaping

The character set should be understood and defined, as it has an affect on every action, and includes security implications. For example, the escaping mechanism (e.g., mysqli_real_escape_string for mysqli, mysql_real_escape_string for mysql, and PDO::quote for PDO_MySQL) will adhere to this setting. It is important to realize that these functions will not use the character set that is defined with a query, so for example the following will not have an effect on them:

Example 2.6 Problems with setting the character set with SQL


<?php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

// Will NOT affect $mysqli->real_escape_string();
$mysqli->query("SET NAMES utf8");

// Will NOT affect $mysqli->real_escape_string();
$mysqli->query("SET CHARACTER SET utf8");

// But, this will affect $mysqli->real_escape_string();
$mysqli->set_charset('utf8');

// But, this will NOT affect it (utf-8 vs utf8) -- don't use dashes here
$mysqli->set_charset('utf-8');

?>


Below are examples that demonstrate how to properly alter the character set at runtime using each API.

Possible UTF-8 confusion

Because character set names in MySQL do not contain dashes, the string "utf8" is valid in MySQL to set the character set to UTF-8. The string "utf-8" is not valid, as using "utf-8" will fail to change the character set.

Example 2.7 Setting the character set example: mysqli


<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

printf("Initial character set: %s\n", $mysqli->character_set_name());

if (!$mysqli->set_charset('utf8')) {
    printf("Error loading character set utf8: %s\n", $mysqli->error);
    exit;
}

echo "New character set information:\n";
print_r( $mysqli->get_charset() );

?>


Example 2.8 Setting the character set example: pdo_mysql

Note: This only works as of PHP 5.3.6.


<?php
$pdo = new PDO("mysql:host=localhost;dbname=world;charset=utf8", 'my_user', 'my_pass');
?>


Example 2.9 Setting the character set example: mysql


<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db   = mysql_select_db("world");

echo 'Initial character set: ' .  mysql_client_encoding($conn) . "\n";

if (!mysql_set_charset('utf8', $conn)) {
    echo "Error: Unable to set the character set.\n";
    exit;
}

echo 'Your current character set is: ' .  mysql_client_encoding($conn);
?>


';