Developing MySQL Database Applications With PHP Part 1:
Using the MySQL Improved Extension, mysqli

Print-friendly Version

This series of tutorials shows the essential steps involved in installing PHP with MySQL extensions mysql, mysqli, and pdo_mysql, and with support for the MySQL native driver for PHP, mysqlnd. The tutorials also provide simple examples to connect, insert, and retrieve data from a MySQL database. Because the focus is on database connectivity from a PHP script, these tutorials assume that some kind of MySQL database is already up and accessible from the client machine.

Application developers who are new to the development of MySQL database applications with PHP are the target audience of this tutorial.

To find out more about using MySQL with PHP, see all tutorials in the series:

• Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli -- Describes mysqli, the MySQL Improved Extension that is the successor to the mysql extension. This extension gives you access to all MySQL server features provided by MySQL 4.1 and above; for example, support for Prepared Statements, Character Sets, and Stored Procedures, which are not available with mysql.
• Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql -- Describes the original extension designed to develop PHP applications that interact with a MySQL database. Although the mysql extension is compatible with MySQL 4.1 and above, it does not support the extra functionality that these versions provide.
• Developing MySQL Database Applications With PHP Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql -- Describes the pdo_mysql driver, which implements the PHP Data Objects (PDO) interface to access the MySQL Server from PHP. Due to its portable interfaces, PDO is useful when you need to build applications capable of talking to different database systems; however, PDO does not allow you to use all of the features that are available in the latest versions of MySQL.
• Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd -- Describes the MySQL native driver for PHP, mysqlnd, which is an alternative to the MySQL client library, libmysql, for connecting from PHP 5 and PHP 6 to MySQL Server 4.1 or later. The mysqlnd driver is tightly integrated into PHP starting with the release of PHP 5.3, thus mysqlnd eliminates the dependency on MySQL client programming support when the database extensions and the database driver are built with support for mysqlnd.

• Access MySQL With PHP Database Extensions
• Major PHP API Offerings for MySQL
• Which PHP Database Extension to Use?
• Create the City Table in the test Database for Code Examples
• Use the MySQL Improved Extension, mysqli
• Install PHP With the mysqli Extension
• Test the MySQL Database Connectivity Using the PHP mysqli Extension
• Work With Prepared Statements
• For More Information

The PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL database extensions, such as the mysql, mysqli, and PHP Data Objects (PDO) extensions, are implemented using the PHP extension framework. The extensions typically expose an API to the PHP programmer to allow their facilities to be used programmatically. However, some extensions that use the PHP extension framework do not expose an API to the PHP programmer, but provide an interface to the layer above it instead. This is the case for the PDO MySQL driver extension. Such an extension can be referred as a database abstraction layer.

The PHP scripting language features three different extensions (and thereby APIs) to connect to the MySQL Server:

• PHP 2+: MySQL extension, ext/mysql
• PHP 5+: MySQL improved extension, ext/mysqli
• PHP 5+: PHP Data Objects (PDO), a database abstraction layer, with MySQL driver, PDO_MySQL

All these extensions are part of the PHP code base.

The mysql extension is the original extension designed to develop PHP applications that interact with a MySQL database. Although the mysql extension is compatible with MySQL 4.1 and greater, it does not support the extra functionality that these versions provide. It is not recommended to use this extension for new application developments. If you use it, you cannot take full advantage of the advanced features that are available in the latest versions of MySQL.

The MySQL Improved Extension, mysqli, is the successor to the mysql extension, and is the current flagship extension from Sun-MySQL AB. The ext/mysqli extension provides the ability to access the functionality provided by MySQL 4.1 and above. For example, it includes support for Prepared Statements, Character Sets, and Stored Procedures, which are not available with ext/mysql. The ext/mysqli extension gives you access to all MySQL server features.

This tutorial explains the MySQL improved extension, ext/mysqli, in detail with plenty of simple examples.

The PDO_MySQL driver implements the PHP Data Objects (PDO) interface to access the MySQL Server from PHP. PDO is a data-access abstraction layer that was introduced with PHP 5.0. Due to its portable interfaces, PDO is useful when you need to build applications capable of talking to different database systems. On the down side, PDO does not allow you to use all of the features that are available in the latest versions of MySQL. For example, the PDO API has no direct support for MySQL's Multiple Statements feature.

MySQL native driver for PHP, mysqlnd, is an alternative to the MySQL client library, libmysql, for connecting from PHP 5 and PHP 6 to MySQL Server 4.1 or later.

