Angela Posted May 21, 2006 Share Posted May 21, 2006 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. Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/ Share on other sites More sharing options...
shoz Posted May 21, 2006 Share Posted May 21, 2006 [!--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. Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37690 Share on other sites More sharing options...
Angela Posted May 21, 2006 Author Share Posted May 21, 2006 [!--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? Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37691 Share on other sites More sharing options...
shoz Posted May 21, 2006 Share Posted May 21, 2006 [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. Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37696 Share on other sites More sharing options...
Angela Posted May 21, 2006 Author Share Posted May 21, 2006 Sorry... not works.Need just total of countries. I have countries and people from countries "c" (code is above). ex:Germany 2USA 4UK 7 etc.need just count how many countries are there in total and echo it. Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37700 Share on other sites More sharing options...
shoz Posted May 21, 2006 Share Posted May 21, 2006 [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] Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37704 Share on other sites More sharing options...
Angela Posted May 21, 2006 Author Share Posted May 21, 2006 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... Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37708 Share on other sites More sharing options...
shoz Posted May 21, 2006 Share Posted May 21, 2006 [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. Link to comment https://forums.phpfreaks.com/topic/10123-solved-how-to-count-countries-just-simply-total/#findComment-37710 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.