How to get row number from 2 top numberd rows

ttmtake · 01-27-2009, 02:12 AM

Hi, i need little help with this.

I have mysql like this:

PHP Code:


			
id          browser            country
--------------------------------------
1            MSIE               Japan  
2            MSIE               Europe 
3            FIREFOX            Canada
4            MSIE               Canada 
5            FIREFOX            Europe 
6            MSIE               France 
7            MSIE               USA
8            FIREFOX            Europe 
9            MSIE               Dansk

Now i need to get number of rows for 2 top used countys, in this case that is 1=Europe and 2=Canada, so need to get $europe = 3 rows AND $canada = 2 rows.

I cant just count rows becouse i dont know which country is in most rows.

Wildhoney · 01-27-2009, 02:25 AM

Please try the following query. I hope this is something like what you're after!

sql Code:

SELECT
    country,
    COUNT(*) AS num_entries
FROM
    countries
GROUP BY
    country
ORDER BY
    num_entries DESC

ttmtake · 01-27-2009, 03:50 AM

Thank you, it is what iam looking.
With this code i can get first 2 names of most used countrys:

PHP Code:


			
$sql=mysql_query("SELECT
    country,
    COUNT(*) AS num_entries
FROM
    countrys
GROUP BY
    country
ORDER BY
    num_entries DESC LIMIT 2");
    
while ($row = mysql_fetch_array($sql)){
echo $row['country'];
}

Now, how can i get row number where 1 country is used and where 2 country is used. Like Europe = 3 rows

ttmtake · 01-27-2009, 04:08 AM

I think i got something, but meybe thers better way.

PHP Code:


			
$sql=mysql_query("SELECT
    country,
    COUNT(*) AS num_entries
FROM
    countrys
GROUP BY
    country
ORDER BY
    num_entries DESC LIMIT 2");
    
while ($row = mysql_fetch_array($sql)){
$country = $row['country'];
$numresults = mysql_query("SELECT * FROM `countrys` WHERE country='$country'"); 
$num=mysql_num_rows($numresults);
echo $row['country'];
echo $num;
}

Wildhoney · 01-27-2009, 10:42 PM

Hmm. I think the query I posted to you originally will do it for you. Try something like the following in your PHP code, after the query:

php Code:

while ($row = mysql_fetch_array($sql))
{
    printf("The country %s has %d entries",
    $row['country'], $row['num_entries']);  
}

Is this what you're after? It will display how many rows there are of that particular country, ordered with the most popular at the top.

ttmtake · 01-28-2009, 11:16 PM

Yes, thats it, i think i was try this after you first answer, hmm, butt now all work good. Thanks