Re: Listing threads by newest posts?

[Rick James]

You can't have more than 127 users?
`user_id` tinyint(4) NOT NULL,
TINYINT SIGNED has a range of of values: -128..127

Strange type for a "date":
`date` varchar(10) NOT NULL,

"not what I want for a forum" -- well, what do you want?

SELECT forum_id,
       count(*) as ct,
       count(DISTINCT user_id) as distinct_users
  FROM forum_threads
  GROUP BY forum_id;

Will list the forums and how many threads in each, plus how many different people have commented in that forum.

SELECT  *
  FROM forum_threads
  ORDER BY `date` DESC
  LIMIT 10;

will list the 10 latest entries.

SELECT * FROM
( SELECT *
    FROM forum_threads
    ORDER BY `Date` DESC ) x
  GROUP BY forum_id;

will list the latest entry for each forum. Is that what you were trying to say in the Subject?

In the long run, you really need separate tables for
* forum
* thread
* message
* (optionally) user