Ok heres where I am in my quest to create a mySQL forum (which is able to display topic counts, and last post details in a list of topics)
my message table looks like this (simplifed for clarity):
message_id, parent_id, forum_id, author, date, subject, body
my goal is to create a flat thread system (similar to yaB😎 where all posts for a topic are displayed on 1 page and all topics for a particular forum will show in a list along with post counts and last post details.
I want to if at all possible avoid using mySQL queries in a loop. That means having a row set that will include the counts and last post details.
I've been struggling with this for awhile, and mySQL doesnt seem to be working with me.
I've come to a point where I realize I need to use a temporary table, but the question is how?
I've been discouraged once from using the group by staement. (in my previous insane attempt to do it all in 1 query), but I can't think of any other way to retrieve the last post details.
SELECT parent_id, COUNT(message_id)-1 as replies, MAX(message) as last_message_id
FROM message
GROUP BY parent;
will generate this:
parent _id | replies | last_message_id
-----------+-----------+----------------
1 | 3 | 7
4 | 2 | 6
8 | 0 | 8
which is all correct.
I can also execute a simple SELECT statement to retrieve a list of parent posts (WHERE parent_id=message_id) with author and subject fields.
Now how to combine these using a temp table??
first I make the table:
CREATE TABLE message_temp
parent_id int(11),
replies int(11),
last_message_id int(11),
and populate it with what I know:
INSERT into message_temp
SELECT parent, COUNT(message)-1 as replies, MAX(message) as last_message_id
FROM message
GROUP BY parent;
Now I can join this table to my message table in a select statement to retrieve a list of topics, authors, dates AND a last_message_id.
but I also want to select the username and date corresponding to that last_message_id without doing a select in my main loop.
Any ideas?
