FetchAll vs while loop

[phoenix254]

HELLO i would like to ask that fetchAll() is good choice to retrieve all user message from database or i should use while loop?
I asked this question because i worry about internet traffic..

[pritaeas]

Both retrieve all data from your query. The better optimized your query, the less traffic you have.

[lorenzoDAlipio]

It's a matter of preference. However, regardless of what you use, you still have to loop through the result.

There is not much difference between while loop and foreach loop. There is an 8th of advantage if you don't loop inside the logic of your application. What requires the most server resources is the double looping shown by very last example below.

for example, if you are using PDO, you can easily send the data outside the function as output.

example of data is being return

function getData($query)
{
    $db = dbConnect('localhost','user','pass','database_name');
    /* you will have to add error checking here */
    /* you can also do prepared statements here */

    $result = $db->prepare($query);
    $result->execute();

    return($result->fetchAll(PDO::FETCH_ASSOC));

}

or you can simply return( the query method in PDO)

    return($db->query($sql));

you can call the function above and loop through data

$rows = getData( "SELECT user_name FROM users");

    /* deliver the results */

    foreach($rows as $row){
        echo $row['userName'] .'<br />';
    }

the above is more elegant approach, because you can separate your presentation logic from the data handler or business logic.

codes below will have the same result, except the results are looped inside the function. If you replace the codes above with this

$result = $db->query($query);
    while($row = $result->fetch_assoc()) {
    echo 'user : '. $row['userName'] . '<br />'; 
}

If you want to get rid of the echo, you will not be able to take the results out of it, unless, you iterate through the end of the array and reassign them into a new array.

$d['userName'] = $row['userName'];

    $out[] = $d;

    return $out;

my last example will double the server resources cost, because of double looping. First, the internal while loop second the returned $out array will have to be looped through foreach. Making it less favorable in high traffic sites.

[phoenix254]

@lorenzoDAlipio
U wrote too much. btw i ddint find anything about traffic

[diafol]

U wrote too much. btw i ddint find anything about traffic

Perhaps you should read lorenzo's post again. The last line actually spells it out.

IMO, it makes little difference on small datasets, speed-wise, but you may encounter differences in memory usage. fetchAll() dumps the entire dataset into an array in one go. So for 100 records, you get all that in one bang and the resulting memory allocation that goes with it. fetch() on the other hand retrieves one record at a time and only allocates a single record's worth of memory on each iteration (if you are re-using the same variable). However, if you are re-creating an output array from a fetch() loop (mimicking a fetchAll()), then memory considerations will be similar, but will be slower.

I love the convenience of fetchAll() and I probably over-use it in some sites, but my datasets are quite small and site traffic is not that big. If you are going to display data in a page, how many records do you really need to show? With modest size datasets, you may get more bangs for your bucks by using a LIMIT clause and ensuring that you are not fetching number-based AND associated arrays.

The default fetch is PDO::FETCH_BOTH. This is pointless for most uses, so pick one, either PDO::FETCH_ASSOC or PDO::FETCH_NUM. The are other FETCH options - so make sure you read up on them, as these may help with speeding up further processing by PHP.

One particular advantage of using fetchAll() that I like is the ability to pull all values from one column and using them as a key for the rest of the record:

$sth->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP)

This 'groups' records by the first column in your query. I remember in the bad old days trying to do this in a loop. So, sometimes fetchAll() can be very good. My 2p.

*Caveat: I'm no expert on this, just some random stuff I've picked up along the way - so would be pleased if somebody could correct me if I'm wrong. *

[Gideon_1]

I think fetchAll() is better since you get everything your sql returns in a single dump. Catch ya.

[gentlemedia]

U wrote too much. btw i ddint find anything about traffic

You could also respect his time and say: "Thank you!"

[phoenix254]

If i have more than 5000 records than what i should use ?

[pritaeas]

Do you need them all on a single page?

[phoenix254]

yes

[Gideon_1]

Then Fetching is your friend

[phoenix254]

fetching ??? do u mean

    $result = $db->query($query);
        while($row = $result->fetch_assoc()) {
        echo 'user : '. $row['userName'] . '<br />'; 
    }

[jkon]

This question and the answers given , puzzled me . Let me remind that the question is “HELLO i would like to ask that fetchAll() is good choice to retrieve all user message from database or i should use while loop? I asked this question because i worry about internet traffic..”.

What has to do how to retrieve data from db with the worry about internet traffic ? . One explanation I could give is that phoenix254 is not worring about internet traffic , but of what is the best method for an application that has a lot of traffic. If this is the case we should look to the question once again , it is implied that for some reason the application should retrieve ALL the messages for a specific user (we don't know more) and we compare fetchAll method of PDO vs fetch with a loop method of PDO.

Probably the application has no reason to retrieve all the messages of a user at once , ( a user might have 1.000.000 messages) it would be best to use pagination or / and retrieve and display only the basic info of each message and then the body and other details of it.

The peak memory usage benefit of fetch vs fetchAll has meaning in a batch job , where we just need the specific record without output anything of it. But again in this case there are other architectural approaches that will work even better depending the need.

This need to use fetch vs fetchAll in an application to reduce peak memory usage is a sign (in my opinion of course) that there is something wrong with the architecture of it. More over in the given examples it forces the use of View inside Model that again in my opinion is a bell that something is problematic. (It could be done without the use of View inside Model , but there must be really a reason to go that way)