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

Any help is appreciated.

Thanks

Why are you stuck, what language is the forum software written in?

Steve

he’s probably stuck on “whichever date is greater among the above 4, the summary should display that particular date along with …”

i gave it a try and it’s too complicated for me to do in one query

Hi, Its in PHP.

there must be someway, how do the forums like phpbb or simplemachine do it?

@cancer10

Hi this is an outline, you will have to fill in the missing peices:


DATE_FORMAT(MAX(created), '%b %e')
SELECT 
  thr_id
  , MAX(DATE_FORMAT(thr_date_created, '%b %e')) AS lastdate_created
  , MAX(DATE_FORMAT(thr_date_created, '%b %e')) AS lastdate_updated 
FROM 
  threads_thr
// execute the sql

// from the results of your query
$thr_id = $row['thr_id'];
$thr_lastdate_created = $row['lastdate_created'];
$thr_lastdate_updated = $row['lastdate_updated '];
  
  
SELECT 
  cmnts_id
  , cmnts_usr_id
  , cmnts_thr_id
  , MAX(DATE_FORMAT(cmnts_date_created, '%b %e')) AS lastdate_created
  , MAX(DATE_FORMAT(cmnts_date_updated, '%b %e')) AS lastdate_updated
FROM 
  comments_cmnts
// execute the sql

// from the results of your query
$cmnts_id = $row['thr_id'];
$cmnts_thr_id = $row['cmnts_usr_id'];
$cmnts_thr_id = $row['cmnts_thr_id'];
$cmnts_lastdate_created = $row['lastdate_created'];
$cmnts_lastdate_updated = $row['lastdate_updated '];


SELECT
  usr_id
  , MAX(DATE_FORMAT(usr_date_created, '%b %e')) AS lastdate_created 
  , MAX(DATE_FORMAT(usr_date_updated, '%b %e')) AS lastdate_updated 
FROM 
  users_usr
// execute the sql

// from the results of your query
$usr_id = $row['thr_id'];
$usr_lastdate_created = $row['lastdate_created'];
$usr_lastdate_updated = $row['lastdate_updated '];

// create a php array of the latest dates
$latest_dates = array(
  'thr' => $thr_lastdate_created
  , 'thr' => $thr_lastdate_updated 
  , 'cmnts'=> $cmnts_lastdate_created
  , 'cmnts' => $cmnts_lastdate_updated
);

$the_ids = array(
  $thr_id => $thr_lastdate_created
  , $thr_id => $thr_lastdate_updated 
  , $cmnts_id => $cmnts_lastdate_created
  , $cmnts_id => $cmnts_lastdate_updated
);





// sort date lowest to highest date
asort($latest_dates);
// get either 'thr' or 'cmnts' of the that will be used in the final sql query i.e: it returns the value of $cmnts_id
$cmnts_or_thr = array_pop(array_keys($latest_dates));
// return the value of the end array value
$lastestdate = end($latest_dates);
asort($the_ids);
$cmnts_or_thr_id = array_pop(array_keys($the_ids));




// Then build your an sql statment that uses the variables collected and create the JOINS
// You will need to use [COLOR=#0000BB]strpos[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]$mystring[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]$findme[/COLOR][COLOR=#007700])[/COLOR]; to determine if $cmnts_or_thr_id is a 'cmnts' or a 'thr'. Once you determine this
// you will use conditional logic to put the correct values into your sql statment with the JOINS

Hope this helps.
Steve

i’m not sure, but i’ll bet you a coffee and doughnut it isn’t “whichever date is greater among the above 4”

@ServerStorm, Thanks so much sir, I will try this when I reach home. Also, within your query, will it be possible to find a count of the total # of comments for each thread?

@r937, Well I am not sure too if they compare the 4 dates, but in that case what do you think they do to display the last updated post and the name of the author?

I will have to look into this tonight… I wonder if your database design wouldn’t benefit with some mapping tables like users2threads and threads2comments with a post type lookup table and a post table with id, post, post_type, date_entered, date_updated then when you want to find the most current posts you look for the highest id and then return the results by joining the post table with the users2threads and threads2comments table?

Regards,
Steve

first of all thank you once again for taking time and replying to my query.

Secondly, As per your suggestion, I have altered the column named of my tables.

The structure looks like this:




