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:-
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.Code:`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,
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.