SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: SQL problem

  1. #1
    Fully Sweet Car noddy's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL problem

    who do I get this SQL query retrive the most recent post date from the category.

    I'm currently using.
    Code:
    SELECT guestbook_cats.*, count( guestbook.cat_id ) AS post_count, guestbook.guest_date AS last_post_date FROM guestbook_cats LEFT JOIN guestbook ON guestbook.cat_id = guestbook_cats.cat_id WHERE guestbook_cats.cat_visible=1 GROUP BY guestbook_cats.cat_id ORDER BY `guestbook_cats`.`cat_name` ASC, guestbook.guest_date DESC
    I want to show the user if there are any new posts in categories since their last visit.

    here the php

    PHP Code:
      while ($row mysql_fetch_array($res)) {
        
    $buf .= "<tr>\n";
        
    $buf .= "  <td><a href=\"goto/view_messages/catid/$row[cat_id]\">$row[cat_name]</a><br>$row[cat_desc]</td>\n";
        
    $buf .= "  <td>$row[post_count]</td>\n";

        
    $user_lastlogin strftime("$info[short_date_time]",$_SESSION['user_lastlogin']); // debug
        
    $last_post_date strftime("$info[short_date_time]",$row['last_post_date']); // debug

        
    if ($row['last_post_date'] > $_SESSION['user_lastlogin']) {
          
    $new "new<br>\n";
          
    $new .= "last post date: $last_post_date and last login date $user_lastlogin";
        } else {
          
    $new "no new<br>\n";
          
    $new .= "last post date: $last_post_date and last login date $user_lastlogin";
        }

        
    $buf .= "  <td>$new</td>\n";
        
    $buf .= "</tr>";
      } 

    my current out put is
    Code:
    Category	Posts	
    Buggy talk
    General chit chat about your buggies	432	no new
    last post date: 9-03-2003 07:42 PM and last login date 4-07-2004 09:48 AM	
    For Sale
    Place the things your trying to sell in here (RC related only please)	6	no new
    last post date: 22-02-2004 01:56 PM and last login date 4-07-2004 09:48 AM	
    General Chat
    General RC chit chat	71	no new
    last post date: 5-05-2003 06:55 AM and last login date 4-07-2004 09:48 AM	
    Other
    Other topics post under here	1	no new
    last post date: 30-06-2004 11:44 PM and last login date 4-07-2004 09:48 AM	
    Race Meeting Chatter
    Discuss up comming races here	118	no new
    last post date: 16-03-2003 10:19 PM and last login date 4-07-2004 09:48 AM	
    Race Meeting Results
    Race results are displayed here for now	12	no new
    last post date: 9-02-2004 11:45 PM and last login date 4-07-2004 09:48 AM	
    RRC Club Talk
    This area is for topics regarding the club	7	no new
    last post date: 19-01-2004 07:50 AM and last login date 4-07-2004 09:48 AM	
    Wanted
    RC equipment you are after	3	no new
    last post date: 6-06-2003 04:10 PM and last login date 4-07-2004 09:48 AM	
    Website Stuff
    Stuff to do with the website goes here	102	no new
    last post date: 9-04-2003 04:06 PM and last login date 4-07-2004 09:48 AM
    thanks for any help



    P.S I also need a catch if the user isnt logged in but thats just formatting its the SQL query I'm having trouble with because I want to do this all in 1 query not 2, or a query for each category to find the last post date it could be to server intensive and I'm sure it can be done in 1

  2. #2
    get into it! bigduke's Avatar
    Join Date
    May 2004
    Location
    Australia
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem seems to be the group by clause.

  3. #3
    Fully Sweet Car noddy's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bigduke
    The problem seems to be the group by clause.
    the group by is merging together all the posts from the different categories in order to produce the count(guestbook.cat_id)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select guestbook_cats.cat_id
         , guestbook_cats.cat_name
         , guestbook_cats.cat_foo
         , guestbook_cats.cat_bar
         , count( guestbook.cat_id )   as post_count
         , max( guestbook.guest_date ) as last_post_date 
      from guestbook_cats 
    left outer
      join guestbook 
        on guestbook_cats.cat_id 
         = guestbook.cat_id 
     where guestbook_cats.cat_visible=1 
     group 
        by guestbook_cats.cat_id
         , guestbook_cats.cat_name
         , guestbook_cats.cat_foo
         , guestbook_cats.cat_bar
    order 
        by guestbook_cats.cat_name asc
         , last_post_date DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Fully Sweet Car noddy's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers that worked great.

    can you explain what the outer join is/does and also what max is doing in this instance?

    thanks for your help so far

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a left outer join returns all rows from the left table, and any matching rows from the right

    an inner join, by comparison, gets only matching rows

    max() is an aggregate function which obtains the largest value in a group

    count() is an aggregate function which obtains the number of values in a group
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Fully Sweet Car noddy's Avatar
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    759
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you that makes that fog clear


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
  •