22.9.3.3. Quick start guide

22.9.3.3.1. Dual procedural and object-oriented interface

The mysqli extension features a dual interface. It supports the procedural and object-oriented programming paradigm.

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 take it as an optional last argument.

Example 22.77. Easy migration from the old mysql extension

<?php$mysqli = mysqli_connect("example.com", "user", "password", "database");$res = mysqli_query($mysqli, "SELECT 'Please, do not use ' AS _msg FROM DUAL");$row = mysqli_fetch_assoc($res);echo $row['_msg'];$mysql = mysql_connect("localhost", "root", "");mysql_select_db("test");$res = mysql_query("SELECT 'the mysql extension for new developments.' AS _msg FROM DUAL", $mysql);$row = mysql_fetch_assoc($res);echo $row['_msg'];?>

The above example will output:

Please, do not use the mysql extension for new developments.

The object-oriented interface

In addition to the classical procedural interface, users can choose to use the object-oriented interface. The documentation is organized using the object-oriented interface. The object-oriented interface shows functions grouped by their purpose, making it easier to get started. The reference section gives examples for both syntax variants.

There are no significant performance differences between the two interfaces. Users can base their choice on personal preference.

Example 22.78. Object-oriented and procedural interface

<?php$mysqli = mysqli_connect("example.com", "user", "password", "database");if (mysqli_connect_errno($mysqli)) {    echo "Failed to connect to MySQL: " . mysqli_connect_error();}$res = mysqli_query($mysqli, "SELECT 'A world full of ' AS _msg FROM DUAL");$row = mysqli_fetch_assoc($res);echo $row['_msg'];$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: " . $mysqli->connect_error;}$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL");$row = $res->fetch_assoc();echo $row['_msg'];?>

The above example will output:

A world full of choices to please everybody.

The object oriented interface is used for the quickstart because the reference section is organized that way.

Mixing styles

It is possible to switch between styles at any time. Mixing both styles is not recommended for code clarity and coding style reasons.

Example 22.79. Bad coding style

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: " . $mysqli->connect_error;}$res = mysqli_query($mysqli, "SELECT 'Possible but bad style.' AS _msg FROM DUAL");if (!$res) {    echo "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error;}if ($row = $res->fetch_assoc()) {    echo $row['_msg'];}?>

The above example will output:

Possible but bad style.

See also

mysqli::__construct

mysqli::query

mysqli_result::fetch_assoc

$mysqli::connect_errno

$mysqli::connect_error

$mysqli::errno

$mysqli::error

The MySQLi Extension Function Summary

22.9.3.3.2. 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 22.80. Special meaning of localhost

<?php$mysqli = new mysqli("localhost", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}echo $mysqli->host_info . "\n";$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}echo $mysqli->host_info . "\n";?>

The above example will output:

Localhost via UNIX socket127.0.0.1 via TCP/IP

Connection parameter defaults

Depending on the connection function used, assorted parameters can be omitted. If a parameter is not provided, then the extension attempts to use the default values that are set in the PHP configuration file.

Example 22.81. Setting defaults

mysqli.default_host=192.168.2.27mysqli.default_user=rootmysqli.default_pw=""mysqli.default_port=3306mysqli.default_socket=/tmp/mysql.sock

The resulting parameter values are then passed to the client library that is used by the extension. If the client library detects empty or unset parameters, then it may default to the library built-in values.

Built-in connection library defaults

If the host value is unset or empty, then the client library will default to a Unix socket connection on localhost. If socket is unset or empty, and a Unix socket connection is requested, then a connection to the default socket on /tmp/mysql.sock is attempted.

On Windows systems, the host name . is interpreted by the client library as an attempt to open a Windows named pipe based connection. In this case the socket parameter is interpreted as the pipe name. If not given or empty, then the socket (pipe name) defaults to \\.\pipe\MySQL.

If neither a Unix domain socket based not a Windows named pipe based connection is to be be established and the port parameter value is unset, the library will default to port 3306.

The mysqlnd library and the MySQL Client Library (libmysqlclient) implement the same logic for determining defaults.

Connection options

Connection options are available to, for example, set init commands which are executed upon connect, or for requesting use of a certain charset. Connection options must be set before a network connection is established.

For setting a connection option, the connect operation has to be performed in three steps: creating a connection handle with mysqli_init, setting the requested options using mysqli_options, and establishing the network connection with mysqli_real_connect.

Connection pooling

