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

[Angela]

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.

[shoz]

[!--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.

[Angela]

[!--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?

[shoz]

[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.

[Angela]

Sorry... not works.

Need just total of countries. I have countries and people from countries "c" (code is above). ex:

Germany 2
USA 4
UK 7
etc.

need just count how many countries are there in total and echo it.

[shoz]

[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]

[Angela]

Now it works
[code]<? print mysql_num_rows(mysql_query("SELECT DISTINCT country FROM ".C_MYSQL_MEMBERS)); ?>[/code] thank you shoz!

How to mark this thread resolved? I'm new here...

[shoz]

[quote]
How to mark this thread resolved? I'm new here...
[/quote]
That feature is no longer available to normal users. I'll mark it solved.