English
English
After executing a database query using MySQLi in PHP, we often want an efficient and convenient way to fetch all query results. In object-oriented MySQLi, mysqli_result::fetch_all provides a concise method to retrieve all results at once as an array. This article explains how to use the fetch_all method in detail and discusses its use cases and considerations.
mysqli_result::fetch_all is a method of the mysqli_result class used to fetch all rows from a result set at once. It returns a two-dimensional array, where each row represents a record from the query result.
The syntax is as follows:
<span><span><span class="hljs-keyword">array</span></span><span> mysqli_result::</span><span><span class="hljs-title function_ invoke__">fetch_all</span></span><span>(</span><span><span class="hljs-keyword">int</span></span><span> </span><span><span class="hljs-variable">$mode</span></span><span> = MYSQLI_NUM)
</span></span>The $mode parameter defines the array format. Common options include:
MYSQLI_NUM: Returns a numerically indexed array.
MYSQLI_ASSOC: Returns an associative array (field names as keys).
MYSQLI_BOTH: Returns both numeric and associative indexes.
Here is a complete example demonstrating the use of fetch_all.
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// Database connection configuration</span></span><span>
</span><span><span class="hljs-variable">$host</span></span><span> = </span><span><span class="hljs-string">"localhost"</span></span><span>;
</span><span><span class="hljs-variable">$user</span></span><span> = </span><span><span class="hljs-string">"root"</span></span><span>;
</span><span><span class="hljs-variable">$password</span></span><span> = </span><span><span class="hljs-string">""</span></span><span>;
</span><span><span class="hljs-variable">$database</span></span><span> = </span><span><span class="hljs-string">"test_db"</span></span><span>;
<p></span>// Create connection<br>
$mysqli = new mysqli($host, $user, $password, $database);</p>
<p>// Check connection<br>
if ($mysqli->connect_error) {<br>
die("Connection failed: ". </span>$mysqli->connect_error);<br>
}</p>
<p>// Query statement<br>
$sql = "SELECT id, name, email FROM users";</p>
<p>// Execute query<br>
$result = $mysqli-></span>query($sql);</p>
<p>// Check if query was successful<br>
if ($result === false) {<br>
</span>die("Query failed: ". </span>$mysqli->error);<br>
}</p>
<p></span>// Use fetch_all to get all results (associative array)<br>
$data = $result-></span>fetch_all(MYSQLI_ASSOC);</p>
<p></span>// Output results<br>
foreach ($data as $row) {<br>
echo "ID: " . $row['id'] . "<br>";<br>
echo "Name: " . $row['name'] . "<br>";<br>
echo "Email: " . $row['email'] . "<br><br>";<br>
}</p>
<p>// Free result set<br>
$result->free();</p>
<p>// Close connection<br>
$mysqli->close();<br>
?><br>
</span>Simplicity: Compared to the traditional while ($row = $result->fetch_assoc()) approach, it requires less code and is clearer.
Efficiency: Loads all data into memory at once, suitable for small to medium-sized datasets.
Flexibility: Supports multiple array formats (numeric, associative, or both).
Be cautious with large datasets: fetch_all loads all data into memory at once, which may cause memory overflow with very large datasets.
Requires MySQL Native Driver (mysqlnd): The fetch_all method requires mysqlnd. In older PHP setups without mysqlnd, this method may not be available.
Error handling: Always check if the query result is false to catch SQL execution errors promptly.
With mysqli_result::fetch_all, PHP developers can efficiently retrieve complete query results from a database. Although this method is simple and convenient, it should be used judiciously based on actual data volume and system resources. Mastering its use cases can enhance both your database operation efficiency and code readability.
Related Tags:
mysqli_result
