How to Use mysql_fetch_row with a while Loop to Iterate Over Multiple Database Records?

In PHP, when working with MySQL databases, it's often necessary to iterate through multiple records in a result set. The mysql_fetch_row() function is a classic function used to retrieve a row from the result set as a numerically indexed array. While modern development recommends using mysqli or PDO, understanding how mysql_fetch_row() works remains helpful for learning the fundamentals of database operations.

This article provides a detailed explanation of how to use the mysql_fetch_row() function in combination with a while loop to iterate through multiple rows returned by a database query.

1. Introduction to mysql_fetch_row()

The mysql_fetch_row() function retrieves the next row from a result set each time it is called, returning it as a numerically indexed array. For example, if a table has three columns, the returned array will have indexes 0, 1, and 2.

When there are no more rows, the function returns false, which is also the condition for the while loop to terminate.

2. Basic Workflow

Let's assume we've successfully executed a SQL query:

$query = "SELECT id, name, email FROM users";
$result = mysql_query($query);

$result is the result resource from the query, and we can use mysql_fetch_row() to iterate through all rows.

3. Iterating with a while Loop

Use a while loop to control the iteration, calling mysql_fetch_row() until it returns false. During each loop, we can access each column of the current row.

Example:

while ($row = mysql_fetch_row($result)) {
    // $row[0] is id
    // $row[1] is name
    // $row[2] is email
    echo "ID: " . $row[0] . ", Name: " . $row[1] . ", Email: " . $row[2] . "<br>";
}

This allows us to output each record one by one.

4. Complete Example Code

The following example demonstrates the entire process from connecting to the database, executing the query, and iterating through the results:

<?php
// Connect to the database
$link = mysql_connect("gitbox.net", "username", "password");
if (!$link) {
    die("Could not connect: " . mysql_error());
}
// Select the database
mysql_select_db("testdb", $link);
// Execute the query
$query = "SELECT id, name, email FROM users";
$result = mysql_query($query);
if (!$result) {
    die("Query failed: " . mysql_error());
}
// Use while and mysql_fetch_row to iterate over results
while ($row = mysql_fetch_row($result)) {
    echo "ID: " . $row[0] . ", Name: " . $row[1] . ", Email: " . $row[2] . "<br>";
}
// Close the connection
mysql_close($link);
?>

5. Notes

• The mysql_* functions have been deprecated as of PHP 7.0. It is recommended to use mysqli or PDO.

• mysql_fetch_row() returns a numeric array. If you need an associative array, use mysql_fetch_assoc().

• When iterating over large datasets, using a loop with mysql_fetch_row() can efficiently handle the data.