SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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.
    Why are you stuck, what language is the forum software written in?

    Steve
    ictus==""

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Why are you stuck, what language is the forum software written in?

    Steve
    Hi, Its in PHP.

    Quote Originally Posted by r937 View Post
    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
    there must be someway, how do the forums like phpbb or simplemachine do it?

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    @cancer10

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

    Code:
    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 strpos($mystring, $findme); 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
    ictus==""

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    there must be someway, how do the forums like phpbb or simplemachine do it?
    i'm not sure, but i'll bet you a coffee and doughnut it isn't "whichever date is greater among the above 4"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    @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?

  8. #8
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by cancer10 View Post
    @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? ...
    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
    ictus==""

  9. #9
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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:

    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

  10. #10
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    @cancer10

    Have you tried doing a Subquery to get the number of comments:
    Code:
    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
    ictus==""

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    @cancer10

    Have you tried doing a Subquery to get the number of comments:
    Code:
    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
    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?


    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

    Many thanks

  13. #13
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    @cancer10

    Hi

    While your query:
    Code:
    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).
    1 Welcome to the forum Sue Jasper 2
    2 problem with Div Sue Jasper 3
    3 My Second Post Yuri Yankavic 0
    4 My First Post Yuri Yankavic 5



    While doing the query this way produces the correct results.:
    Code:
    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
    title last_posters_full_name comments_per_thread
    Welcome to the forum Sue Jasper 1
    problem with Div Sue Jasper 2
    My Second Post Yuri Yankavic 0
    My First Post Yuri Yankavic 3

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

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

    Best,
    Yuri
    6 4 3 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


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

    Regards,
    Steve
    ictus==""


Bookmarks

Posting Permissions

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