News Archive
PhpRiot Newsletter
Your Email Address:

More information

Executing MySQL queries with PHP mysqli

Note: This article was originally published at Planet PHP on 9 November 2011.
Planet PHP

The mysqli quickstart series is coming to an end. Today, the post is about non-prepared statements. You may also want to check out the following related blog posts:

Using mysqli to execute statements

Statements can be executed by help of the mysqli_query(), mysqli_real_query() and mysqli_multi_query() function. The mysqli_query() function is the most commonly used one. It combines executing statement and doing 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.

The mysqli_multi_query() function is used with Multiple Statements and is described here.

$mysqli = new mysqli("", "user", "password", "database"); 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. Nagivation is fast because the result sets is hold in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

$mysqli = new mysqli("localhost", "root", "", "test"); 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";

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.

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

$mysqli = mysqli_init(); $mysqli-options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

Truncated by Planet PHP, read more at the original (another 2122 bytes)