How to select multiple rows from mysql with one query and use them in php

How to select multiple rows from mysql with one query and use them in php

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How to select multiple rows from MySQL and use them in PHP

When you execute a SELECT query that filters for specific criteria, such as finding all rows where number1 equals 1, your database returns a result set containing every matching record. The challenge arises when attempting to access data beyond the first item using standard procedural functions like mysql_fetch_assoc(). This legacy function processes results sequentially and returns one associative array per row; once that array is returned and assigned to a variable, the internal pointer advances, making it impossible to retrieve subsequent rows without explicitly looping through them.

To achieve a structure resembling array['number2'][2], you must fundamentally change how you iterate over the result set or how you construct your PHP logic. The most robust approach involves wrapping the fetch loop within a standard PHP foreach statement. This allows you to accumulate all matching rows into a single master array before performing any further operations.

Here is a practical implementation using modern MySQLi or PDO, as the deprecated mysql_* functions are no longer supported and pose security risks:

// Assume $result holds your query execution return value
// Example: SELECT * FROM your_table WHERE number1 = 1;

$rows = [];
while ($row = mysqli_fetch_assoc($result)) {
    // Accumulate every row into the $rows array
    // The index here acts as the primary key for retrieval later
    $rows[] = $row; 
}

// Now you can access any specific row by its numeric index
echo $rows[0]['number2']; // First matching row
echo $rows[1]['number2']; // Second matching row

In this pattern, $rows becomes a dense, zero-indexed list where the first element represents the first database row found. If you specifically need to access the second row using an index of 2 (skipping the first two), you simply reference $rows[2]. Note that in PHP arrays created via [], the index reflects the position in the sequence, not a specific field value within the record unless you explicitly map them.

If your application logic requires a multi-dimensional structure where the outer key represents the column name rather than the row number, you can transpose the data during iteration:

$columns = [];
while ($row = mysqli_fetch_assoc($result)) {
    foreach ($row as $key => $value) {
        if (!isset($columns[$key])) {
            $columns[$key] = [];
        }
        $columns[$key][] = $value;
    }
}

// Accessing 'number2' for the second row (index 1 in 0-based array)
echo $columns['number2'][1]; 

While this transposed approach mimics a matrix, it is generally less performant and harder to debug than maintaining a list of associative arrays. It also complicates the retrieval of associated data from other tables if you need to join them later.

A critical pitfall in handling multiple rows is assuming that fetch_assoc provides random access. Without a loop, you are limited to the first result. Additionally, always ensure your connection and query execution methods use parameterized queries to prevent SQL injection, especially when dealing with dynamic conditions or user input.

For developers working within the Laravel ecosystem, consider leveraging Eloquent collections, which abstract this iteration logic away from raw SQL. However, understanding the underlying mechanics of fetching multiple rows remains essential for optimizing database calls and managing memory efficiently, particularly when handling large result sets where eager loading strategies might be preferable to selecting everything into PHP memory.

Our network