PHP使用for循环来迭代MySQLi记录集

Question

Whenever I am working with PHP MySQLi recordsets, I have always worked with the returned data using the standard while loop to iterate over the recordset. Recently, however, I started wondering if there is a way to use a for loop instead. This would be handy in situations where you want to limit the number of results returned.

---

Here is an example of using the while loop:

//Prepare a query that will produce a reverse-order recordset
$sql = "SELECT * FROM tblNames ORDER BY numberID DESC";
$recordset = $conn -> query($sql);

//Count the number of contacts added to the list
$contactCount = 0;

while($row = $recordset -> fetch_assoc())
{   
    //If the list has reached its maximum number (5), end the display loop
    if($contactCount >= 5)
    {
        break;
    }

    $contactList .= $row["name"] . "<br>";

    //Increment the number of contacts added to the list
    $contactCount ++;
}

//Use '$contactList' somewhere....
echo($contactList);

---

While this definitely works, there must be a better way to end the loop after a specified number of iterations. Is it easier to use a for loop in a situation like this? If so, how?

Answer 1

You can use LIMIT in the query. For example:

SELECT * FROM tblNames ORDER BY numberID DESC LIMIT 15

This way you don't have to worry about what happens if your query does return less than 15 results.

Answer 2

As I was writing this question, I suddenly decided that I would try it one last time, but in a different way than I had been previously. I had been stuck finding an efficient/safe way to tell when the recordset was empty (had been running into issues when the custom max number was greater than the number of records, and when there were no records).

---

//Execute the SQL query (reverse order), and store the results in a recordset
$sql = "SELECT * FROM tblNames ORDER BY numberID DESC";
$recordset = $conn -> query($sql);

//Use a 'for' loop to iterate over the recordset
for($i = 0; $i < 15; $i++)
{ 
    //If there is another row in the recordset, add the column value to the list
    if($row = $recordset -> fetch_assoc())
    {
        $contactList .= $row["name"] . "<br>";
    }
    else
    {
        //Break from the loop when there are no more records (used if the 
        //   given maximum number was actually greater than the number of records)
        break;
    }
}

echo($contactList);

As far as I can tell, this is a much better way to loop through a set/custom number of records, and then stop. It also will safely catch the end of the recordset (assuming it is reached before the cutoff number), and end the loop.

---

Edit

As is pointed out in the answer by HenryTK above, if you have control over the query, the best way is to use the LIMIT SQL statement. However, if you merely have access to the recordset, I still think the for loop will save time. (Although I'm not sure when this would happen).