CODEWITHSUNDEEP
phpmysql
PickleBranston
PickleBranston

Reputation: 55

MYSQL: What happens to a result set after the table is changed?

Part of a script I am writing requires me to know exactly how a result set gets it's information from a MYSQL query.

I have a standard result from an SQL query, which I then make a row array using fetch_array.

Whilst looping through this content let's say I delete one of the rows I find from the table. If I reset the pointer of the result back to the first, will I find that row again even though it no longer exists, find an empty row, or miss that row entirely?

In other words is the result asking MYSQL for each row as it needs it, or does it get the whole array in one go so it will not pickup on any changes to the table afterwards?

Cheers in advance

EDIT

$result = $conn->query("SELECT ID, value FROM table");
while($row=$result->fetch_array()){
  if(x){
    $conn->query("DELETE FROM table WHERE ID=$row['ID']");
    mysqli_data_seek($result,0);
  }
}

The question is will that deleted row be repeated after the reset, get skipped or return something else such as NULL?

Upvotes: 3

Views: 61

Answers (1)

Kevin
Kevin

Reputation: 41893

No, it will not delete that row inside initial fetched result set.

But of course it will delete the row in your present table.

If you try to reset the pointer, the initial result set with that row still resides. Not unless you overwrite it with another one.

Consider this example. Lets say you have this inside your table:

+----+--------+
| id | value  |
+----+--------+
| 1  | test1  |
| 2  | test2  |
| 5  | test5  |
+----+--------+

If you make this kind of operation:

$result = $conn->query('SELECT id, value FROM mytable'); // sample query
while($row = $result->fetch_assoc()) { // fetch all results
    if($row['id'] == 5) { // some condition
        $conn->query('DELETE FROM mytable WHERE id = 5'); // delete one row in table
    }
}

$result->data_seek(0); // reset the pointer
while($row = $result->fetch_assoc()) { // fetch the same result set
    echo $row['id'] . '<br/>';
}

It will delete that particular row in your table but not the one in the initial result.

At initial load, it will show:

1
2
5

If you refresh it, now 5 will be gone since its a new request and another result set called.

Upvotes: 2

Related Questions