Php efficiently mark messages as read

I have a messaging system (PHP/MYSQL) and I already prepared the field for read/unread in the simple message table

Table
- msgbody
- datetime
- receipent
- read/unread
- sent/unsent (this field is for sending of notification)

I have 2 questions

I understand when user create the message I will insert into the table with the field as Unread, and when I retrieve the message the first time with the following statement
```
 MYSQL statement: Select * from table WHERE receipient = "a";
```

What I do is I go through the array and then I set the unread field to read using multiple insert statements (very inefficient!)

Is there anyway I can forgo the array loop and update the field when I am selecting it?

And is this the correct way to determine whether a message is read?

2) I'm using jquery as my front end, and I can do ajax call to php, but I'm still curious how do I do I determine on the front end whether the user loaded and seen the message or not.

Anyone can shed some light on how should it be done?

Ajax call to update to read when Ajax call to load is successful?
Ajax call to update when database is read?
Ajax call to update ......when?

UPDATE: Added codes. My php code for INSERTING of message

Disclaimer: Codes are working fine. it's the process that I need help with.

$query = $this->db->prepare('INSERT INTO table (msgbody,date time,recipient,read,sent) VALUES(:msg,:date,:user,:read,:sent);
if ($query->execute(array(
':msg' => $msg,
':date' => $datetime,
':user' => $user,
':read' => '0',
':sent' => '0'))) {
return TRUE;
}


My php code for retrieving of message

$query = $this->db->prepare('SELECT * FROM table WHERE recipient = :user);
if ($query->execute(array(
':user' => $user
))){

$data = $query->fetchAll();

foreach ($data as $msg) {
if $msg['read'] == '0';
$this->updateRead($msg['id']);


}

$this->updateRead function includes updating table read field to 1, as well as sending Pusher to the original recipient so they know the message is read.

So what happen is if i have 1000 messages I may end up looping 1000 times just to mark 2-3 messages as Read.

Solution

You can't do the select and update in a single query. But you don't need a loop, you can update all the messages with a single query.

START TRANSACTION;
SELECT * FROM table WHERE recipient = 'a';
UPDATE table SET read = 1 WHERE recipient = 'a';
COMMIT;

If you do these in a transaction, you don't have to worry about new messages being added between the two queries. Transaction atomicity should ensure that they see the same view of the database.