Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts

    SQL SELECT for a Blog

    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.
    Last edited by Kurashu; 09-17-2004 at 04:51 AM.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by Roy Sinclair; 09-17-2004 at 05:19 PM.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    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?

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #5
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    There aren't any. There don't need to be (to my knowledge)

    Code:
    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.
    Last edited by Kurashu; 09-18-2004 at 12:05 AM.

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote 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']
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, and I'll try to avoid *'s in generic table joins.

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're welcome
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •