English
English
In PHP, the mysqli extension is a powerful tool for connecting to and working with MySQL databases. It offers multiple ways to handle SQL query result sets. Among them, the mysqli::use_result function is a commonly used method, particularly suitable for scenarios where you need to process large amounts of data or fetch rows one by one. This article explains in detail how to use mysqli::use_result for basic result set handling and provides an example to demonstrate its practical application.
mysqli::use_result is a method of the mysqli class used to retrieve a result set from an executed SQL query. When you call this method, it does not load all results into memory at once. Instead, it returns a pointer to the result set, which allows you to fetch and process data row by row.
This method is typically used with SELECT queries. When queries return a large amount of data, use_result helps avoid excessive memory usage and improves performance. It differs from the mysqli::store_result method, which loads the entire result set into memory at once.
Create a Database Connection
First, use the mysqli_connect function to connect to the MySQL database.
Execute an SQL Query
Use the mysqli_query function to run an SQL query, ensuring it is a SELECT statement.
Call the use_result Method
Retrieve the query results by calling the mysqli::use_result method.
Process the Result Set
Fetch rows one by one using functions such as mysqli_fetch_row or mysqli_fetch_assoc until no more data is available.
Close the Connection
Once the query and result processing are complete, close the database connection.
Here is a simple example that demonstrates how to use mysqli::use_result to handle a result set:
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// Step 1: Create database connection</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span>, </span><span><span class="hljs-string">"username"</span></span>, </span><span><span class="hljs-string">"password"</span></span>, </span><span><span class="hljs-string">"database"</span></span>);
<p></span>// Check connection<br>
if (</span>$mysqli->connect_error) {<br>
</span>die("Connection failed: " . </span>$mysqli->connect_error);<br>
}</p>
<p></span>// Step 2: Execute SQL query<br>
</span>$sql = "SELECT id, name, email FROM users";<br>
</span>$result = </span>$mysqli->query(</span>$sql);</p>
<p></span>// Check if query succeeded<br>
</span>if (</span>$result) {<br>
</span>// Step 3: Use use_result to get result set<br>
</span>$res = </span>$mysqli->use_result();</p>
</span><span><span class="hljs-keyword">while</span></span> (</span><span class="hljs-variable">$row</span></span> = </span><span><span class="hljs-variable">$res</span></span>->fetch_assoc()) {
echo "ID: " . </span><span><span class="hljs-variable">$row</span></span>['id'] . " - Name: " . </span><span><span class="hljs-variable">$row</span></span>['name'] . " - Email: " . </span><span><span class="hljs-variable">$row</span></span>['email'] . "<br>";
}
} else {
echo "Query failed: " . $mysqli->error;
}
// Step 5: Close database connection
$mysqli->close();
?>
Create a Database Connection:
We first establish a database connection using new mysqli, passing the hostname, username, password, and database name. If the connection fails, $mysqli->connect_error returns the error message and terminates the script.
Execute Query:
We run the query using the $mysqli->query method. On success, it returns a result object.
Retrieve the Result Set:
We call $mysqli->use_result() to get a pointer to the result set, which refers to each row of the query results.
Process Results Row by Row:
We use fetch_assoc to extract data row by row. This method returns an associative array where the keys are column names and the values are the corresponding data.
Close Connection:
Once processing is complete, we close the database connection with $mysqli->close().
Memory Efficient: Since use_result does not load all rows into memory at once, it is suitable for large datasets.
Better Performance: For large queries, processing results row by row is usually more efficient than loading everything at once.
Cannot Run Multiple Queries at Once: Unlike store_result, when using use_result, you can only process the current query’s results and cannot execute multiple queries simultaneously.
No Random Access: The result set returned by use_result can only be processed sequentially and does not allow random access to rows.
mysqli::use_result is a valuable tool when working with large datasets, especially in scenarios where row-by-row processing is required and memory usage needs to be minimized. Knowing how to use it properly helps developers strike a balance between performance and memory management. If you are handling large amounts of data or are sensitive to memory usage for query results, use_result is a method worth trying.
Related Tags:
mysqli
