...

View Full Version : SQL SELECT for a Blog



Kurashu
09-17-2004, 04:46 AM
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:


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
09-17-2004, 05:16 PM
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
09-17-2004, 11:18 PM
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
09-17-2004, 11:27 PM
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
09-18-2004, 12:01 AM
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
09-18-2004, 08:07 AM
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
09-21-2004, 05:10 AM
Thanks, and I'll try to avoid *'s in generic table joins.

raf
09-21-2004, 08:58 AM
You're welcome :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum