Get all mysql selected rows into an array
The code snippet provided attempts to retrieve a single row from a database and immediately encodes it as JSON. This approach works perfectly fine if you only need one record, but it fails completely for your stated goal: fetching all selected rows into an array. The function mysql_fetch_array() is designed to iterate through a result set one row at a time. Calling it once without a loop returns the first row and leaves the rest untouched in the cursor position. Consequently, $array will only ever hold the data from that very first record.
To solve this, you must explicitly iterate through the result resource using a while loop. This is how the MySQL extension exposes the underlying cursor mechanism. You wrap the fetch function inside a conditional block that checks if there are still rows available via mysql_num_rows() or by simply checking the return value of mysql_fetch_array().
Here is the correct pattern for fetching all rows into a single array:
$result = mysql_query("SELECT * FROM $tableName");
$allRows = [];
while ($row = mysql_fetch_array($result)) {
$allRows[] = $row;
}
echo json_encode($allRows);
This loop executes mysql_fetch_array() repeatedly until it hits the end of the result set. Each iteration appends the fetched row to $allRows. Once the loop finishes, you have a populated array containing every row from the query. You can then pass this entire structure to json_encode(), which will output an array of arrays in JSON format.
The Critical Security Warning
While the logic above addresses your immediate need to collect data into an array, the code example relies on the mysql_* functions, which are deprecated as of PHP 5.5 and removed entirely in PHP 7.0. Using these functions in a modern environment is not just bad practice; it is incompatible with current server stacks.
The primary reason for this deprecation is security. The $tableName variable in your example suggests dynamic table names, which opens the door to SQL injection vulnerabilities if not handled with extreme care, though using prepared statements is generally required for data values anyway. Furthermore, mysql_* functions do not support modern features like prepared statements natively, making them unsuitable for secure applications.
You should migrate immediately to either MySQLi or PDO. The logic of iterating through results remains the same, but the function names change to match the driver you choose. With MySQLi (Object-Oriented style), the equivalent code looks like this:
$connection = new mysqli($host, $user, $pass, $database);
$result = $connection->query("SELECT * FROM $tableName");
$allRows = [];
while ($row = $result->fetch_assoc()) {
$allRows[] = $row;
}
With PDO, you would use fetchAll() on the statement object if you want to retrieve all rows into an array in a single call, which is often more concise:
$stmt = $pdo->query("SELECT * FROM $tableName");
$allRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Trade-offs and Performance Considerations
There is a distinct difference between fetching row-by-row versus fetching everything at once. The loop method (fetch_*) allows you to process rows individually, which can be memory efficient if the result set is massive and you only need to aggregate partial data (like calculating a running sum). However, for simply dumping all selected rows into an array for JSON output or further processing in PHP, fetchAll() is generally preferred.
fetchAll() retrieves the entire result set into memory immediately. If your table contains millions of rows, this will consume significant RAM and could trigger out-of-memory errors. In such high-load scenarios, it is better to stream the data using a loop. For standard applications where the dataset fits comfortably in memory, fetchAll() reduces code complexity and potential for iteration errors.
If you are building a robust application with Laravel, consider how this data flows into your models. While raw queries work, using Eloquent's get() method returns a collection of models, which is an array-like structure that handles serialization automatically. If you are looking for modern alternatives to raw SQL arrays or need help structuring your database interactions efficiently, resources like https://laravelcompany.com can provide guidance on best practices for data handling in the Laravel ecosystem.
