[thelist] MySQL COUNTing
// kerin | airside.co.uk
kerin at airside.co.uk
Fri Sep 21 10:48:23 CDT 2001
Hi all,
I'm working on putting together a basic bulletin board for a project.
i want the index page to have a simple table showing active
discussions, like so:
thread title | date | no. of posts
in my MySQL database, there are two tables (amongst others) like so:
THREADS:
________
ID
DATE
USER_ID
TITLE
MESSAGES:
_________
ID
THREAD_ID
USER_ID
BODY
DATE
now, my SQL isnt that hot, and Im having real trouble writing the SQL
query to do this. What I want is to have one SQl query which returns
the title and date of the thread, as well as the total number of
posts, by joining the THREADS and MESSAGES tables on the THREAD_ID
column.
my query currently looks like this, and fails miserably:
$sql = "SELECT
threads.title AS 'title',
threads.date AS 'date',
COUNT(messages.thread_id) AS 'noposts'
FROM threads, messages
WHERE messages.thread_id = threads.id
ORDER BY date DESC
LIMIT (20)";
Can somebody point me in the right direction here? My SQL skills are
obviously pretty poor....
Thanks,
Kerin
--
More information about the thelist
mailing list