CREATE TABLE `thread` (
  `thread_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `project_id` INT(10) DEFAULT NULL,
  `title` VARCHAR(64) DEFAULT NULL,
  `description` TEXT,
  `lock_thread` ENUM('YES','NO') DEFAULT 'NO',
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `last_post_time` INT(10) DEFAULT CURRENT_TIMESTAMP,,
  PRIMARY KEY (`thread_id`)
) ENGINE=MYISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1


CREATE TABLE `comment` (
  `comment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(10) DEFAULT NULL,
  `thread_id` INT(10) UNSIGNED DEFAULT NULL,
  `comment` TEXT,
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,,
  PRIMARY KEY (`comment_id`)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1




CREATE TABLE `user` (
  `user_id` INT(10) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(66) NOT NULL,
  `last_name` VARCHAR(66) NOT NULL,
  `email_address` VARCHAR(255) DEFAULT NULL,
  `password` VARCHAR(100) NOT NULL,
  `role_id` INT(5) NOT NULL,
  `is_active` ENUM('YES','NO') NOT NULL DEFAULT 'YES',
  `date_created` DATETIME NOT CURRENT_TIMESTAMP,
  `date_updated` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `email_address` (`email_address`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


Thirdly, I found a simpler solution for finding out the last updated thread. I added a column “last_post_time” in the thread table. This field will be updated each time a comment is posted for that thread. In that way I can know which thread has latest comment.

however I seem to have a problem here. I want to get the count of all comments for each thread. Can this be done easily within one single query?

Thanks in advance

@cancer10

Have you tried doing a Subquery to get the number of comments:


SELECT
  u.first_name || ', ' || u.lastname AS Fullname
  , t.title AS Thread
  , t.date_updated as Date
  , (SELECT
        COUNT(c.thread_id) 
     FROM
        comments as c
     WHERE
        t.thread_id = c.thread_id
    ) AS as 'Comments Count'
[...}

Steve

normally, it is recommended that one never store in a table a value which is obtainable elsewhere

however, exceptions abound, and this is one of them

nice job

:slight_smile:

Hi, I will try the subquery thing as u suggested. I have two questions:

  1. Is a subquery really needed for achieving this?
  2. I am trying the following query but it does not show the correct count of comments per thread.
    Any idea why?

SELECT 
	thread.title, 
	CONCAT(user.first_name,' ', user.last_name) AS full_name,
	COUNT(comment.comment_id) AS comment_count
FROM thread
LEFT JOIN COMMENT ON (comment.thread_id=thread.thread_id)
LEFT JOIN USER ON ( (thread.user_id=user.user_id) OR (comment.user_id=user.user_id) )
GROUP BY thread.thread_id



Many thanks

@cancer10

Hi

While your query:


SELECT 
    threads.title, 
    CONCAT(users.first_name,' ', users.last_name) AS full_name,
    COUNT(comments.comment_id) AS comment_count
FROM threads
LEFT JOIN comments ON (comments.thread_id=threads.thread_id)
LEFT JOIN users ON ( (threads.user_id=users.user_id) OR (comments.user_id=users.user_id) )
GROUP BY threads.thread_id

Produces the wrong results (as you mention).
[TABLE=“class: grid”]
[TR]
[TD]1[/TD]
[TD]Welcome to the forum[/TD]
[TD]Sue Jasper[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]problem with Div[/TD]
[TD]Sue Jasper[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]My Second Post[/TD]
[TD]Yuri Yankavic[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]My First Post[/TD]
[TD]Yuri Yankavic[/TD]
[TD]5[/TD]
[/TR]
[/TABLE]

While doing the query this way produces the correct results.:


SELECT 
  t.title, 
  CONCAT(u.first_name,' ', u.last_name) AS full_name,
  (SELECT
        COUNT(c.thread_id) 
     FROM
        comments as c
     WHERE
        t.thread_id = c.thread_id)
FROM threads as t
LEFT JOIN comments as c
  ON (c.thread_id=t.thread_id)
LEFT JOIN users as u
  ON ((t.user_id=u.user_id) 
       OR 
     (c.user_id=u.user_id))
GROUP BY t.thread_id

[TABLE=“class: grid”]
[TR]
[TD]title[/TD]
[TD]last_posters_full_name[/TD]
[TD]comments_per_thread[/TD]
[/TR]
[TR]
[TD]Welcome to the forum[/TD]
[TD]Sue Jasper[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]problem with Div[/TD]
[TD]Sue Jasper[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]My Second Post[/TD]
[TD]Yuri Yankavic[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]My First Post[/TD]
[TD] Yuri Yankavic[/TD]
[TD]3[/TD]
[/TR]
[/TABLE]

Here are all the comments to verify, you can get an idea of the threads and users tables from the results above:
[TABLE]
[TR]
[TD]comment_id[/TD]
[TD]thread_id[/TD]
[TD=“width: 55”]user_id[/TD]
[TD=“width: 1166”]comment[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Awesome forum, some members are really helpful![/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]Thanks Yuri that works but not I have this happening could you help? […][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]Thanks Yuri[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Hi Sue,

Your <div> problem is in your css here:
<pre>
#gardens {
float: none;
}
</pre> […][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]Sorry I was frustrated an spoke before I thought. I will
try to start things (again) on the right foot.

Best,
Yuri[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]Hi Yuri,

Welcome to the forum. However this post is too strongly
worded for this forum, we would appreciate that you put
a foundation to such claims and that you keep your tone
more civil.

Steve[/TD]
[/TR]
[/TABLE]

Hope this helps you see why the sub-query is needed and how to get the right results.

Regards,
Steve