Selecting MySql table data into an array

Selecting MySql table data into an array

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

Stefan Bogdanescu

Founder & Senior Architect

Fixing mysql_fetch_array Assignment Logic in PHP

The core issue preventing your code from storing the ID, name, and code is a simple assignment operator error inside your while loop. In your current implementation, the line $userinfo = $row_user[name] overwrites the entire array with just a single string (the user's name) on every iteration. By the time the loop finishes, $userinfo only holds the name of the last record fetched from the database.

To store multiple columns within a single row, you must use the array assignment operator ([]) instead of the direct variable assignment (=). This allows you to construct an associative array where each key corresponds to a column from the result set. Once this logic is corrected, your script will accumulate all rows into one master array rather than replacing previous data.

Here is the corrected approach using the deprecated mysql_* functions as requested, since you specified no object-oriented code:

$sql = mysql_query("SELECT id, name, code FROM users");
$userinfo = array();

while ($row_user = mysql_fetch_array($sql)) {
    // Use [] to add a new element with 'id', 'name', and 'code' keys
    $userinfo[] = array(
        'id' => $row_user['id'],
        'name' => $row_user['name'],
        'code' => $row_user['code']
    );
}

foreach ($userinfo as $usinfo) {
    // Access all properties of the accumulated user object
    echo $usinfo['id'] . ", " . $usinfo['name'] . ", " . $usinfo['code'] . "<br>";
}

The Mechanics of Array Accumulation

In PHP, [] is used to append elements to an array. When you write $userinfo = ..., you are setting the variable's value. When you write $userinfo[] = ..., you are telling PHP to treat $userinfo as a collection and add a new item to the end of it.

Inside your loop, $row_user is already an associative array (assuming you used mysql_fetch_array with the default second argument or omitted it). You can access its elements using numeric indices like $row_user['id']. By wrapping these values in their own array() declaration and appending them to $userinfo, you preserve the structure of every row.

Critical Security and Maintenance Warnings

While this solution addresses your immediate syntax error, there are two massive red flags regarding the code provided in your source material that you must address immediately.

First, the mysql_* extension has been deprecated since PHP 5.5 and removed entirely in PHP 7.0. This function set does not support Prepared Statements, making it impossible to prevent SQL injection attacks. If you are maintaining legacy code, this is acceptable only if no new features are added, but you should plan a migration path. The modern standard is mysqli or PDO, which enforce strict separation of data and queries.

Secondly, accessing columns via $row_user['name'] assumes the column order in your query matches the variable names exactly. It is much safer to rely on the column aliases explicitly defined in the SQL string. If you change your SELECT statement later, relying on position-based arrays (like $row_user[0]) will cause silent failures. Always map columns by name or alias.

If you are starting a new project or have the flexibility to refactor, consider using Laravel. The ecosystem provides robust tools for handling database interactions without writing raw SQL loops manually. For instance, Laravel's Eloquent ORM allows you to iterate over models directly in a collection:

$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->id . ' - ' . $user->name;
}

This pattern handles the array mapping internally and offers better performance through eager loading when dealing with related tables. However, if you are strictly bound to procedural PHP for legacy reasons, the corrected [] assignment logic is the necessary fix to get your data into the array correctly.

Our network