SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29

Thread: PHP SQL count

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP SQL count

    I cant figure out how to do this

    Code PHP:
    $sql = 'SELECT COUNT(canada) as num_canada from phpbb_buysell WHERE canada = "canada",
    COUNT(canada) as num_usa from phpbb_buysell WHERE canada = "usa"';


    Thanks,


  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select canada,count(*)
    from phpbb_buysell
    where canada in ('canada', 'usa')
    group by canada
    this should give you 1 row for canada, and one for usa with the number of rows grouped for each country.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by iansteeze View Post
    WHERE canada = "usa"
    wtf?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    Code:
    select canada,count(*)
    from phpbb_buysell
    where canada in ('canada', 'usa')
    group by canada
    this should give you 1 row for canada, and one for usa with the number of rows grouped for each country.
    Im not sure if that is what i want or maybe im just not doing it rite?

    what i am trying to do is count the number of ads that are posted for Canada and the number posted for USA so my page will have

    Canada ( 10 )
    USA ( 20 )

    Quote Originally Posted by r937 View Post
    wtf?
    haha i know im not sure why i didn't use "location"

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im not sure if that is what i want or maybe im just not doing it rite?

    what i am trying to do is count the number of ads that are posted for Canada and the number posted for USA so my page will have

    Canada ( 10 )
    USA ( 20 )
    And that query will give you exactly that, but on 2 rows as there are 2 countries.
    You could aggregate them by doing a subquery if you want to, but it would not be necessary.

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    And that query will give you exactly that, but on 2 rows as there are 2 countries.
    You could aggregate them by doing a subquery if you want to, but it would not be necessary.
    how would i put it into variables so i could display them?

    this is what i have but it doesn't
    Code PHP:
    $sql = 'select canada,count(*)
    		from phpbb_buysell
    			where canada in ('canada', 'usa')
    				group by canada';
     
    $result = $db->sql_query($sql);
    $row = $db->sql_fetchrow($result);
     
    $template->assign_vars(array(
        'NUM_CANADA'		=> $row['num_canada'],
    	'NUM_USA'		=> $row['num_usa'],
    ));
    work

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    $sql 
      
    "SELECT 
          SUM(CASE
                WHEN canada = 'canada' THEN 1 ELSE 0
              END) AS num_canada
        , SUM(CASE
                WHEN canada = 'usa' THEN 1 ELSE 0
              END) AS num_canada
       FROM phpbb_buysell
       WHERE canada IN ('canada', 'usa')"


  8. #8
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    PHP Code:
    $sql 
      
    "SELECT 
          SUM(CASE
                WHEN canada = 'canada' THEN 1 ELSE 0
              END) AS num_canada
        , SUM(CASE
                WHEN canada = 'usa' THEN 1 ELSE 0
              END) AS num_canada
       FROM phpbb_buysell
       WHERE canada IN ('canada', 'usa')"

    That is giving me

    Canada ( 1 ) (correct)
    USA ( 0 ) (should be 2)

  9. #9
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql 'select canada as location,count(*) as cnt
            from phpbb_buysell
                where canada in (\'canada\', \'usa\')
                    group by canada'
    ;
     
    $result $db->sql_query($sql);
    foreach(
    $row=mysql_fetch_object($result)){  //we iterate on every rows returned by the query
      
    $var="num_";
      $
    $var.$row->location=$row->cnt// this should do the trick to create $num_usa and $num_canada
    }
     
    $template->assign_vars(array(
        
    'NUM_CANADA'        => $num_canada,
        
    'NUM_USA'      => $num_usa,
    )); 
    Not tested, and my php is 2 years old, but this should do the trick

  10. #10
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    PHP Code:
    $sql 'select canada as location,count(*) as cnt
            from phpbb_buysell
                where canada in (\'canada\', \'usa\')
                    group by canada'
    ;
     
    $result $db->sql_query($sql);
    foreach(
    $row=mysql_fetch_object($result)){  //we iterate on every rows returned by the query
      
    $var="num_";
      $
    $var.$row->location=$row->cnt// this should do the trick to create $num_usa and $num_canada
    }
     
    $template->assign_vars(array(
        
    'NUM_CANADA'        => $num_canada,
        
    'NUM_USA'      => $num_usa,
    )); 
    Not tested, and my php is 2 years old, but this should do the trick
    I am getting this error

    Code:
    Parse error: syntax error, unexpected ')' in /homepages/7/d256403619/htdocs/forum/buysell/buysell.php on line 395
    that is this row

    Code:
    foreach($row=mysql_fetch_object($result)){
    I dont see what the problem with that is

  11. #11
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stupid me...
    It's not a foreach loop, but a while loop that should be there.
    PHP Code:
    $sql 'select canada as location,count(*) as cnt
            from phpbb_buysell
                where canada in (\'canada\', \'usa\')
                    group by canada'
    ;
     
    $result $db->sql_query($sql);
    while(
    $row=mysql_fetch_object($result)){  //we iterate on every rows returned by the query
      
    $var="num_";
      $
    $var.$row->location=$row->cnt// this should do the trick to create $num_usa and $num_canada
    }
     
    $template->assign_vars(array(
        
    'NUM_CANADA'        => $num_canada,
        
    'NUM_USA'      => $num_usa,
    )); 

  12. #12
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    Stupid me...
    It's not a foreach loop, but a while loop that should be there.
    PHP Code:
    $sql 'select canada as location,count(*) as cnt
            from phpbb_buysell
                where canada in (\'canada\', \'usa\')
                    group by canada'
    ;
     
    $result $db->sql_query($sql);
    while(
    $row=mysql_fetch_object($result)){  //we iterate on every rows returned by the query
      
    $var="num_";
      $
    $var.$row->location=$row->cnt// this should do the trick to create $num_usa and $num_canada
    }
     
    $template->assign_vars(array(
        
    'NUM_CANADA'        => $num_canada,
        
    'NUM_USA'      => $num_usa,
    )); 
    I tried that also but it just show Canada ( ) Usa () with no numbers in them. Maybe it the template vars? They are used in an external file like {NUM_CANADA}

    How would i do the sub queries?

  13. #13
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe it the template vars?
    What are the results if you run this query directly into mysql?

    How would i do the sub queries?
    Code:
    select canada.cnt as canada_cnt, usa.cnt as usa_cnt
    from (
      select count(*) as cnt, id=1
      from phpbb_buysell
      where canada ='canada'
    ) as canada
    inner join (
      select count(*) as cnt, id=1
      from phpbb_buysell
      where canada ='usa'
    )as usa 
      on canada.id=usa.id
    I'm ready to get flamed by more experienced sql coders, but this worked for me in the past.

  14. #14
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    What are the results if you run this query directly into mysql?


    Code:
    select canada.cnt as canada_cnt, usa.cnt as usa_cnt
    from (
      select count(*) as cnt, id=1
      from phpbb_buysell
      where canada ='canada'
    ) as canada
    inner join (
      select count(*) as cnt, id=1
      from phpbb_buysell
      where canada ='usa'
    )as usa 
      on canada.id=usa.id
    I'm ready to get flamed by more experienced sql coders, but this worked for me in the past.
    Dont worry the way i have it now, they would probably come to my house and take my comptuer away

    Code PHP:
    $sql = 'SELECT COUNT(canada) as num_canada from phpbb_buysell WHERE canada = "canada"';
    $result = $db->sql_query($sql);
     
    	while($row = $db->sql_fetchrow( $result )) {
    $template->assign_vars(array(
        'NUM_CANADA'        => $row['num_canada'],
     
    )); 
     
    	}
    $sql = 'SELECT COUNT(canada) as num_usa from phpbb_buysell WHERE canada = "usa"';
    $result = $db->sql_query($sql);
    while($row = $db->sql_fetchrow( $result )) {
    $template->assign_vars(array(
     
        'NUM_USA'      => $row['num_usa'],
    )); 
     
    	}
    	$sql = 'SELECT COUNT(canada) as num_int from phpbb_buysell WHERE canada = "international"';
    $result = $db->sql_query($sql);
    while($row = $db->sql_fetchrow( $result )) {
    $template->assign_vars(array(
     
        'NUM_INT'      => $row['num_int'],
    )); 
     
    	}
    	$sql = 'SELECT COUNT(canada) as num_all from phpbb_buysell';
    $result = $db->sql_query($sql);
    while($row = $db->sql_fetchrow( $result )) {
    $template->assign_vars(array(
     
        'NUM_ALL'      => $row['num_all'],
    )); 
     
    	}

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tripy View Post
    ... this worked for me in the past.
    indeed, but it's kind of hackish

    this is the same concept, but cleaner:
    Code:
    select canada.cnt as canada_cnt, usa.cnt as usa_cnt
    from (
      select count(*) as cnt
      from phpbb_buysell
      where canada ='canada'
    ) as canada
    cross join (
      select count(*) as cnt
      from phpbb_buysell
      where canada ='usa'
    )as usa
    look ma, no ids

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    cleaner...
    Code:
    select *
    from (
      select count(*) as canada_cnt
      from phpbb_buysell
      where canada ='canada'
    ) as canada
    cross join (
      select count(*) as usa_cnt
      from phpbb_buysell
      where canada ='usa'
    )as usa
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    cleaner...
    Code:
    select *
    from (
      select count(*) as canada_cnt
      from phpbb_buysell
      where canada ='canada'
    ) as canada
    cross join (
      select count(*) as usa_cnt
      from phpbb_buysell
      where canada ='usa'
    )as usa
    I am getting

    SQL ERROR [ mysql4 ]

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(*) as canada_cnt from phpbb_buysell where cana [1064]

    SQL

    select * from ( select count(*) as canada_cnt from phpbb_buysell where canada ="canada" ) as canada cross join ( select count(*) as usa_cnt from phpbb_buysell where canada ="usa" )as usa

    BACKTRACE

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aren't you on at least version 4.1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    aren't you on at least version 4.1?

    MySQL 4.0.27

  20. #20
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look ma, no ids
    :-D I could not repress a giggle...
    I always forget about the cross join.
    I've been so many told that cardinal product are like crossing the rays (it's baaaaaad) that I tend to have developed some, well, hackish, workarounds, as you've seen.

  21. #21
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    aren't you on at least version 4.1?

    I just bought your book also, cant wait to read it!

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by iansteeze View Post
    MySQL 4.0.27
    can you upgrade? you need 4.1 to run queries with subqueries in 'em
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tripy View Post
    I always forget about the cross join... (it's baaaaaad)
    it's great when 1 x 1 = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can you upgrade? you need 4.1 to run queries with subqueries in 'em

    Just upgraded to mysql5 so i will try now.

    Maybe when im done reading your book i will know all this stuff

  25. #25
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok that works perfect so far

    I can figure out how to add more to it tho


    this gives me an error

    Code PHP:
    $sql = 'select *
    from (
      select count(*) as canada_cnt
      from phpbb_buysell
      where canada ="canada"
    ) as canada
    cross join (
      select count(*) as usa_cnt
      from phpbb_buysell
      where canada ="usa"
    )as usa
    cross join (
      select count(*) as int_cnt
      from phpbb_buysell
      where canada ="international"
    )as int';


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
  •