Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question 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:

    Code:
    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
    Last edited by phantom007; 01-15-2012 at 08:10 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    I hope you won't take this wrong, but I think you have a couple of flaws in your database design that are making not only this query but other queries in the future more complex than needed.

    I, personally, would kill off these fields from your threads table:
    Code:
      `thr_description` TEXT,
      `thr_date_created` DATETIME DEFAULT NULL,
      `thr_date_updated` DATETIME DEFAULT NULL,
    and then, instead, I would *only* use the corresponding fields in the comments table.

    In other words, the first message in the thread is simply stored as the first comment in that thread.

    And, too, I would *ALWAYS* set the cmnts_date_updated field, even when that comment is first posted. So a comment that has not been updated would have that field and cmnts_date_created identical.

    Now you only have to do a really simple query to find the latest post in a thread:
    Code:
    SELECT * FROM comments ORDER BY cmnts_date_updated DESC LIMIT 1
    (And of course you can join to the threads and users tables to get the thread title and the user's name, but that won't affect the basic ORDER BY and LIMIT 1.)

    Note that you should add an index on cmnts_date_updated if you want any decent performance at all.

    I really believe this will simplify a lot of things. For example, to get the text of all postings in a thread, you only have to look in the one table; you don't have to treat the first post special to get it from the threads table, as well.

    If you aren't amenable to these changes, we can build a query to do what you want, but it will be a lot clumsier.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-21-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Oh, incidentally, you should never declare foreign keys to be NULLable, even with MyISAM tables. And I also strongly recommend than you declare foreign keys even in MyISAM tables, just for self-documentation, if no other reason.

    Finally, why not provide default values when possible?
    Code:
    CREATE TABLE `comments_cmnts` (
       cmnts_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       cmnts_usr_id  INT REFERENCES users_usr(usr_id),
       cmnts_thr_id  INT UNSIGNED REFERENCES threads_thr(thr_id),
       cmnts_message TEXT,
       cmnts_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       cmnts_date_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       INDEX (cmnts_date_updated),
       INDEX (cmnts_thr_id)
    );
    You clearly need the two indexes I added there. The one on cmnts_date_updated for finding latest posts. The one on cmnts_thr_id for finding all posts in a given thread quickly.

    **********

    And I have to ask: Why the "double names" on your tables? Why "comments_cmnts" instead of just "comments"??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-21-2012)

  • #4
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    first of all thank you once again Old Pedant 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:

    Code:
    
    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

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    I am trying the following query but it does not show the correct count of comments per thread.
    Any idea why?

    Code:
    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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Sure. Change COUNT(commen_id) to just comment_id and omit the GROUP BY, temporarily, and you'll clearly see the reason why.

    That is, do this:
    Code:
    SELECT 
    	thread.title, 
    	CONCAT(user.first_name,' ', user.last_name) AS full_name,
    	comment.comment_id
    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) )
    This is one reason why I told you to kill the user_id on the thread table.

    You are getting duplicate matches, so the comment_id gets counted multiple times.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Any time you have a single JOIN the matches a primary key to more than one foreign key, you can almost guarantee that you have a flaw in your db design. As you do here.

    Sorry, but I still say you need to rethink the design here.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #8
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question

    Quote Originally Posted by Old Pedant View Post
    This is one reason why I told you to kill the user_id on the thread table.
    Even if I kill the user_id on the thread table, how am I supposed to know which user posted the thread?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Go back and carefully read what I said in post #2.

    I quite frankly don't understand why you treat the first post in a thread special. It will only cause you grief in the long run.

    For example, if you want to offer a SEARCH through all posts, you would have to UNION the thread and comment tables in order to effectively search *all* posts.

    I'm sorry, but I think you have a design flaw that's going to come back and bite you in more ways in the future.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    (That doesn't mean you shouldn't have the THREAD table. Just that it should *ONLY* contain information about the thread, per se, and nothing about the posts in the thread...including the first post.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #11
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    Go back and carefully read what I said in post #2.
    So you want to say the thread post should have the following 2 fields only:

    thread_id and thread_title

    and the rest part of can be added to the comments table?

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    It could be as few as those two fields.

    I wouldn't worry about adding thread_start_date. It's not strictly necessary, as it can be derived from the associated comments, but it could be handy to have. You could also have thr_user_id, so that it's easy to get "find all threads started by xxx" as this forum has, for example. Again, it could be derived from the comments table but it might be nice to have it for efficiency of some later queries.

    *IF* you used a TRIGGER, then I might even support thread_last_updated. But not if you don't use a TRIGGER.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #13
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    You could also have thr_user_id, so that it's easy to get "find all threads started by xxx" as this forum has, for example.
    Would you do that if you were designing the application? (just taking an idea)

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Think about the kinds of queries you will later need to make. That's what should drive the design, esp. what fields are needed in the threads table.

    For example, without a thr_user_id, to find who started a thread you have to do
    Code:
    SELECT userid, postdate FROM comments WHERE userid = xxx AND threadid = yyy ORDER BY postdate LIMIT 1
    and that's bad enough for getting one userid, but how do you find "all thread started by use xxx"?

    Something as ugly as this:
    Code:
    SELECT COUNT(*)
    FROM comments AS C,
         ( SELECT threadid, MIN(postdate) AS mindate
           FROM comments
           GROUP BY threadid ) AS M
    WHERE C.postdate = M.mindate
    AND C.userid = xxx
    Okay, that's not *real* ugly, but it's a lot slower than simply
    Code:
    SELECT COUNT(*) FROM threads WHERE userid = xxx
    So... plan ahead, that's the best advice I can give.

    Simplify, yes, but if you see that adding in some easy to add data item will help later queries a lot...well, it's all about balance.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Mind you, if you never expect to have a query such as "Find all threads started by user xxx" then the need for userid in the threads table goes way down.

    So... As with most programming questions, the real answer is: "It depends ... "
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •