SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL behaving badly...

    Code:
    SELECT comments.cid, 
           comments.email, 
           comments.name,  
           DATE_FORMAT(comments.date, '%M %D, %Y') as fdate,
           comments.subject, 
           comments.text,
           COUNT(com_replies.rid) AS cou, 
           AVG(com_votes.vote) as avv 
    FROM comments 
           LEFT JOIN com_replies on com_replies.cid = comments.cid 
           LEFT JOIN com_votes on com_votes.cid = comments.cid 
    WHERE comments.id = '$id' and 
          comments.status = '1' 
    GROUP BY comments.cid 
    ORDER by avv desc, comments.date 
    LIMIT 20
    Right.

    So this SQL selects from 3 tables. A table of commments, a table of replies to comments, and a table of comment votes.

    What I'm doing is building a list of comments along with the rating each comment has and a number of replies for the comment. Its very much like a forum.

    Whats not working is the number of replies. If there is no replies it lists the number 0 correctly. If there is a reply it lists a random number.

    To see this in action check out this page: http://www.online-literature.com/orwell/1984/

    Anything with a reply gets a random number of replies listed.

    Each comment at most has like 1 reply. I looked in the database, the random number is neither the ID of the reply or the number of the reply (in order).

    So... I have no idea where the number is coming from or how it's getting it.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    location location
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just is just a stab in the dark but the only thing I can think is that one of your left joins is causing the problem. I would put decent money on the fact that field 'com_replies.rid' was blank or the same (probably all the same) for the records it's bringing back. You could prove this by removing the count, avg and group by and see what it brings back then.

    I don't know what the DB is, looks like MySQL or Access, I'm more of an Oracle person but if the problem is I think, then the fix on oracle would be

    COUNT(DISTINCT(com_replies.rid))

    This may or may not work in MySQL.

    Hope this helps or at least points you in the right direction.

  3. #3
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is MySQL and I know for a fact that you can't do a count distinct like that in MySQL.

    To get distinct counts you need to do a count and a group by, which is what I'm doing.

    I can remove the extra parts of the query and make a prototype one that does the count and a group by and it works perfectly.

    Its only when I put it with the rest of the query that this happens.

    com_replies.rid is not going to be blank or identical. Its the primary key of the com_replies table so each one is going to be defined and different.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    location location
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looking at this further, is the number returned the number of votes placed ? I say this because I would say that the count will return :-

    1 (the comments table will always be unique in this select) * number of comments * number of votes.

    You say the number of comments is always one, so I reckon the count it shows will be the number of votes for that comment. If the number of comments was more than one, that would also be added in.

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was thinking that perhaps that was the case. I have yet to confirm it though.

    Assuming that is the problem do you have a solution?
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    location location
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just had a quick look about mySQL and COUNT(distinct and got the following from the MySQL site

    Stanislav Yordanov:
    Counting rows from different tables in one query:

    SELECT COUNT(DISTINCT table1.ID) AS table1, COUNT(DISTINCT table2.ID) AS table2, COUNT(DISTINCT table3.ID) AS table3 FROM table1, table2, table3

    DISTINCT in COUNT() is supported in version 3.23.2 and upper.

  7. #7
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well thats good to know.. the last time I had to try this it wasn't supported and I do have a newer version of MySQL now... I'll give it a try.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  8. #8
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That doesn't fix it... logically I don't think it would because you're not counting the number of distinct values, rather you just want the total number of values.

    Though I have confirmed that the number is being created by replies * votes.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  9. #9
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried changing the type of join I did to a natural join and it had the same result...
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  10. #10
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ha. I get whats happening now.

    When doing a left join it makes additional rows when you have a many to many relationship.

    So for the joins to work since there are like 25 votes and 1 reply it must make 25 rows. Thus in my count there were 25 replies.

    Using distinct was the right solution - however replacing the group by clause with the distinct didn't work - the correct answer was to add the distinct qualifier to the select statement and leave on the group by clause (since I still needed to group the results of the aggregate function).
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums


Bookmarks

Posting Permissions

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