OK, my problem is little bit specific and harder to explain. So I will try to simplify as much as possible.

I have MySQL table with "hints" that are beeing shown on website. These hints are ordered by int in column "order". So table looks like this: id(int autoincrement), hint (varchar), order(int).

Now when first hint is displayed to user, it is displayed until user acknowledges it and then next hint is displayed and so on.

I came up with query, which gets me next hint based on which hint specific user acknowledged last time:

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

This works fine. However we also need to add sometimes new hint and it's not usually added as the last hint but somewhere in between. So for example, user has seen last hint with order #6, but we added new hint at position e.g. #3. And it will never be displayed to this user, because we have saved for him that he has seen hint #6.

Is there a way how to manage this ? Possible with only by one or two MySQL queries ?

Thank you for any help and hints in advance.

EDIT: Each user has its own "seen-state". We keep that simply in PHP $_SESSION['last_seen_item_order']

CodePudding user response：

You can't manage it by this logic.

For this, you need to maintain an extra column - seen You can set this to 1 if the user have seen the hints So your query would 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 my suggestion for doing that way.You can have number of ideas of doing it.

Edit - If you want to maintain user wise hints, then you probably have 2 options to maintain seen.

1. Store user wise json for hints seen by user.
2. Make a separate table user_hints_see with columns id(Autoincrement), user_id, hint_id, seen.