TalkPHP - How to get row number from 2 top numberd rows

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - How to get row number from 2 top numberd rows (http://www.talkphp.com/general/3916-how-get-row-number-2-top-numberd-rows.html)

ttmtake

01-27-2009 02:12 AM

How to get row number from 2 top numberd rows

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

All times are GMT. The time now is 08:52 PM.