Converting SQL Result Sets into PHP Arrays
When you execute a SELECT query against a database, the raw output is a result set containing multiple rows and columns. While procedural functions like mysqli_fetch_array() are useful for iterating over data one row at a time, there are scenarios where you need to load the entire dataset into a single array structure before processing it. This approach allows you to access data sequentially via numeric keys ($data[0]['uid']) rather than relying on cursor state from a previous function call.
The fundamental mechanism for this conversion is mysqli_fetch_all(). Unlike fetch_array(), which returns a single row and advances the internal cursor, fetch_all() retrieves all remaining rows in the result set and returns them as an array of arrays. The resulting structure mimics the behavior you described: $myData[0]['uid'] gives you the UID of the first row, $myData[1]['uid'] for the second, and so on.
Implementation with MySQLi
Using the procedural MySQLi interface shown in your source material, the logic is straightforward. You execute the query, check for errors, and then pass the result object directly to fetch_all(). By default, this function returns an array of associative arrays if you do not specify a return type, which aligns perfectly with your requirement to access fields by name like $myData['uid'] (with the numeric index wrapper).
$result = mysqli_query($connection, $command);
if (!$result) {
die("Query Failed: " . mysqli_error($connection));
}
// Fetch all rows into a single array
$myData = mysqli_fetch_all($result, MYSQLI_ASSOC);
mysqli_close($connection);
// Accessing the data
print_r($myData[0]['uid']);
The second argument in mysqli_fetch_all() controls the return type. Passing MYSQLI_ASSOC ensures every row is an associative array keyed by column names. If you omit this or pass MYSQLI_NUM, you will get numeric keys instead, which would not satisfy your specific need for $myData['uid'].
The Trade-off: Memory Usage
The primary consideration when loading an entire result set into memory is resource consumption. While iterating row-by-row with a while loop is memory-efficient and suitable for streaming large datasets, fetch_all() loads every single row into RAM simultaneously.
If your query returns millions of rows, converting the result to a full array can lead to significant memory spikes or even cause the script to hit PHP's memory limit (memory_limit). In such cases, keeping the cursor open with while($row = mysqli_fetch_array($result)) is the robust architectural choice. However, for typical application data setsâsay, up to a few thousand recordsâthe array approach simplifies downstream logic and allows you to manipulate the data structure independently of the database connection lifecycle.
Error Handling and Resource Cleanup
It is critical to note that mysqli_fetch_all() does not automatically close the underlying connection or free the result resource if an error occurs before execution, but it does handle cleanup internally upon completion. However, best practice dictates explicitly closing the connection with mysqli_close($connection) as shown in your snippet, especially when returning data from a function. If you wrap this logic inside a function intended for reuse, ensure the query execution happens within a transaction or is isolated to prevent unintended side effects on other parts of the application.
For developers working within the Laravel framework, this specific procedural pattern often translates into using the Query Builder's get() method, which returns an array of associative arrays directly without manual cursor management. However, understanding the underlying mechanism with raw MySQLi remains essential for debugging and optimizing performance in non-framework contexts or legacy codebases.