[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