Correct Methods for Processing Multiple Column Data with mysqli_fetch_array Loops in PHP

Problem Analysis and Common Errors

When using PHP's mysqli extension to process database query results, developers frequently encounter situations requiring iteration through multiple rows while accessing individual column values. A typical error example is as follows:

while($row = mysqli_fetch_array($result)) {
    $posts[] = $row['post_id'].$row['post_title'].$row['content'];
}

Although this code executes, it creates significant data structure issues. The concatenation of multiple column values into a single string using the string concatenation operator completely eliminates the original column boundaries. For instance, when post_id is "4", and both post_title and content are "test", the resulting string "4testtest" cannot be separated back into the three original independent values.

The fundamental flaw in this approach lies in destroying the structured nature of the data. Database query results are essentially two-dimensional tabular data, where each row contains multiple columns with independent semantics. Merging them into a single string not only eliminates column-based access capabilities but may also cause parsing errors if the data content includes delimiter characters.

Solution One: Complete Row Data Storage

The most straightforward and recommended solution is to store the entire row array into the result array:

$posts = array();
while($row = mysqli_fetch_array($result)) {
    $posts[] = $row;
}

This method preserves the complete data structure returned by mysqli_fetch_array. When set to MYSQLI_ASSOC mode, $row is an associative array with column names as keys and corresponding data as values; when set to MYSQLI_NUM mode, it becomes an indexed array. The default MYSQLI_BOTH mode provides both access methods simultaneously.

To access the stored data, nested loops can be used:

foreach ($posts as $row) {
    foreach ($row as $element) {
        echo $element."<br>";
    }
}

If specific data needs to be accessed by column name, direct access is possible:

foreach ($posts as $post) {
    echo "ID: ".$post['post_id'].", Title: ".$post['post_title']."<br>";
}

Solution Two: Structured Array Construction

Another approach involves explicitly constructing a multidimensional array, creating independent structured storage for each row of data:

$x = 0;
while($row = mysqli_fetch_array($result)) {
    $posts[$x]['post_id'] = $row['post_id'];
    $posts[$x]['post_title'] = $row['post_title'];
    $posts[$x]['type'] = $row['type'];
    $posts[$x]['author'] = $row['author'];
    $x++;
}

Although this method requires slightly more code, it offers better readability and explicitness. It clearly demonstrates the data structure and is particularly suitable for scenarios requiring specific column processing or complex data operations. It is crucial to maintain the $x counter to ensure each row's data is stored at a different array index, preventing subsequent rows from overwriting previous ones.

Technical Details and Best Practices

The mysqli_fetch_array function returns the next row from the result set with each call, returning NULL when no more rows are available. When used within loops, this characteristic makes the while loop a natural choice. Understanding this is essential for avoiding common errors.

Regarding HTML tag handling, special attention must be paid to distinguishing between tag descriptions as text content and tags as functional instructions. For example, the <br> tag mentioned in discussions about string concatenation must be HTML-escaped when it is part of text content; otherwise, it will be parsed by the browser as a line break instruction. The correct approach is: echo "Tag example: <br>";.

For large datasets, memory management becomes particularly important. If query results contain numerous rows, consider using mysqli_fetch_assoc or mysqli_fetch_row instead of mysqli_fetch_array to reduce memory overhead. Additionally, promptly release result set resources: mysqli_free_result($result);.

Performance Considerations and Extended Applications

In performance-sensitive applications, the two solutions have different characteristics. The first method (complete row storage) is generally more efficient because it reduces the number of array assignment operations. The second method (structured construction) offers advantages when data validation or transformation is needed, allowing processing of each value before storage.

For more complex data processing requirements, consider implementing an Object-Relational Mapping (ORM) pattern. By creating data model classes, row data can be encapsulated as objects, providing type safety and richer operational methods. For example:

class Post {
    public $id;
    public $title;
    public $content;
    
    public function __construct($row) {
        $this->id = $row['post_id'];
        $this->title = $row['post_title'];
        $this->content = $row['content'];
    }
}

$posts = array();
while($row = mysqli_fetch_array($result)) {
    $posts[] = new Post($row);
}

Although this approach increases initial development costs, it enhances code maintainability and testability, making it particularly suitable for large-scale projects.

Regardless of the chosen method, the key is to maintain data structure integrity, ensuring each column's data can be accessed and processed independently. This is not merely a technical implementation issue but also reflects an understanding of data essence—columns in database tables have independent semantics and purposes, and this independence should be preserved within applications.