Member Avatar for Taffd

I've created my own forum and would like to display
username - from users table
title - form topics table and
a count of comments - from replies table

I 've got the first bit sorted ok

SELECT username, title
FROM users, topics
WHERE users.userid = topics.userid
ORDER BY RAND()

I now want to add a count of the comments associated with each topic, something of the order of

SELECT COUNT(replies.comment) AS 'count'
WHERE topics.topicid = replies.topicID
GROUP BY topics.title

I would have assumed I'd do a LEFT JOIN ON topics.topicid = replies.topicID, but this doesn't seem to work. I've tried various combinations of the query but I can't get it to function. Any ideas.

Regards
Taffd

  • 2 Contributors
  • 4 Replies
  • 89 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

Nice job, — the general approach you used is the right pattern: keep topics in the result even when there are no replies, and aggregate the reply rows to produce a per-topic count. That preserves topics with zero comments while still returning accurate totals for those that do have replies. 's question about the random ordering is also sensible: RAND() randomizes the result set but can be costly on larger tables.

Two practical caveats for anyone adapting this today. Modern MySQL servers often enable ONLY_FULL_GROUP_BY by default, which requires every nonaggregated column in SELECT to appear in GROUP BY (or be wrapped in an aggregate). If you see the "not in GROUP BY" error, either explicitly GROUP BY the correct unique key(s) or adjust your SQL mode with care; the manual documents the behavior and options MySQL GROUP BY handling and ONLY_FULL_GROUP_BY mode.

A few quick tips: index the reply->topic foreign key to keep GROUP BY fast; prefer counting a reply-table column (non-null) rather than COUNT(*) when using an outer join so topics with zero replies show zero; and avoid ORDER BY RAND() on large tables — it forces a full sort and can be very slow, so use sampling or id-based selection for scalable random picks.

Member Avatar for Taffd

It's ok folks, I've just cracked it.
Here's the solution

SELECT username, title, COUNT(replies.comment) AS 'count'
FROM users, topics
LEFT JOIN replies
ON topics.topicid = replies.topicID
WHERE users.userid = topics.userid
GROUP BY topics.title
ORDER BY RAND()

Member Avatar for nav33n

It's ok folks, I've just cracked it.
Here's the solution

SELECT username, title, COUNT(replies.comment) AS 'count'
FROM users, topics
LEFT JOIN replies
ON topics.topicid = replies.topicID
WHERE users.userid = topics.userid
GROUP BY topics.title
ORDER BY RAND()

Order by rand() ? Thats something new that I am seeing. What does it order by (which column) ?

Member Avatar for Taffd

All it does is outputs the results in a random order.
An opening page on one of my forums will show a random 30 topics. People can get a glimpse of what's available and also see various categories listed.

Member Avatar for nav33n

ah! i see.. thanks :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.