Getting the most recent post based on date
- by camcim
Hi guys,
How do I go about displaying the most recent post when I have two tables, both containing a column called creation_date
This would be simple if all I had to do was get the most recent post based on posts created_on value however if a post contains replies I need to factor this into the equation.
If a post has a more recent reply I want to get the replies created_on value but also get the posts post_id and subject.
The posts table structure:
CREATE TABLE `posts` (
`post_id` bigint(20) unsigned NOT NULL auto_increment,
`cat_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`subject` tinytext NOT NULL,
`comments` text NOT NULL,
`created_on` datetime NOT NULL,
`status` varchar(10) NOT NULL default 'INACTIVE',
`private_post` varchar(10) NOT NULL default 'PUBLIC',
`db_location` varchar(10) NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
The replies table structure:
CREATE TABLE `replies` (
`reply_id` bigint(20) unsigned NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`comments` text NOT NULL,
`created_on` datetime NOT NULL,
`notify` varchar(5) NOT NULL default 'YES',
`status` varchar(10) NOT NULL default 'INACTIVE',
`db_location` varchar(10) NOT NULL,
PRIMARY KEY (`reply_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
Here is my query so far. I've removed my attempt of extracting the dates.
$strQuery = "SELECT posts.post_id, posts.created_on, replies.created_on, posts.subject ";
$strQuery = $strQuery."FROM posts ,replies ";
$strQuery = $strQuery."WHERE posts.post_id = replies.post_id ";
$strQuery = $strQuery."AND posts.cat_id = '".$row->cat_id."'";
