MySQLi
The MySQLi Extension is a relational database driver used in the PHP scripting language to provide an interface with MySQL protocol compatible databases.
There are three main API options when considering connecting to a MySQL database server:
- PHP's MySQL Extension
- PHP's MySQLi Extension
- PHP Data Objects
The PDO MySQL driver extension, for example, does not expose an API to the PHP developer, but provides an interface to the PDO layer above it.
MySQLi is an improved version of the older PHP MySQL driver, offering various benefits.
The authors of the PHP scripting language recommend using MySQLi when dealing with MySQL server versions 4.1.3 and newer.
Technical details
The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which are:- An object-oriented interface
- Support for prepared statements
- Support for multiple statements
- Support for transactions
- Enhanced debugging support
Comparison of features
| PHP's MySQLi Extension | PDO | PHP's MySQL Extension | |
| PHP version introduced | 5.0 | 5.0 | pre-3.0 |
| Included with PHP 5.x | |||
| Included with PHP 7.x | |||
| Development status | Active development | Active development as of PHP 5.3 | Deprecated as of PHP 5.5 Removed in PHP 7.0 |
| Recommended by MySQL for new projects | - preferred option | ||
| API supports Charsets | |||
| API supports server-side Prepared Statements | |||
| API supports client-side Prepared Statements | |||
| API supports Stored Procedures | |||
| API supports Multiple Statements | |||
| Supports all MySQL 4.1+ functionality |
Start guide
Dual interface
The MySQLi extension features a dual interface - it supports both the procedural and object-oriented programming paradigms.Users migrating from the old MySQL extension may prefer the procedural interface. The procedural interface is similar to that of the old MySQL extension. In many cases, the function names differ only by prefix. Some MySQLi functions take a connection handle as their first argument, whereas matching functions in the old MySQL interface took it as an optional last argument.
New and old native function calls
$mysqli = mysqli_connect;
$result = mysqli_query;
echo mysqli_num_rows;
$mysql = mysql_connect;
mysql_select_db;
$result = mysql_query;
echo mysql_num_rows;
?>
Connections
The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.The hostname
localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.Example. Special meaning of localhost
$mysqli = new mysqli;
if
echo $mysqli->host_info. "\n";
$mysqli = new mysqli;
if
echo $mysqli->host_info. "\n";
?>
Output
Localhost via UNIX socket
127.0.0.1 via TCP/IP
Executing statements
Statements can be executed with the mysqli_query, mysqli_real_query and mysqli_multi_query functions. The mysqli_query function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Calling mysqli_query is identical to calling mysqli_real_query followed by mysqli_store_result.Example: Connecting to MySQL
$mysqli = new mysqli;
if
if ||
!$mysqli->query ||
!$mysqli->query VALUES )
?>
Buffered result sets
After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query combines statement execution and result set buffering.
PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.
Example: Navigation through buffered results
$mysqli = new mysqli;
if
if ||
!$mysqli->query ||
!$mysqli->query VALUES,, )
$res = $mysqli->query;
echo "Reverse order...\n";
for
echo "Result set order...\n";
$res->data_seek;
while )
?>
The above example will output:
Reverse order...
id = 3
id = 2
id = 1
Result set order...
id = 1
id = 2
id = 3
Unbuffered result sets
If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.
Example: Navigation through unbuffered results
$mysqli->real_query;
$res = $mysqli->use_result;
echo "Result set order...\n";
while )
?>
Result set values data types
The mysqli_query, mysqli_real_query and mysqli_multi_query functions are used to execute non-prepared statements. At the level of the MySQL Client Server Protocol, the command COM_QUERY and the text protocol are used for statement execution. With the text protocol, the MySQL server converts all data of a result sets into strings before sending. This conversion is done regardless of the SQL result set column data type. The MySQL client libraries receive all column values as strings. No further client-side casting is done to convert columns back to their native types. Instead, all values are provided as PHP strings.
Example: Text protocol returns strings by default
$mysqli = new mysqli;
if
if ||
!$mysqli->query ||
!$mysqli->query VALUES )
$res = $mysqli->query;
$row = $res->fetch_assoc;
printf\n", $row, gettype);
printf\n", $row, gettype);
?>
The above example will output:
id = 1
label = a
It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd library. If set, the mysqlnd library will check the result set meta data column types and convert numeric SQL columns to PHP numbers, if the PHP data type value range allows for it. This way, for example, SQL INT columns are returned as integers.
Example: Native data types with mysqlnd and connection option
$mysqli = mysqli_init;
$mysqli->options;
$mysqli->real_connect;
if
if ||
!$mysqli->query ||
!$mysqli->query VALUES )
$res = $mysqli->query;
$row = $res->fetch_assoc;
printf\n", $row, gettype);
printf\n", $row, gettype);
?>
The above example will output:
id = 1
label = a
Prepared statements
The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.Basic workflow
The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
The MySQL server supports using anonymous, positional placeholder with ?.
See example in.
Stored procedures
The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.Parameter
Stored procedures can have IN, INOUT and OUT parameters, depending on the MySQL version. The MySQLi interface has no special notion for the different kinds of parameters.
IN parameter
Input parameters are provided with the CALL statement. Please, make sure values are escaped correctly.
See examples in.
Multiple statements
MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.Multiple statements or multi queries must be executed with mysqli_multi_query. The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.
See examples in