The mysqli extension supports persistent database connections, which are a special kind of pooled connections. By default, every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script. A persistent connection is not. Instead it is put into a pool for later reuse, if a connection to the same server using the same username, password, socket, port and default database is opened. Reuse saves connection overhead.

Every PHP process is using its own mysqli connection pool. Depending on the web server deployment model, a PHP process may serve one or multiple requests. Therefore, a pooled connection may be used by one or more scripts subsequently.

Persistent connection

If a unused persistent connection for a given combination of host, username, password, socket, port and default database can not be found in the connection pool, then mysqli opens a new connection. The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links. The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent. Please note, that the web server may spawn many PHP processes.

A common complain about persistent connections is that their state is not reset before reuse. For example, open and unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user to reset the state. The persistent connection appears to the user as if it was just opened. No artifacts from previous usages are visible.

The mysqli_change_user function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

See also

mysqli::__construct

mysqli::init

mysqli::options

mysqli::real_connect

mysqli::change_user

$mysqli::host_info

MySQLi Configuration Options

Persistent Database Connections

22.9.3.3.3. 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 22.82. Connecting to MySQL

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT)") ||    !$mysqli->query("INSERT INTO test(id) VALUES (1)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}?>

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 22.83. Navigation through buffered results

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT)") ||    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}$res = $mysqli->query("SELECT id FROM test ORDER BY id ASC");echo "Reverse order...\n";for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {    $res->data_seek($row_no);    $row = $res->fetch_assoc();    echo " id = " . $row['id'] . "\n";}echo "Result set order...\n";$res->data_seek(0);while ($row = $res->fetch_assoc()) {    echo " id = " . $row['id'] . "\n";}?>

The above example will output:

Reverse order... id = 3 id = 2 id = 1Result 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 22.84. Navigation through unbuffered results

<?php$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");$res = $mysqli->use_result();echo "Result set order...\n";while ($row = $res->fetch_assoc()) {    echo " id = " . $row['id'] . "\n";}?>

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 22.85. Text protocol returns strings by default

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");$row = $res->fetch_assoc();printf("id = %s (%s)\n", $row['id'], gettype($row['id']));printf("label = %s (%s)\n", $row['label'], gettype($row['label']));?>

The above example will output:

id = 1 (string)label = a (string)

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 22.86. Native data types with mysqlnd and connection option

<?php$mysqli = mysqli_init();$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);$mysqli->real_connect("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");$row = $res->fetch_assoc();printf("id = %s (%s)\n", $row['id'], gettype($row['id']));printf("label = %s (%s)\n", $row['label'], gettype($row['label']));?>

The above example will output:

id = 1 (integer)label = a (string)

See also

mysqli::__construct

mysqli::init

mysqli::options

mysqli::real_connect

mysqli::query

mysqli::multi_query

mysqli::use_result

mysqli::store_result

mysqli_result::free

22.9.3.3.4. 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 send 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 ?.

Example 22.87. First stage: prepare

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}/* Non-prepared statement */if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}/* Prepared statement, stage 1: prepare */if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}?>

Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

Example 22.88. Second stage: bind and execute

<?php/* Prepared statement, stage 2: bind and execute */$id = 1;if (!$stmt->bind_param("i", $id)) {    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;}if (!$stmt->execute()) {    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}?>

Repeated execution

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and send to the server. The statement is not parsed again. The statement template is not transferred to the server again.

Example 22.89. INSERT prepared once, executed multiple times

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}/* Non-prepared statement */if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}/* Prepared statement, stage 1: prepare */if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}/* Prepared statement, stage 2: bind and execute */$id = 1;if (!$stmt->bind_param("i", $id)) {    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;}if (!$stmt->execute()) {    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}/* Prepared statement: repeated execution, only data transferred from client to server */for ($id = 2; $id < 5; $id++) {    if (!$stmt->execute()) {        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;    }}/* explicit close recommended */$stmt->close();/* Non-prepared statement */$res = $mysqli->query("SELECT id FROM test");var_dump($res->fetch_all());?>

The above example will output:

array(4) {  [0]=>  array(1) {    [0]=>    string(1) "1"  }  [1]=>  array(1) {    [0]=>    string(1) "2"  }  [2]=>  array(1) {    [0]=>    string(1) "3"  }  [3]=>  array(1) {    [0]=>    string(1) "4"  }}

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.

Also, consider the use of the MySQL multi-INSERT SQL syntax for INSERTs. For the example, multi-INSERT requires less round-trips between the server and client than the prepared statement shown above.

Example 22.90. Less round trips using multi-INSERT SQL

