CODEWITHSUNDEEP
phpmysqlmysqli
RyanN
RyanN

Reputation: 15

Does PHP mysqli query and fetch_(row/assoc) use real time data?

When we use PHP mysqli->query() and then fetch_row/fetch_assoc in a loop, does PHP cache the data first and give back result as whole (and hence if the row is updated in the meantime, the fetch_row() still returns old data? or does it give us real time data?

Is there any place where we can set this behaviour (e.g. setting database type, php config...?) so that we can control whether PHP should use cached data or real-time data as desire.

For example, we have 2 processes that modify the same DB table 'users':

file 1:

<?php
$res=$mysqli->query("SELECT * FROM users WHERE id > 0 and id < 100 ORDER BY id ASC");
while($r=$res->fetch_assoc()){
    ...do something that takes 10 mins each loop...
    print_r($r['active']);
}

file 2:

$mysqli->query("UPDATE users SET `active` = 'TRUE' where id = 90");

Let say initially all users are having active set to FALSE. We run file 1 on one terminal, then run file 2 right after that on another terminal.

At the time when file 1 comes to process the user "90", does it print out "TRUE" or "FALSE"?

Thank you.

Upvotes: 1

Views: 509

Answers (1)

Hanky Panky
Hanky Panky

Reputation: 46900

$res=$mysqli->query("SELECT * FROM users WHERE id > 0 and id < 100 ORDER BY id ASC");

Your query was executed only on that step. How you access the data after that does not make PHP query the records again. The loop that you're running is on a PHP internal variable that holds all the data returned by the query. There is no further communication with MySQL Server during your loop.

If records are changed after your query and before your display, you will see old data displayed. Not realtime.

To get the latest data which might have been updated by another process during this period (and we're usually talking micro/milli/whatever seconds here), you need to query again.

At the time when file 1 comes to process the user "90", does it print out "TRUE" or "FALSE"?

FALSE

Because when you queried the database it was set to FALSE and it doesn't matter what the current value is unless you query again.

Upvotes: 2

Related Questions