Hi there,
On my database, I have to show a preview with all the posts with their last content and I don’t really know how to do it.
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(11) NOT NULL auto_increment,
`post_id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`body` text NOT NULL,
`created` date NOT NULL,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
INSERT INTO `posts` (`id`, `post_id`, `title`, `body`, `created`) VALUES
(1, 1, 'hello world', 'this is the first post', '2011-10-10'),
(2, 2, 'the second post', 'and this is the second post', '2011-10-10'),
(3, 2, 'second post', 'title is modified, because this is a better version', '2011-10-11');
So the result has to be the post with id 1 and 3. The post with id 2, appears twice because there are two versions on different dates. The dates of the posts marks which version is newer.
I need to display the last state of all the post. How can I do that?