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) :
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!
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:
// 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!