Internally, all three extensions can use either the MySQL client library, libmysql, or the MySQL native driver for PHP, mysqlnd. You can choose at compile time if ext/mysql, ext/mysqli, and PDO_MySQL make use of mysqlnd or libmysql.

For accessing MySQL from PHP scripts, the general recommendation is to use ext/mysqli built with mysqlnd. PDO is the second choice, and ext/mysql is the last choice.

In addition, it is strongly recommended to read the extensive documentation that is available at php.net and dev.mysql.com before choosing an appropriate extension for your application.

Also check the comparison of features among those three extensions at php.net.

In addition to the extensions discussed in this document, there are plenty of other extensions and database abstraction libraries, like ADOdb and MDB2, that are available for PHP to connect to database servers like MySQL and Oracle. However discussion about those external extensions of PHP is beyond the scope of this series.

The code samples in this tutorial try to retrieve the data from the City table in the MySQL test database. The table structure and the data from the City table are shown here by using the mysql client. The MySQL server is running on the default port 3306. You can create this table and populate the data so that you can use it in the tutorials in this article series.

bash# mysql -u root -p
Enter password: admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.24-rc-standard Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Database changed

mysql> DESCRIBE City;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| CityName | varchar(30) | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
1 row in set (0.07 sec)

mysql> SELECT * FROM City;

+--------------------+
| CityName           |
+--------------------+
| Hyderabad, India   | 
| San Francisco, USA | 
| Sydney, Australia  | 
+--------------------+
3 rows in set (0.17 sec)

The MySQL improved extension, mysqli, was developed to take advantage of the new features introduced in MySQL 4.1.3 and later versions. The ext/mysqli extension is integrated in all versions of the PHP code base starting with version 5.0. In addition to the new features that have been added as a result of MySQL's evolution into a full-featured DBMS, ext/mysqli offers all of the functionality provided by its predecessor, ext/mysql. In other words, ext/mysqli is a kind of superset to ext/mysql.

The mysqli extension fully supports the following:

• Authentication protocol used in MySQL 5.x
• Prepared Statements
• Multiple Statements APIs
• ACID (Atomicity, Consistency, Isolation, Durability) transactions
• Enhanced debugging capabilities
• Embedded server support
• Advanced, object-oriented programming interface

Besides the object-oriented interface, this extension also provides a procedural interface that is very similar to the interface for ext/mysql.

Users building PHP 5.x from the source code should use the --with-mysqli=mysql_config_path/mysql_config configuration option to include the improved MySQL support, where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1. Then ext/mysqli uses the MySQL client library, libmysql, to talk to the MySQL database server.

Alternatively, if you wish to use the MySQL native driver for PHP (mysqlnd), in place of the MySQL client library (libmysql) to communicate with the MySQL server, see the installation instructions in the tutorial Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd.

Sun Solaris OS users can download the optimized PHP 5 packages for the Solaris OS from either the Cool Stack or from the Web Stack. Once installed, make sure that the mysqli extension is enabled by uncommenting the line extension="mysqli.so" in the PHP configuration file, php.ini. The /opt/coolstack/php5/lib directory contains the php.ini configuration file in the Cool Stack installation, whereas the /usr/php5/<version> directory contains the php.ini in the Web Stack PHP installation.

bash# grep -w mysqli.so php.ini
extension="mysqli.so"

Check the PHP Manual for the installation steps for platforms other than Solaris. Also check the Runtime Configuration to configure php.ini, which will affect the behavior of the improved MySQL functions.

The following sample PHP script shows how to connect to a MySQL database running on the same host, using the mysqli extension. The code sample connects to the MySQL database test by using the ext/mysqli API, sends out a query to retrieve all the rows from the table City, extracts the data from the result set, and finally displays the data on the standard output. To minimize the dependencies, this script is shown as a stand-alone script rather than a PHP page that can be accessed over the web server.

The sample code is provided for the purpose of demonstration only; it is not meant to indicate that readers should adopt a particular style of coding. To keep it simple, the sample code assumes that the user always provides well-formed input, hence there is no explicit error checking code in the following example. Use discretion in reusing the sample code.

