MySQL: Forum teaser, how to get latest post date and author name?

Hello,

I am writing a small forum software and I am stuck to a feature where I need to show a summary of the forum's latest post title, latest post date, and latest post author.

So there is a

1) Thread table.
2) Comments table: This is related to the thread table with the cmnts_thr_id foreign key.
3) Users table.


The latest post date is to be identified by comparing the following 4 dates:

• threads_tr.thr_date_created
• threads_tr.thr_date_updated
• comments_cmnts.cmnts_date_created
• comments_cmnts.cmnts_date_updated

and whichever date is greater among the above 4, the summary should display that particular date along with the thread title and the user's name who has recently updated the thread or comment.

DDLs:

CREATE TABLE `threads_thr` (
  `thr_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `thr_usr_id` INT(10) DEFAULT NULL,
  `thr_title` VARCHAR(64) DEFAULT NULL,
  `thr_description` TEXT,
  `thr_date_created` DATETIME DEFAULT NULL,
  `thr_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`thr_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1



CREATE TABLE `comments_cmnts` (
  `cmnts_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cmnts_usr_id` INT(10) DEFAULT NULL,
  `cmnts_thr_id` INT(10) UNSIGNED DEFAULT NULL,
  `cmnts_message` TEXT,
  `cmnts_date_created` DATETIME DEFAULT NULL,
  `cmnts_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`cmnts_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1


CREATE TABLE `users_usr` (
  `usr_id` INT(10) NOT NULL AUTO_INCREMENT,
  `usr_first_name` VARCHAR(66) NOT NULL,
  `usr_last_name` VARCHAR(66) NOT NULL,
  `usr_email_address` VARCHAR(255) DEFAULT NULL,
  `usr_password` VARCHAR(100) NOT NULL,
  `usr_date_created` DATETIME NOT NULL,
  `usr_date_updated` DATETIME DEFAULT NULL,
  PRIMARY KEY (`usr_id`),
  KEY `email_address` (`usr_email_address`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Code (markup):

Any help is appreciated.


Thanks

 

The below should give the date and user id. Join it with the other tables to get the required details

SELECT  max(thr_date_created) 'last_date',thr_usr_id 'user_id' FROM threads_thr WHERE thr_id=1 UNION SELECT max(thr_date_updated),thr_usr_id FROM threads_thr WHERE thr_id=1 UNION SELECT  max(cmnts_date_created),cmnts_user_id FROM comments_cmnts WHERE cmnts_thr_id=1 UNION SELECT  max(cmnts_date_updated),cmnts_user_id FROM comments_cmnts WHERE cmnts_thr_id=1 ORDER by last_date LIMIT 1

Code (markup):

 

Thanks for your help Sir.