SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting actual count from total count

    I'm trying to get the ACTUAL count of countries from the TOTAL count of countries in my fanlistings tables (a members database of fandom).
    Basically I have table which has over 900 members and an ACTUAL count of 60 different countries. HOWEVER my code (shown below) is counting countries appearing more than once where members are from, to get the TOTAL which is 220.

    1. I have a table called "owned" which is made up of what I call subtables, because it has rows like: id, name, url, pending, current, approved. Inside these rows, there are rows that have its own info like: id, name, email, website, password, country, approved.

    2. How can I get my code to read COUNTRY COUNT IN TOTAL > 220 > ACTUAL COUNTRIES (not counted more than once): 60.

    To break things down, it's doing this:

    Members from USA: 20
    Members from Canada: 10

    USA appearing: 20 times + Canada appearing: 10 times = 30 countries.

    When I want it to do: USA appearing: 20 times > grouped into 1 count + Canada appearing: 10 times > grouped into: 1 count = 2 countries.

    So here's my code which is returning the result of 220 countries (including countries appearing more than once in a count) :

    PHP Code:
    // collective total countries (approved)
    $collective_total_countries 0;
    $ownedarray get_owned'current' );
    $query '';
    foreach( 
    $ownedarray as $o ) {
       
    $info get_listing_info$o );
       
    $table $info['dbtable'];
       
    $dbserver $info['dbserver'];
       
    $dbdatabase $info['dbdatabase'];
       
    $dbuser $info['dbuser'];
       
    $dbpassword $info['dbpassword'];

       if( 
    $dbserver != $db_server || $dbdatabase != $db_database ||
          
    $dbuser != $db_user || $dbpassword != $db_password ) {
          
    // if not on same database, get counts NOW except if it can't be accessed; if not, skip this one
          
    $db_link mysql_connect$dbserver$dbuser$dbpassword );
          if( 
    $db_link === false )
              continue; 
    // if it can't be accessed; if not, skip this one
          
    $connected mysql_select_db$dbdatabase );
          if( !
    $connected )
             continue; 
    // if it can't be accessed; if not, skip this one
          
    $thisone "SELECT DISTINCT( `country` ) AS `total` FROM `$table` WHERE `pending` = 0";
          
    $result mysql_query$thisone );
          if( !
    $result ) {
             
    log_error__FILE__ ':' __LINE__,
                
    'Error executing query: <i>' mysql_error() .
                
    '</i>; Query is: <code>' $query '</code>' );
             die( 
    STANDARD_ERROR );
          }
          
    $row mysql_fetch_array$result );
          
    $collective_total_countries += $row['total'];
       } else {
          
    $query .= "SELECT COUNT(DISTINCT( `country` )) AS `country` FROM `$table` WHERE `pending` = 0";
          
    $query .= " !!! ";
       }
    }
    $query rtrim$query"! " );
    $query str_replace'!!!''UNION ALL'$query );

    $db_link mysql_connect$db_server$db_user$db_password )
       or die( 
    DATABASE_CONNECT_ERROR mysql_error() );
    mysql_select_db$db_database )
       or die( 
    DATABASE_CONNECT_ERROR mysql_error() );
    if( 
    $query != '' ) { // if there IS a query
       
    $result mysql_query$query );
       if( !
    $result ) {
          
    log_error__FILE__ ':' __LINE__,
             
    'Error executing query: <i>' mysql_error() .
             
    '</i>; Query is: <code>' $query '</code>' );
          die( 
    STANDARD_ERROR );
       }
       while( 
    $row mysql_fetch_array$result ) ) {
          
    $collective_total_countries += $row['country'];
       }

    I've read I need subqueries like:

    query = "SELECT COUNT(DISTINCT `country`) FROM (SELECT DISTINCT `country` FROM $table) AS `country` GROUP BY `country`";

    but I'm not exactly sure WHERE to put it or HOW to put it in...
    I'm figuring it's got to be somewhere before my UNION ALL (unioning all my mini-tables in my OWNED table).

    Any help would be appreciated as this is really annoying me!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i didn't even try to read the PHP code, but is all you want a count of the number of records per country?
    Code:
    select country
         , count(*) as country_count
      from country
    group
        by country

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    subtables? mini tables?

    SELECT COUNT(DISTINCT country) FROM $table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT COUNT(DISTINCT country) FROM $table
    I tried this in the first place, it only gives 220 in return (this is all the countries counted more than once e.g. America 20 times)

    select country
    , count(*) as country_count
    from country
    group
    by country
    I can't define my table as country because there's not a country table in owned table. And * is everything. I don't need that.
    I read about subqueries (select in a select query) but I don't fully understand that.

    I need it to something like this: $query = "SELECT DISTINCT(country) FROM (SELECT COUNT(DISTINCT(country)) FROM $table) AS country_count GROUP BY country";

    I'm not sure where to put it though, I know it has go after my UNION ALL because I have UNION ALL my subtables of owned and then count the countries only ONCE.

    EDIT

    Gah I'm a doofus! I was looking at the wrong table >_< owned has all my information of my fanlistings, but my members are in their own tables...I'm an idiot.

    Ok I have 25 members tables (each with their own subject...so this may be easier to understand now).

    I need UNION ALL my member tables (not my non-member tables) THEN select the countries (total of 60 (only counting them once. There's 220 if I count the ones reappearing more than once)).

    So I'd actually need to do: SELECT DISTINCT(`country`) AS `country` FROM $table WHERE `pending` = 0 GROUP BY `country` UNION ALL

    Or something like that...right?

    I'd actually write it like this: SELECT DISTINCT(`country`) as `country` FROM mytablename WHERE `pending` = 0 GROUP BY `country` UNION
    (repeat the select with a different table name and so on)

    But I keep adding tables and I don't want to keep updating my script for a new table added.
    Last edited by Aymie Jordaine; Apr 8, 2007 at 13:30.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i read that whole post and was completely confused, until i got to this line:
    Quote Originally Posted by Aymie Jordaine View Post
    But I keep adding tables and I don't want to keep updating my script for a new table added.
    well, there's your problem! stop adding new tables!

    you imply that you have one table per user. this is poor design. use one table, and add a username or user_id column.


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
  •