Ok at this point, I am pretty confused on how to construct my pagination system in an efficient manner. The problem is that the system is not typical and does not go in regular intervals such as 10 per page. The problem is, messages can have replies and thus share the same reply_chunk_id (reply_id). I do not want messages to get cut off but it is seeming increasingly complex to do so.
My initial query to retrieve message is this and it retrieves messages as a grouping no matter where a reply is in the table, it will come out grouped with its corresponding messages with the same reply_chunk_id one after another in descending order based on timestamp
SELECT m.timestamp, m.user, m.message, g.grp_timestamp,m.reply_chunk_id
FROM shoutbox AS m JOIN
(SELECT reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox
GROUP BY reply_chunk_id) AS g ON m.reply_chunk_id = g.reply_chunk_id
WHERE m.topic_id = ?
ORDER BY g.grp_timestamp DESC, g.reply_chunk_id, m.timestamp DESC limit ?, ?
I was thinking that I would need another query to retrieve the limit parameters for this query since the pages do not go in regular intervals and thus need unique starting and ending points depending on what page you have selected. I was thinking of picking a limit say $limit of 10 just as an example, and then going to the nearest rounded number of messages over that. So for example, if you got to the 10th message and there were 2 more replies for that group, you would retrieve 12 messages.
The trouble I am having is with the logic of constructing this limit retrieving query. I would have to somehow start at the first message in that topic, count all replies for it, go to the second, count all replies until the rounded number is reached and then output it.
The real trouble comes when say you want to change the page, how would you transfer over the ending point of the previous page, or maybe you skip a page and go straight from 1-3rd page. The answer is you cannot so you would have to start from the first message for that topic each time, count all replies, move on and do the same for each message until you reach your rounded number, somehow indicate you have passed the first page, and move on until you get to the page messages you desire. I am really not sure how to do this, or if this is the best way so any help or advice whatsoever is really appreciated.
TABLE DESIGN
CREATE TABLE IF NOT EXISTS `shoutbox` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`user` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
DEFAULT 'anonimous',
`message` varchar(2000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`topic_id` varchar(35) NOT NULL,
`reply_chunk_id` varchar(35) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;