PHP MySQLi mysqli_stmt_store_result()

mysqli_stmt_store_result()

The mysqli_stmt_store_result() function transfers the complete result set from the MySQL server into the local memory of the PHP script. This is essential if you want to count rows or seek through results after executing a prepared statement.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (mysqli_connect_errno()) {
    echo "Connection failed: " . mysqli_connect_error();
    exit();
}

// 1. Prepare the statement to read all data
$stmt = mysqli_prepare($connection, "SELECT * FROM mysqli_table");

// 2. Execute the query on the server
mysqli_stmt_execute($stmt);

// 3. Store the result from the server locally in the PHP buffer
// NOTE: This step is REQUIRED if you want to use the mysqli_stmt_num_rows() 
// function immediately afterward. If you do not store the results locally, the row 
// counting function will return 0, as PHP has not yet transferred all the data into its memory!
mysqli_stmt_store_result($stmt);

// 4. Now we can safely retrieve and output the exact number of rows
$row_count = mysqli_stmt_num_rows($stmt);
echo "Number of rows in the table: " . $row_count;

// 5. Close the statement and connection to free up memory
mysqli_stmt_close($stmt);
mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Practical use: If you are using a SELECT query with prepared statements and want to find out the number of rows using the mysqli_stmt_num_rows() function, you must call mysqli_stmt_store_result() beforehand.

It transfers results from the server into local storage.

It enables the proper functioning of the row counting function (num_rows).

It frees up the server to execute new queries while PHP processes the stored data.