Jump to content


Photo

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


  • Please log in to reply
7 replies to this topic

#1 Angela

Angela
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 May 2006 - 02:55 PM

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.

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 May 2006 - 03:09 PM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(DISTINCT country) FROM table [!--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.


#3 Angela

Angela
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 May 2006 - 03:12 PM

[!--quoteo(post=375770:date=May 21 2006, 10:09 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 21 2006, 10:09 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(DISTINCT country) FROM table [!--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?

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 May 2006 - 03:22 PM

How to echo it?

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] COUNT(DISTINCT country) AS total FROM table [!--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 MySQL Data Retrieval (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.

#5 Angela

Angela
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 May 2006 - 03:38 PM

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.

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 May 2006 - 03:55 PM

Sorry... not works.

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.
$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;


#7 Angela

Angela
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 21 May 2006 - 04:10 PM

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

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

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 May 2006 - 04:26 PM

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

That feature is no longer available to normal users. I'll mark it solved.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users