In PHP should I use WHILE with a mysql result or an array?

Question

Below is just a mockup of 2 codes in php/mysql.
The first one returns a mysql result and run a while loop to iterate the results, the second one does almost the same thing but instead puts the result into a query and then puts it to screen. Now for a more OO approach the second method would be better I think (not sure, i'm learning) however where I am confused is, wouldn't using the second method below use twice as much memory? Isn't it basicly creating 2 arrays instead of 1?

Putting it into an array seems a lot more flexible, please give me your tips/advice. Keep in mind it would not be a simple thing ran 1 time on a page either, a real world example on a page on my site that array might contain 5,000 usernames and userID number in the array and multiple queries might be made on the same page all with different data.

<?PHP
// first method without using array
$url = "http://www.google.com/";
$sql = "SELECT user_id, nickname FROM `".TABLE_USERS."`
          WHERE referrer LIKE '".$db->escape($url)."%'
          ORDER BY nickname DESC
          LIMIT 0,10";

$rows = $db->query($sql);

while ($record = $db->fetch_array($rows)) {
    echo "<tr><td>$record[user_id]</td>
          <td>$record[nickname]</td></tr>";
}
//////////////////////////////////////////////////////////////////////
//second method using array
$url = "http://www.google.com/";
$sql = "SELECT user_id, nickname FROM `".TABLE_USERS."`
          WHERE referer LIKE '".$db->escape($url)."%'
          ORDER BY nickname DESC
          LIMIT 0,10";

// feed it the sql directly. store all returned rows in an array
$rows = $db->fetch_all_array($sql);

// print out array later on when we need the info on the page
foreach($rows as $record){
    echo "<tr><td>$record[user_id]</td>
          <td>$record[nickname]</td></tr>";
}

?>

Answer 1

while looks better to me, because it leaves the possibility not to load the whole array of data into memory.

Answer 2

Hi,

The first option is faster since it requires less calculation from the processor.

Answer 3

The second method will use a lot more than twice as much memory. The first method is only fetching a single row at a time and sending it to STDOUT. The second method reads in every row into an array. If you have 5000 rows, it's going to use approximately 5000 times the memory (not accounting things like PHP's internal output buffering, etc).

The second method does allow you to manipulate the data much more, but it definitely uses a lot more memory. If you are really dealing with that much data on a page, you might have to find ways to break it down if you start to run into resource problems.

Answer 4

This has nothing to do with OO. To be OO, you'd need to populate some objects in the loop. Like this, for example:

$o = new MyObject();
$o->aField = $record['aField'];
$objects[] = $o;

But in this case, you are just doing printing to the page, and the two approaches are just a different way of transferring those data.

The first approach uses one array at a time, in the size of the record. The second one uses one array in the size of one record times the number of records. Since you are not processing the entire array anyway, go with the first one.