SQL SELECT for a Blog

[Kurashu]

I have a bit of trouble on my hands. I am constructing a blog using PHP and PostGreSQL. I need to be able to call out the articles with the usernames and the number of comments for each article. Now, the articles and username I can do no problem, but the number of comments is making me mad (not really, but I can't think of a better word).

Right now, my SQL looks like:

PHP Code:

SELECT greenblogger_users.username, 
           greenblogger_articles.*,
           count(greenblogger_comments.cid) 
WHERE greenblogger_articles.uid = greenblogger_users.uid 
AND greenblogger_articles.nid = greenblogger_comments.nid; 


The Error I get is: ERROR: Attribute greenblogger_users.username must be GROUPed or used in an aggregate function.

I've tried grouping it, but it doesn't work.

[Roy Sinclair]

When you use an aggregate function like count or sum then you also need to add a GROUP BY clause which lists ALL table elements that aren't included in the count or sum aggregate function.

[Kurashu]

SELECT greenblogger_users.username,  
           greenblogger_articles.*, 
           count(greenblogger_comments.cid)  
WHERE greenblogger_articles.uid = greenblogger_users.uid  
AND greenblogger_articles.nid = greenblogger_comments.nid
GROUP BY greenblogger_users.username,
           greenblogger_articles.uid,
           greenblogger_articles.nid, 
           greenblogger_articles.name,
           greenblogger_articles.date,
           greenblogger_articles.article;

Now it produces nothing. o.o What am I doing wrong?

[Roy Sinclair]

It doesn't even give you an error of some sort? Just an empty dataset?

Where is the FROM and how is the JOIN between the tables declared?

[Kurashu]

There aren't any. There don't need to be (to my knowledge)

SELECT greenblogger_articles.*, greenblogger_users.*;

Means select everything from greenblogger_articles and greenblogger_users.

Edit: >_< I keep forgetting that tab won't tab the data but it select the next form item.

[raf]

Originally Posted by Roy Sinclair

Where is the FROM and how is the JOIN between the tables declared?

it's a generic join, defined by the where-clause.

Kurashu,

never use table.* in embedded sql !! always specify the fieldlist.
try
SELECT greenblogger_users.username,
greenblogger_articles.uid,
greenblogger_articles.nid,
greenblogger_articles.name,
greenblogger_articles.date,
greenblogger_articles.article;
count(*) as numrec
FROM (greenblogger_articles INNER JOIN greenblogger_users ON greenblogger_articles.uid = greenblogger_users.uid)
INNER JOIN greenblogger_comments ON greenblogger_articles.nid = greenblogger_comments.nid
GROUP BY greenblogger_users.username,
greenblogger_articles.uid,
greenblogger_articles.nid,
greenblogger_articles.name,
greenblogger_articles.date,
greenblogger_articles.article;

you can then get the number for each group with
$row['numrec']

[Kurashu]

Thanks, and I'll try to avoid *'s in generic table joins.

[raf]

You're welcome