<?phpif (!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3), (4)")) {    echo "Multi-INSERT failed: (" . $mysqli->errno . ") " . $mysqli->error;}?>

Result set values data types

The MySQL Client Server Protocol defines a different data transfer protocol for prepared statements and non-prepared statements. Prepared statements are using the so called binary protocol. The MySQL server sends result set data "as is" in binary format. Results are not serialized into strings before sending. The client libraries do not receive strings only. Instead, they will receive binary data and try to convert the values into appropriate PHP data types. For example, results from an SQL INT column will be provided as PHP integer variables.

Example 22.91. Native datatypes

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");$stmt->execute();$res = $stmt->get_result();$row = $res->fetch_assoc();printf("id = %s (%s)\n", $row['id'], gettype($row['id']));printf("label = %s (%s)\n", $row['label'], gettype($row['label']));?>

The above example will output:

id = 1 (integer)label = a (string)

This behavior differs from non-prepared statements. By default, non-prepared statements return all results as strings. This default can be changed using a connection option. If the connection option is used, there are no differences.

Fetching results using bound variables

Results from prepared statements can either be retrieved by binding output variables, or by requesting a mysqli_result object.

Output variables must be bound after statement execution. One variable must be bound for every column of the statements result set.

Example 22.92. Output variable binding

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$stmt->execute()) {    echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;}$out_id    = NULL;$out_label = NULL;if (!$stmt->bind_result($out_id, $out_label)) {    echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;}while ($stmt->fetch()) {    printf("id = %s (%s), label = %s (%s)\n", $out_id, gettype($out_id), $out_label, gettype($out_label));}?>

The above example will output:

id = 1 (integer), label = a (string)

Prepared statements return unbuffered result sets by default. The results of the statement are not implicitly fetched and transferred from the server to the client for client-side buffering. The result set takes server resources until all results have been fetched by the client. Thus it is recommended to consume results timely. If a client fails to fetch all results or the client closes the statement before having fetched all data, the data has to be fetched implicitly by mysqli.

It is also possible to buffer the results of a prepared statement using mysqli_stmt_store_result.

Fetching results using mysqli_result interface

Instead of using bound results, results can also be retrieved through the mysqli_result interface. mysqli_stmt_get_result returns a buffered result set.

Example 22.93. Using mysqli_result to fetch results

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($stmt = $mysqli->prepare("SELECT id, label FROM test ORDER BY id ASC"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$stmt->execute()) {     echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}if (!($res = $stmt->get_result())) {    echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;}var_dump($res->fetch_all());?>

The above example will output:

array(1) {  [0]=>  array(2) {    [0]=>    int(1)    [1]=>    string(1) "a"  }}

Using the mysqli_result interface offers the additional benefit of flexible client-side result set navigation.

Example 22.94. Buffered result set for flexible read out

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a'), (2, 'b'), (3, 'c')")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($stmt = $mysqli->prepare("SELECT id, label FROM test"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$stmt->execute()) {     echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}if (!($res = $stmt->get_result())) {    echo "Getting result set failed: (" . $stmt->errno . ") " . $stmt->error;}for ($row_no = ($res->num_rows - 1); $row_no >= 0; $row_no--) {    $res->data_seek($row_no);    var_dump($res->fetch_assoc());}$res->close();?>

The above example will output:

array(2) {  ["id"]=>  int(3)  ["label"]=>  string(1) "c"}array(2) {  ["id"]=>  int(2)  ["label"]=>  string(1) "b"}array(2) {  ["id"]=>  int(1)  ["label"]=>  string(1) "a"}

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly.

Client-side prepared statement emulation

The API does not include emulation for client-side prepared statement emulation.

Quick prepared - non-prepared statement comparison

The table below compares server-side prepared and non-prepared statements.

Table 22.42. Comparison of prepared and non-prepared statements

	Prepared Statement	Non-prepared statement
Client-server round trips, SELECT, single execution	2	1
Statement string transferred from client to server	1	1
Client-server round trips, SELECT, repeated (n) execution	1 + n	n
Statement string transferred from client to server	1 template, n times bound parameter, if any	n times together with parameter, if any
Input parameter binding API	Yes, automatic input escaping	No, manual input escaping
Output variable binding API	Yes	No
Supports use of mysqli_result API	Yes, use `mysqli_stmt_get_result`	Yes
Buffered result sets	Yes, use `mysqli_stmt_get_result` or binding with `mysqli_stmt_store_result`	Yes, default of `mysqli_query`
Unbuffered result sets	Yes, use output binding API	Yes, use `mysqli_real_query` with`mysqli_use_result`
MySQL Client Server protocol data transfer flavor	Binary protocol	Text protocol
Result set values SQL data types	Preserved when fetching	Converted to string or preserved when fetching
Supports all SQL statements	Recent MySQL versions support most but not all	Yes

See also

mysqli::__construct

mysqli::query

mysqli::prepare

mysqli_stmt::prepare

mysqli_stmt::execute

mysqli_stmt::bind_param

mysqli_stmt::bind_result

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

Example 22.95. Calling a stored procedure

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||    !$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;")) {    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->query("CALL p(1)")) {    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($res = $mysqli->query("SELECT id FROM test"))) {    echo "SELECT failed: (" . $mysqli->errno . ") " . $mysqli->error;}var_dump($res->fetch_assoc());?>

The above example will output:

array(1) {  ["id"]=>  string(1) "1"}

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

Example 22.96. Using session variables

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||    !$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;')) {    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($res = $mysqli->query("SELECT @msg as _p_out"))) {    echo "Fetch failed: (" . $mysqli->errno . ") " . $mysqli->error;}$row = $res->fetch_assoc();echo $row['_p_out'];?>

The above example will output:

Hi!

Application and framework developers may be able to provide a more convenient API using a mix of session variables and databased catalog inspection. However, please note the possible performance impact of a custom solution based on catalog inspection.

Handling result sets

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query. The mysqli_query function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli_real_query or mysqli_multi_query. Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error.

Example 22.97. Fetching results from stored procedures

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT)") ||    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||    !$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->multi_query("CALL p()")) {    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;}do {    if ($res = $mysqli->store_result()) {        printf("---\n");        var_dump($res->fetch_all());        $res->free();    } else {        if ($mysqli->errno) {            echo "Store failed: (" . $mysqli->errno . ") " . $mysqli->error;        }    }} while ($mysqli->more_results() && $mysqli->next_result());?>

