I have a table of messages, the structure could probably be better but I have to work with it as it is. The table has these fields:-
`id` int(16) NOT NULL AUTO_INCREMENT,
`to_id` int(16) NOT NULL,
`from_id` int(16) NOT NULL,
`original_message_id` int(16) NOT NULL DEFAULT '0',
`message` text NOT NULL,
`date` datetime NOT NULL,
It holds messages between members, when a message is originally stored a column `original_message_id` = 0. When someone replies to a message, then `original_message_id` will equal the `id` of the original message.
I need to list the original messages, ie `original_message_id` = 0, by date DESC, but I want the date to include the replies. So if a message was sent ages ago, but it just receives a reply I want it listed above messages sent a short time ago.
I have no idea how to do this. If anyone can help I would greatly appreciate it.