bash# cat PHPmysqliClient.php
<?php

       $host="localhost";
	$port=3306;
	$socket="/tmp/mysql.sock";
	$user="root";
	$password="admin";
	$dbname="test";

	/* create a connection object which is not connected */
	$cxn = new mysqli();
	$cxn -> init();

	/* set connection options */
	$cxn -> options (MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
	$cxn -> options (MYSQLI_OPT_CONNECT_TIMEOUT, 5);

	/* connect to MySQL server */
	$cxn -> real_connect ($host, $user, $password, $dbname, $port, $socket)
		or die ('Could not connect to the database server : ' . $cxn -> connect_error);

	$query = "SELECT * FROM City";
	
	/* execute the query */
	if ($cxn -> real_query ($query)) {

		/* initiate the result set retrieval */
		if ($result = $cxn -> store_result()) {

			/* find the number of rows in the result set */
			$nrows = $result -> num_rows;

			echo "\nRetrieved $nrows row(s).\n\n";
			echo "CityName\n--------\n";

			while ($row = $result -> fetch_assoc()) {
				$data1 = $row["CityName"];
				echo "$data1\n";
			}

		}

		/* close the result set */
		$result -> close();

	} else {
       		echo "\nreal_query() failed with error : " . $cxn -> error . "\n";
        }

	/* close the database connection */
	$cxn -> close();
?>

bash# export LD_DEBUG=symbols,libs

bash# export LD_DEBUG_OUTPUT=lddebug.log

bash# php PHPmysqliClient.php

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

bash# unset LD_DEBUG LD_DEBUG_OUTPUT

bash# grep mysql_fetch_row lddebug.log.11248 | grep libmysql
11248: 1: symbol=mysql_fetch_row;  lookup in file=/opt/coolstack/mysql_32bit/lib/mysql/libmysqlclient.so.16  [ ELF ]

Some of the important steps in the above code sample are explained below. As mentioned earlier, ext/mysqli provides the interface for procedural as well as object-oriented style programming. Procedural interfaces in ext/mysqli are almost identical to their counterparts in ext/mysql. Most of the mysql_XX() functions have equivalent mysqli_XX() versions. For example, ext/mysqli has mysqli_connect() in the same lines as that of mysql extension's mysql_connect() method. The sample script in this section focuses on the object-oriented interfaces, whereas the sample script in the tutorial Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql illustrates the use of procedural style interfaces.

Call the mysqli:init() method of the mysqli object to obtain a database connection handle to use with subsequent mysqli::real_connect(). The init() method creates a database connection object but doesn't establish a connection to the MySQL server. In order to establish the actual database connection, one must call the real_connect() method against the database connection object that was returned by the init() method. Until real_connect() is called, all the calls to the mysqli methods except the mysqli::options() fail.

Calling mysqli:init() followed by a call to mysqli::real_connect() can be simplified by calling mysqli object's constructor with all the necessary parameters to open a connection to the MySQL server.

The mysqli::options() method can be used to to set extra connect options like the connection timeout, auto-commit mode, and so on, that can affect the behavior of a database connection.

The following code fragment in the sample PHP script establishes the connection to the MySQL database engine running on the local host on default port 3306, sets the default database to test, disables the auto-commit mode, and sets the connection timeout to 5 seconds.

$host="localhost";
$port=3306;
$socket="/tmp/mysql.sock";
$user="root";
$password="admin";
$dbname="test";

$cxn = new mysqli();
$cxn->init();

$cxn->options (MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
$cxn->options (MYSQLI_OPT_CONNECT_TIMEOUT, 5);

$cxn->real_connect ($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());

Another way to turn on or turn off the auto-commit is by calling the mysqli::autocommit() method with an argument of TRUE or FALSE against the database connection object.

For example:

/* turn off auto-commit */
$cxn->autocommit(FALSE);

When the auto-commit is turned off, you must commit or roll back the transactions by explicitly calling mysqli::commit() or mysqli::rollback() methods respectively.

Read the man page for the mysqli::real_connect() method for the default values to the input parameters. Also check the note about the safe mode in the tutorial Developing MySQL Database Applications With PHP Part 2: Using the MySQL Extension, mysql.

Notes:

• If the host is localhost, then the port number is ignored and 3306 is assumed by default. If the MySQL server is running on any port other than the default port 3306, use the IP address 127.0.0.1 in place of localhost for the $host argument.
• The mysqli extension built with libmysql support does not support persistent connections. Consider using the MySQL native driver for PHP, mysqlnd, if you need the mysqli support for persistent connections. For more information, see the tutorial Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd.

The method mysqli::real_query() executes a single query against the selected database whose result can then be retrieved using the mysqli::store_result() or mysqli::use_result() functions.

The following code fragment in the sample PHP script executes the SQL query.

$query = "SELECT * FROM City";
$cxn->real_query ($query)

To execute multiple queries (also called Multiple Statements), which are concatenated by a semicolon, use the mysqli::multi_query() method. Check the man page of mysqli::multi_query() for examples.

Call mysqli::use_result() to initiate the retrieval of the result set from the last query executed using the mysqli::real_query() function. The method use_result() returns the result as an unbuffered set, meaning that the set is retrieved on an as-needed basis from the server. Unbuffered result sets increase performance for large result sets, but disallow the opportunity to do various things with the result set, such as immediately determine how many rows have been returned by the server, or move to a particular row offset using functions such as mysqli_result::data_seek(). When you are trying to retrieve a very large number of rows, this method might require less memory and produce a faster response time. On the other hand, if a lot of processing on the client side is performed, it might lead to table lockups and prevent other threads from updating the tables from which the data is being fetched. Use your discretion in choosing this method.

Call mysqli::store_result() if you need the ability to move to any row within the result set randomly. Using store_result() returns the result from the last query executed using the mysqli::real_query() function as a buffered set, meaning the entire set is made available for navigation immediately. It is recommended to use store_result() if a lot of processing on the client side is performed because this releases the locks as soon as the result set is transferred to the client. It is good practice to free the memory used by the result of a query by calling the mysqli_result::free() or mysqli_result::close() function after transferring large result sets using the store_result() method.

Either mysqli::use_result() or mysqli::store_result() must be called before the results of a query can be retrieved, and to prevent the next query on that database connection from failing.

The extension mysqli has multiple methods to retrieve the data from a result set. Because the mysqli_result::fetch_assoc() method is used in the sample PHP script, we will focus only on the fetch_assoc() method in this section. For the rest of the mysqli_result::fetch_XX() methods, check the MySQLi_Result class.

The mysqli_result::fetch_assoc() method returns an associative array of strings representing the fetched row in the result set, where each key in the array represents the name of one of the result set's columns, or NULL if there are no more rows in the result set. To retrieve all the rows in the result set, call fetch_assoc() iteratively in a loop.

The following code fragment in the sample PHP script retrieves all the data from the result set and displays it on the standard output.

if ($result = $cxn->store_result()) {

	    echo "CityName\n--------\n";

	    while ($row = $result->fetch_assoc()) {
		        $data1 = $row["CityName"];
		        echo "$data1\n";
	    } // while
} // if

Once the required data has been retrieved from the result set, it is a recommended practice to free up the memory that was holding the result set by explicitly calling any of the following methods:

• mysqli_result::free()
• mysqli_result::close()
• mysqli_result::free_result()

Sample code frees up the memory associated with the result set by closing the result set at the end.

$result->close();

If the result set is not closed explicitly by calling any of the three methods mentioned above, all the associated result memory is automatically freed at the end of the script's execution. However if you want to use the existing database connection to execute more queries, make sure to explicitly close the result set by calling the mysqli_result::free_result() method.

Finally, close the database connection by calling the mysqli::close() method. The close() method closes the non-persistent connection to the MySQL server that is associated with the specified connection handle.

The following statement in the sample PHP script closes the database connection.

$cxn->close();

The LD_DEBUG* lines were added in the output to show that the MySQL client library is being used by the ext/mysqli.

Before concluding the discussion on ext/mysqli, let's look at the support for the prepared statements with a few examples.

MySQL 4.1 introduced prepared statements to accomplish the task of executing a query repeatedly, albeit with different parameters in each iteration. Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. However note that even though prepared statements can improve the security, it is still the responsibility of the application developer to guard against security attacks and to sanitize the input before submitting it to the database for processing.

Reduced query parsing due to the prepared statements can lead to significant performance improvements in the MySQL server.

The MySQL client/server protocol supports two methods of sending the database results to the client: as text and as binary. The text protocol always converts the data into strings before sending them across the network, and the server decodes the strings into appropriate data types. Unlike the text protocol, the binary protocol avoids converting the data into strings wherever possible. The binary protocol is used only with the prepared statements. Based on the data being sent over the network, the binary protocol that prepared statements use can reduce the CPU and network overhead by eliminating the encoding and decoding of the strings into the correct data types at the client and the server.

The following statements can be used as prepared statements: CALL, CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. See the latest MySQL Reference Manual to check the support for additional statements.

There are two types of prepared statements: bound-parameter prepared statements, and bound-result prepared statements. Bound-parameter prepared statements allow you to store a query statement on the MySQL server with only the changing data being repeatedly sent to the server and integrated into the query for execution. Similarly, bound-result prepared statements bind the results from a query to a set of PHP variables.

You can use the mysqli_stmt::bind_param() method of ext/mysqli to bind application variables to a prepared statement as parameters and mysqli_stmt::bind_result() to bind the results from a prepared statement to a set of PHP application variables.

The following example uses the bound-parameter as well as bound-result prepared statements to insert a couple of records into the City table of the test database, and then retrieves the result set by binding the PHP variable $city to the CityName field in the SQL statement.

bash# cat PHPmysqliPreparedStmtsClient.php

<?php

	/* create a new server connection */
	$cxn = new mysqli("localhost", "root", "admin", "test")
		or die ('Could not connect to the database server' . mysqli_connect_error());

	/* create the query and the corresponding placeholders */
	$sql = 'INSERT INTO City VALUES(?)';

	/* create a statement object */
	$stmt = $cxn->stmt_init();

	/* prepare the statement for execution */
	$stmt->prepare($sql);

	/* bind the parameters */
	$stmt->bind_param('s', $city);

	/* assign value to the $city parameter */
	$city = 'London, UK';
	
	/* execute the prepared statement */
	if ($stmt->execute()) {
		echo "\nSuccessfully inserted " . $city . " into the table, City.";
	}

	/* assign another value to the $city parameter */
	$city = 'Paris, France';
	
	/* execute the prepared statement one more time*/
	if ($stmt->execute()) {
		echo "\nSuccessfully inserted " . $city . " into the table, City.\n";
	}

	/*  reclaim the statement resources */
	$stmt->close();

	/* create the query and the corresponding placeholders */
	$sql = 'SELECT CityName FROM City';

	/* create a statement object */
	$stmt = $cxn->stmt_init();

	/* prepare the statement for execution */
	$stmt->prepare($sql);

	/* execute the prepared statement */
	$stmt->execute();

	/* store the result */
    	$stmt->store_result();

	echo "\nRetrieved " . $stmt->num_rows . " row(s).\n";

	/* bind the result parameters */
    	$stmt->bind_result($city);

	echo "\nCityName\n--------\n";

	/* loop through the results and display the data */
	while($stmt->fetch()) {
		echo $city . "\n";
	}

	/*  reclaim the statement resources */
	$stmt->close();

	/* close the database connection */
	$cxn->close();

?>

bash# php PHPmysqliPreparedStmtsClient.php

Successfully inserted London, UK into the table, City.
Successfully inserted Paris, France into the table, City.

Retrieved 5 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia
Paris, France
London, UK

Some of the important steps in the above code sample are explained below.

Instantiating the mysqli class via its constructor establishes a usable database connection to the MySQL server.

The mysqli::stmt_init() method initializes a statement and returns an object of type mysqli_stmt for use with mysqli_stmt::prepare().

Regardless of whether you are using the bound-parameter or bound-result prepared statement, first you need to prepare the statement for execution by using the mysqli_stmt::prepare() method.

Call the mysqli_stmt::bind_param() method to bind variable names to corresponding fields.

bool mysqli_stmt::bind_param (string types, mixed &var1 [, mixed &varN])

where the types parameter represents the data types of each respective variable to follow (represented by &var1, ... &varN in the syntax). At present, four type codes are supported:

• i for all INTEGER types
• d for the DOUBLE and FLOAT types
• b for the BLOB types
• s for all other types including strings

For example, if there are two variables and if the first variable is of type string and the second variable is of type integer, use the string "si" as the input for the types parameter.

The method mysqli_stmt::execute() executes a prepared statement.

The method mysqli_stmt::store_result() returns the result set from the prepared statement as a buffered set, meaning the entire set is made available for navigation immediately. Check the notes around the mysqli::store_result() method for the advantages and disadvantages of using buffered result sets.

The method mysqli_stmt::bind_result() binds columns in the prepared statement result set to PHP variables. In the above example, when mysqli_stmt::fetch() is called to fetch the data, the MySQL client/server protocol places the data for the bound columns into the specified variables $city.

Using mysqli_stmt::close() closes a prepared statement, and mysqli::close() closes the database connection.

Check the MySQLi Extension Function Summary page at PHP.net for the rest of the API that isn't discussed in this tutorial.

• It is strongly recommended to read the extensive documentation that is available at php.net and dev.mysql.com before choosing an appropriate extension for your application.
• Also check the comparison of features among those three extensions at php.net.
• PHP Manual
• PHP Database Extensions
• MySQL 5.1 Reference Manual
• Beginning PHP and MySQL: From Novice to Professional, W. Jason Gilmore
• Ulf Wendel's Blog: Internet Super Hero

Ulf Wendel, Sun | MySQL AB