In MySQL, how to get the next row of data while taking into account time changes in the data while sorting by the sort column?

Question

Okay, my question is a bit specific and hard to explain. So I'll try to simplify it.

I have a MySQL table that displays "prompts" from a website. The prompts are sorted by the integer in the Order column. So the table looks like this: id (incrementing int), prompt (varchar), order (int).

Now when the first prompt is shown to the user, it will be shown until the user confirms, then the next prompt will be shown, and so on.

I came up with a query to get the next prompt based on the user's last confirmed prompt:

SELECT hint FROM hints WHERE `order` > $last_seen_item_order ORDER BY `order` DESC LIMIT 1

This query is very useful. However, we sometimes need to add a new prompt, usually not at the last prompt, but somewhere in the middle. So for example, the user saw the last prompt in order #6, but we added a new prompt at position #3. This new prompt will never be shown to this user because we have saved that he has already seen prompt #6.

Is there a way to manage this problem? Maybe only one or two MySQL queries are needed?

Thanks in advance for any help and tips.

Edit: Each user has his or her own "seen status". We simply save it in PHP's $_SESSION['last_seen_item_order'].

Answer 1

You can't manage it with this logic.

For this you need to maintain an extra column - seen If the user has already seen hints, you can set it to 1 So your query will be -

SELECT 
   hint 
FROM
  hints 
WHERE 
  `order` > last_seen_item_order
   OR seen = 0 
ORDER BY 
   CASE WHEN `order` > last_seen_item_order THEN `order` END DESC
   CASE WHEN `order` <= last_seen_item_order THEN `id` END ASC
LIMIT 1

NOTE - This is what I recommend. You can have many other ideas.

edit - If you want to maintain prompts on a per-user basis, then you may have two options for maintaining seen.

1. Maintain seen prompts in json format for users.
2. Create a separate table named user_hints_see, which contains id (increment), user_id, hint_id, seenColumn.