Executing statements
Executing statements
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 #1 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 #2 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 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 #4 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 #5 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