MySQL and PHP
Copyright 1997-2021 the PHP Documentation Group.
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 3.6 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 3.7 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 = 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 3.8 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 3.9 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 3.10 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
|