Hello,
We have a messaging system on our site that pools together all messages of all users in one database using an auto-increment and want to implement a feature that allows a user to view the PREVIOUS and NEXT message when he's viewing a message. The problem is that since the auto-increment is global and not consecutive for a particular user, we don't know how the System can know which is the next message for that user and which is the previous message.
To help you understand what I mean, here's how it's currently working, starting with the table we are using:
-- --------------------------
----------
----------
-------
-- Table `Dating`.`Inbox`
-- --------------------------
----------
----------
-------
CREATE TABLE IF NOT EXISTS `Dating`.`Inbox` (
`idInbox` INT(11) NOT NULL AUTO_INCREMENT ,
`idUsers` INT(11) NULL DEFAULT 0 ,
`from` VARCHAR(20) NULL ,
`to` INT(11) NULL DEFAULT 0 ,
`subject` VARCHAR(80) NOT NULL ,
`message` TEXT NULL ,
`date` INT(11) NULL DEFAULT 0 ,
`viewed` CHAR(1) NULL DEFAULT 'N' ,
`replied` CHAR(1) NULL DEFAULT 'N' ,
PRIMARY KEY (`idInbox`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
When a user enters the message inbox, the system controls whether there are messages for that user whose user ID corresponds with the second column which says "idUser". So if the user's idUsers = 3, the system finds all the messages for user 3. Then there are two other columns "viewed" and "replied" to control whether a particular message has been viewed or replied.
The links to each message in the inbox include the idUsers (unique to the user) and the idInbox (unique to the message), which auto-increments. So, for example, if that user has 2 messages in his inbox, both messages will have idUsers = 3 in the URL, but each message will have a idInbox that is unique to that message and which auto-increments, but that are NOT necessarily consecutive.
So the problem is, if a user if viewing a message, how can the system know what is the PREVIOUS and NEXT message (idInbox) in their inbox since the auto-increment is global and not consecutive for users?
Just to be clear, here's another example. User 3 has only 3 messages in his inbox for which the idInbox variables are 1, 7, 13. If the user is viewing message idInbox = 7, how can the system know that the message before idInbox = 7 for idUsers = 3 is idInbox = 1 and that the next message is idInbox = 13 for that user?
How would we do that? Or do you recommend re-doing the messaging system from scratch to make this easier to accomplish (although we prefer not to, that is an option as we're still in beta with few users). If so, how do you suggest we do it?
Thank you very much.
