mySQL woes

Anon

I need some help guys.

Heres the problem. I am currently writing a message board in PHP with mySQL support. I want to make things a little cleanr in my database tables so I accomplish this in 2 tables:

forum: id, description
message: id, parent, forum_id, date, username, subject, body

The theory is that any message with a parent of itself (or 0) is a new topic; and any message with a parent of another message, is a sub-post.. (ovbiously)

my problem lies in the script to display all the threads in a particular forum (similar to yaB😎.

First I do a SELECT staement to determine the number of threads in the forum:

SELECT COUNT(*) AS count FROM message WHERE parent='0' ;

this allows me to calculate the number of pages required (for my LIMIT arguments in my main SELECT statement)

Now heres the big one.

I want to have a select statement where all of the rows with no parent message are selected, along with the latest poster and post date in that thread, and also a count of replies.

Very hard. This is what I have so far:

SELECT topic.*, MAX(post.date) as last_post_date, post.username as last_poster, COUNT(post.parent) as count
FROM message as topic, message as post
WHERE post.parent=topic.message
GROUP BY parent
ORDER BY last_post_date DESC
LIMIT $start_topic, $results_per_page;

so to explain my thinking: I dont think this can be accomplished without the JOIN, am I right? SO I join the table to itself. I also get the count of each topic (by using GROUP) and the MAX() of the date (to get the latest post date) for that result. And these both work fine... although the MAX date is a little more kludgy than I wanted. I don't know how to get the lastest poster tho.

Any Ideas? Can this all be done with the one table? Or do I need to go the way of everyone else and make a seperate topic and post table?
plz help!

Anon

I may be misunderstanding your problem, but couldn't you add another field to your message table:

head - stores the message id for the head post (parent=0) in a thread.

That should simplify your query, with no need for a join.

-K

[deleted]

"Or do I need to go the way of everyone else and make a seperate topic and post table?"

How would that help? It doesn't matter wether you join one table to itself or two tables to eachother...

First of all, your current query is very dangerous. When you use a 'group by', you may only select aggregates and fields that are mentioned in the group by, So topic.* is very invalid. MySQL does allow it, but that's because MySQL is a piece of crap.
There could be 100 records that have the same value for 'parent', so how can mysql know which of the records to display? It can't , but mysql doesn't care, it just gives you the first record it find. This can change every time you run this query.

"I want to have a select statement where all of the rows with no parent message are selected, along with the latest poster and post date in that thread"

Either build a very complex query, or use a temporary table.

First create the temporary table accoring to whatever you want to select (postid, lastpostdate, lastpostername, etc)
then run one query to insert all the 'first post' data for all threads.
Then run another query to update the temporary table to add the lastpostdate and lastposter data for all threads that appear in the temporary table. (using a join betwee the temporary table and the posts table)

Then finally select * from the temporary table and destroy the temporary table.

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

use a temporary table.

Thanks for the help. 1 final question. Will making a temp table pose problems if 2 people run the script at the same time? Will mySQL create and drop table before another user can call the script to create the temp table? Or should I just make the tablename with a unique id?