将MySQL EXACTLY ONCE中的信息显示给Android用户

Question

suppose I have this MySQL table:

+-----+-------------+------+
| id  | info        | seen |
+-----+-------------+------+
| 1   | bla bla     | 0    |
+-----+-------------+------+

I want to build a RESTful API where an android client provides the rowID and gets to see the information. After I echo the Information I set seen=1 such that the user cannot access the information a second time.

Suppose I do all of this in one request like:

- $id = $_GET['id']
- SELECT * FROM table WHERE seen=0 and id=$id
- echo $info
- UPDATE table SET seen=1 WHERE id=$id

what happens if the user looses connection and 'seen' is updated 1 even if the user never received $info? how can I make sure this never happens/ how can I detect this?

I also thought about sending two requests, one to get the $info and one to approve that the information was received. But this way the user could just receive the information multiple times by not sending the received message.

thanks for any help :)

Answer 1

You could assign the variable in the update statement:

UPDATE table
   SET seen = if(@info := info, 1, 1)
   WHERE id = $id;