SQL problem

[noddy]

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

I'm currently using.

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

  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

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

[bigduke]

The problem seems to be the group by clause.

[noddy]

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)

[r937]

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

[noddy]

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

[r937]

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

[noddy]

Thank you that makes that fog clear