Jump to content

*SOLVED* How to count countries (just simply total)


Angela

Recommended Posts

This code gives country and number of people from country "c":

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$tmp=mysql_query("SELECT country, count(*) as c FROM ".C_MYSQL_MEMBERS." group by country order by country asc");
while($i=mysql_fetch_array($tmp)) {
<?=$wcr[$i['country']]?><?=$i['c']?> }[/quote]
Need count how many countries are there (total). Please help.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] [color=blue]COUNT[/color]([color=green]DISTINCT[/color] country) [color=green]FROM[/color] [color=orange]table[/color] [!--sql2--][/div][!--sql3--]
You could also modify your existing query to retrieve the total in addition to the indvidual counts using [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html\" target=\"_blank\"]ROLLUP[/a] (added in MYSQL 4.1.1). You wouldn't be able to use ORDER BY however and would need to use PHP to do the sorting.
[!--quoteo(post=375770:date=May 21 2006, 10:09 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 21 2006, 10:09 AM) [snapback]375770[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] [color=blue]COUNT[/color]([color=green]DISTINCT[/color] country) [color=green]FROM[/color] [color=orange]table[/color] [!--sql2--][/div][!--sql3--]
You could also modify your existing query to retrieve the total in addition to the indvidual counts using [a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html\" target=\"_blank\"]ROLLUP[/a] (added in MYSQL 4.1.1). You wouldn't be able to use ORDER BY however and would need to use PHP to do the sorting.
[/quote]
How to echo it?
[quote]
How to echo it?
[/quote]
It will be easier if the column is given an alias
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] [color=blue]COUNT[/color]([color=green]DISTINCT[/color] country) [color=green]AS[/color] total [color=green]FROM[/color] [color=orange]table[/color] [!--sql2--][/div][!--sql3--]
The above gives the column the alias "total". eg: $row['total'];

If this is a general mysql data retrieval question then take a look at the phpfreaks.com [a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=31047\" target=\"_blank\"]PHP-FAQ[/a] under [b]MySQL Data Retrieval[/b] (Do a text search).

There are multiple sections dealing with Mysql data retrieval in the FAQ. You can do a 'find next' (or whichever method means the same) in your browser.

Afterwards, if there are any problems (or for others looking at the thread at a later date) you can post the script with the progress you've made and any questions you have.
[quote]
Sorry... not works.
[/quote]
What doesn't work?. Have you looked at the FAQ for information on how to retrieve information from a MYSQL database? The query posted should give you the results you're looking for.

In your previous example I assume you want the total to be 3? Post the script as you have it now with the query that I posted, and I'll help you further.

If you're saying that you're looking for the total in addition to the list as you've made it you can use the following.
[code]
$tmp=mysql_query("SELECT country, count(*) as c FROM ".C_MYSQL_MEMBERS." group by country order by country asc");
$prev = '';
$total = 0;
while($i=mysql_fetch_array($tmp)) {
    if ($prev != $i['country']){
        $total++;
        $prev = $i['country'];
    }
    <?=$wcr[$i['country']]?><?=$i['c']?>
}
echo $total;
[/code]

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.