MySQL Simple Forum

Question

So I'm trying to build a simple forum. It'll be a list of topics in descending order by the date of either the topic (if no replies) or latest reply. Here's the DB structure:

forum_topic

id, name, email, body, date

forum_reply

id, email, body, date, topic_id

The forum itself will consist of an HTML table with the following headers:

Topic, Last Modified, # Replies

What would the query or queries look like to produce such a structure? I was thinking it would involve a cross join, but not sure... Thanks in advance.

Answer 1

Somewhat like this:

select * from forum_topic
inner join forum_reply on forum_topic.id=topc_id

However, don't use select *

That's bad practice :)

And I don't like the way you avoid normalization! Meaning I would rather have:

Users

• UserID
• Name
• Email

Threads

• ThreadID
• Subject
• Answered
• AskedByUserID
• Date

Replies

• ReplyID
• ThreadID
• UserID
• Answer
• Date

Then selecting a Thread like this:

select ThreadID, Subject, Answered, AksedByUserID, Date from Threads

And selecting all replies like this

select Answer, Date, Name, Email from Threads
inner join Replies on Threads,ThreaID=Replies.ThreadID
inner join Users on AskedByUserID=UserID 
where Threads.ThreadID=xxx

Now this was just written from the top of my head, but you might need to add some group by as well.

Answer 2

Yes, you should be able to get it with a query like this:

SELECT 
  forum_topic.id, 
  forum_topic.name AS Topic,  
  MAX(forum_reply.date) AS Last_Modified, 
  count(*) AS  Replies
FROM forum_topic 
INNER JOIN forum_reply ON (forum_topic.id=forum_reply.topic_id)
GROUP BY forum_topic.id

The "group by" is the magic that gives us one row per topic, with the MAX() and COUNT() functions giving us the aggregated data you need.

(EDIT: I missed that the body of the first post was in the topic table, so posts with no replies would get missed by the above query. Filip has the right idea suggesting you normalize your data. Once normalized, a query similar the above would get you the data you need).

Answer 3

By "normalized", you mean that the body column of "forum_topic" should be removed, and the actual topic body should be the first reply?

Answer 4

First off, it seems to me noboody is actually answering your question, which was:

What would the query or queries look like to produce such a structure?

with a requested structure of

Topic, LastModified, # Replies.

The SQL to produce a result table with that structure, given the table structures you provided, would be:

SELECT t.Id, t.Name AS Topic, 
       MAX(r.Date) AS LastModified, 
       COUNT(*) AS NumReplies
FROM Forum_Topic t
LEFT OUTER JOIN Forum_Reply r ON t.id = r.topic_id
GROUP BY t.Id, t.Name

(sorry, this is tested only on SQL Server, as I don't have access to MySql at the moment)

Also, your structure IS already normalized. Suggestions to the contrary are making assumptions about what you want to do, e.g., assuming that you are interested in tracking user names in addition to email addresses. This is quite reasonable, but is nevertheless an assumption. There is nothing wrong, from a normalization perspective, with using email address as a unique user identifier.

Now, if you are looking for general suggestions on how to set up a database, we can give you LOTS of those. Before normalization, I would start with not using potential keywords as object names (e.g., don't give columns names like 'Name' and 'Date').

Regarding the comment from Matt about the value being NULL when there are no replies: using the COALESCE() function will fix that. COALESCE() returns the first non-NULL argument (or NULL if all arguments are NULL). So replace the MAX(r.Date) with MAX(COALESCE(r.Date, t.Date)).