[cancer10]

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

Any help is appreciated.


Thanks