Jump to content

Archived

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

Angela

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

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.

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites

×

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.