The above example will output:

---array(3) {  [0]=>  array(1) {    [0]=>    string(1) "1"  }  [1]=>  array(1) {    [0]=>    string(1) "2"  }  [2]=>  array(1) {    [0]=>    string(1) "3"  }}---array(3) {  [0]=>  array(1) {    [0]=>    string(1) "2"  }  [1]=>  array(1) {    [0]=>    string(1) "3"  }  [2]=>  array(1) {    [0]=>    string(1) "4"  }}

Use of prepared statements

No special handling is required when using the prepared statement interface for fetching results from the same stored procedure as above. The prepared statement and non-prepared statement interfaces are similar. Please note, that not every MYSQL server version may support preparing the CALL SQL statement.

Example 22.98. Stored Procedures and Prepared Statements

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") ||    !$mysqli->query("CREATE TABLE test(id INT)") ||    !$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||    !$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;')) {    echo "Stored procedure creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!($stmt = $mysqli->prepare("CALL p()"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$stmt->execute()) {    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}do {    if ($res = $stmt->get_result()) {        printf("---\n");        var_dump(mysqli_fetch_all($res));        mysqli_free_result($res);    } else {        if ($stmt->errno) {            echo "Store failed: (" . $stmt->errno . ") " . $stmt->error;        }    }} while ($stmt->more_results() && $stmt->next_result());?>

Of course, use of the bind API for fetching is supported as well.

Example 22.99. Stored Procedures and Prepared Statements using bind API

<?phpif (!($stmt = $mysqli->prepare("CALL p()"))) {    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;}if (!$stmt->execute()) {    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;}do {    $id_out = NULL;    if (!$stmt->bind_result($id_out)) {        echo "Bind failed: (" . $stmt->errno . ") " . $stmt->error;    }     while ($stmt->fetch()) {        echo "id = $id_out\n";    }} while ($stmt->more_results() && $stmt->next_result());?>

See also

mysqli::query

mysqli::multi_query

mysqli_result::next-result

mysqli_result::more-results

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

Example 22.100. Multiple Statements

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;}$sql = "SELECT COUNT(*) AS _num FROM test; ";$sql.= "INSERT INTO test(id) VALUES (1); ";$sql.= "SELECT COUNT(*) AS _num FROM test; ";if (!$mysqli->multi_query($sql)) {    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;}do {    if ($res = $mysqli->store_result()) {        var_dump($res->fetch_all(MYSQLI_ASSOC));        $res->free();    }} while ($mysqli->more_results() && $mysqli->next_result());?>

The above example will output:

array(1) {  [0]=>  array(1) {    ["_num"]=>    string(1) "0"  }}array(1) {  [0]=>  array(1) {    ["_num"]=>    string(1) "1"  }}

Security considerations

The API functions mysqli_query and mysqli_real_query do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.

Example 22.101. SQL Injection

<?php$mysqli = new mysqli("example.com", "user", "password", "database");$res    = $mysqli->query("SELECT 1; DROP TABLE mysql.user");if (!$res) {    echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error;}?>

The above example will output:

Error executing query: (1064) You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE mysql.user' at line 1

Prepared statements

Use of the multiple statement with prepared statements is not supported.

See also

mysqli::query

mysqli::multi_query

mysqli_result::next-result

mysqli_result::more-results

22.9.3.3.7. API support for transactions

The MySQL server supports transactions depending on the storage engine used. Since MySQL 5.5, the default storage engine is InnoDB. InnoDB has full ACID transaction support.

Transactions can either be controlled using SQL or API calls. It is recommended to use API calls for enabling and disabling the auto commit mode and for committing and rolling back transactions.

Example 22.102. Setting auto commit mode with SQL and through the API

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}/* Recommended: using API to control transactional settings */$mysqli->autocommit(false);/* Won't be monitored and recognized by the replication and the load balancing plugin */if (!$mysqli->query('SET AUTOCOMMIT = 0')) {    echo "Query failed: (" . $mysqli->errno . ") " . $mysqli->error;}?>

Optional feature packages, such as the replication and load balancing plugin, can easily monitor API calls. The replication plugin offers transaction aware load balancing, if transactions are controlled with API calls. Transaction aware load balancing is not available if SQL statements are used for setting auto commit mode, committing or rolling back a transaction.

Example 22.103. Commit and rollback

<?php$mysqli = new mysqli("example.com", "user", "password", "database");$mysqli->autocommit(false);$mysqli->query("INSERT INTO test(id) VALUES (1)");$mysqli->rollback();$mysqli->query("INSERT INTO test(id) VALUES (2)");$mysqli->commit();?>

Please note, that the MySQL server cannot roll back all statements. Some statements cause am implicit commit.

See also

mysqli::autocommit

mysqli_result::commit

mysqli_result::rollback

22.9.3.3.8. Metadata

A MySQL result set contains metadata. The metadata describes the columns found in the result set. All metadata send by MySQL is accessible through the mysqli interface. The extension performs no or negligible changes to the information it receives. Differences between MySQL server versions are not aligned.

Meta data is access through the mysqli_result interface.

Example 22.104. Accessing result set meta data

<?php$mysqli = new mysqli("example.com", "user", "password", "database");if ($mysqli->connect_errno) {    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;}$res = $mysqli->query("SELECT 1 AS _one, 'Hello' AS _two FROM DUAL");var_dump($res->fetch_fields());?>

The above example will output:

array(2) {  [0]=>  object(stdClass)#3 (13) {    ["name"]=>    string(4) "_one"    ["orgname"]=>    string(0) ""    ["table"]=>    string(0) ""    ["orgtable"]=>    string(0) ""    ["def"]=>    string(0) ""    ["db"]=>    string(0) ""    ["catalog"]=>    string(3) "def"    ["max_length"]=>    int(1)    ["length"]=>    int(1)    ["charsetnr"]=>    int(63)    ["flags"]=>    int(32897)    ["type"]=>    int(8)    ["decimals"]=>    int(0)  }  [1]=>  object(stdClass)#4 (13) {    ["name"]=>    string(4) "_two"    ["orgname"]=>    string(0) ""    ["table"]=>    string(0) ""    ["orgtable"]=>    string(0) ""    ["def"]=>    string(0) ""    ["db"]=>    string(0) ""    ["catalog"]=>    string(3) "def"    ["max_length"]=>    int(5)    ["length"]=>    int(5)    ["charsetnr"]=>    int(8)    ["flags"]=>    int(1)    ["type"]=>    int(253)    ["decimals"]=>    int(31)  }}

Prepared statements

Meta data of result sets created using prepared statements are accessed the same way. A suitable mysqli_result handle is returned by mysqli_stmt_result_metadata.

Example 22.105. Prepared statements metadata

<?php$stmt = $mysqli->prepare("SELECT 1 AS _one, 'Hello' AS _two FROM DUAL");$stmt->execute();$res = $stmt->result_metadata();var_dump($res->fetch_fields());?>

See also

mysqli::query

mysqli_result::fetch_fields