SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My question is simple:

    Why does this work:
    mysql_query("SELECT COUNT(*) FROM stories", $con);

    When this doesn't?
    mysql_query("SELECT COUNT(DISTINCT column) FROM table", $con);

    (The last one is, of course, what I want to do)

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    PHP Code:
    mysql_query("SELECT DISTINCT COUNT(column) FROM table"$con); 
    I think that should work...I mean, I hope that will work.
    Last edited by TWTCommish; Feb 20, 2001 at 16:31.

  3. #3
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Following onto this thread, maybe a little off topic but is it possible to count the number of rows that a certain field has

    E.G, I have a joined query, which there are different amounts of records, for different things. So i was wanting to do this:

    PHP Code:
    mysql_query("SELECT COUNT(subid), text, name, author FROM table1, table2"); 
    Except it doesn't work. Is there a way i can do this?

    Peter

  4. #4
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pete: your query should be like this:

    PHP Code:
    $result mysql_query("SELECT*COUNT(subid) AS thecounter,*text,*name,*author*FROM*table1,*table2");
    $thecounter $result["thecounter"]; 

  5. #5
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx for that!

    Works...

    Peter

  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    That still shouldn't work. As MySQL will tell you, you can't mix grouping functions with non-grouped columns without using a GROUP BY clause. This is the error you'll get:

    ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

    The problem is that COUNT(subid) returns a single value, while text, name, and author returns a value for each combination of rows from the two tables. MySQL can't return a single row in one column and multiple rows in another column. It just won't work!

    Rethink what you're trying to do and see if you can come up with a more sensible way of getting the information you're after. We'll be happy to help!
    Last edited by Kevin Yank; Feb 20, 2001 at 16:37.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To which block of code are you referring to Kevin? The first?

  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I'm referring to the very last query:

    PHP Code:
    $result mysql_query("SELECT COUNT(subid) AS thecounter, text, name, author FROM table1, table2"); 
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  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)
    for count distinct it was discussed before - look here:

    http://www.sitepointforums.com/showt...count+distinct
    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
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, my mistake. I think Pete jumped the gun then, because he said it worked.

  11. #11
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that surprised me.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  12. #12
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yup, got it working... It's true that you can't put it all in one query though - I had to do it like this:

    $result = mysql_query("SELECT distinct ip from tablename");
    $count = mysql_num_rows($result);

  13. #13
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Opps....

    I have a variable $i and i echoed that instead of $c which is what i was using for count.

    Nothing works if i try to do it, just echos $i which happened to be 1

    Pete


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
  •