Simple MySQL Forum
So I am trying to create a simple forum. This will be a list of topics in descending order by the date of either topics (if no replies) or last 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 headings:
Topic, last change, # Answers
What would the query or queries look like to create such a structure? I thought it would be cross related, but not sure ... Thanks in advance.
source to share
First, it seems to me that noboody actually answers your question, which was:
What does the query or queries look like to create such a structure?
with requested structure
Subject, LastModified, # Reply.
The SQL to create a results table with this 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 only tested on SQL Server as I don't have access to MySql at the moment)
Also, your IS structure is already normalized. Suggestions to the contrary make assumptions about what you want to do, such as assuming that you are interested in tracking usernames in addition to email addresses. This is quite reasonable, but, nevertheless, an assumption. There is nothing wrong, in terms of normalization, using an email address as a unique user ID.
Now if you are looking for general suggestions for database setup, we can provide you with a lot. Prior to normalization, I would start by not using potential keywords as object names (for example, omitting column names such as "Name" and "Date").
Regarding Matt's comment about NULL when there are no answers: using the COALESCE () function will fix this. COALESCE () returns the first non-NULL argument (or NULL if all arguments are NULL). So replace MAX (r.Date) with MAX (COALESCE (r.Date, t.Date)).
source to share
Somewhat:
select * from forum_topic
inner join forum_reply on forum_topic.id=topc_id
However, don't use select *
Bad practice :)
And I don't like the way you avoid normalization! I would prefer:
Users
- User ID
- Name
Topics
- ThreadID
- Topic
- Answered
- AskedByUserID
- date
Answers
- ReplyID
- ThreadID
- User ID
- Answer
- date
Then select Thread like so:
select ThreadID, Subject, Answered, AksedByUserID, Date from Threads
And choosing all answers 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 written just from the head, but you may need to add a group as well.
source to share
Yes, you should get it with a request 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
"group by" is the magic that gives us one row for each topic, with MAX () and COUNT () giving us the aggregated data you need.
(EDIT: I missed that the body of the first post was in the topic table, so the unanswered posts would be dropped by the above query. Philip has the correct idea suggesting you normalize your data. After normalizing the query similar to the one above, you will get the data you want).
source to share