I've been working on things a bit now, and can't seem to find how to do this one so that it works here. Maybe someone can give a bit of advice.

When individuals go to my webpage, a counter is called which grabs their stats (os, browser, referrer, etc), and throws that into an SQL table.

What I'm trying to do is pull the top 5 of each stat out of the database. Browser, OS, viewed from this table. I've tried the following:

$bquery=$db->sql_query("SELECT browser, COUNT(browser) FROM statistics GROUP BY browser");
while ($row=$db->sql_fetch_array($bquery))
{
$bcount=$row[COUNT(browser)];
$browser=$row[browser];
}

However, that returns one result, and typically the lowest result, not anything in the top 5.

Is there a better way to do this somehow? Something that I'm just not catching?

add an order by clause at the end, and use whatever "limit" capability your db has. Eg if this was Postgres you could achive what you want by tacking on

ORDER BY count DESC LIMIT 5;

linux-tech,

you need to supply an AS for your count.

"COUNT(browser) as browsercount"

And if you don't want the browser string field, you can loose the alone "browser".

linux-tech,

you need to supply an AS for your count.

"COUNT(browser) as browsercount"



$bquery=$db->sql_query("SELECT browser, COUNT( browser ) FROM statistics GROUP BY browser ORDER BY `COUNT( browser )` DESC LIMIT 0 , 1");

Did the trick for the "most popular" , and I simply changed 0,1 to 0,5 for the top 5. No "AS" needed. I thought the same thing, but it works fine without it


And if you don't want the browser string field, you can loose the alone "browser".

Actually I do, this is part of the deal. I need to be able to pull up WHAT the most popular browser is, and how many times it accessed the page, judging from the statistics I have.

$bquery=$db->sql_query("SELECT browser, COUNT( browser ) AS bcount FROM `statistics` GROUP BY `browser` ORDER BY `bcount` DESC LIMIT 0 , 1");

It's alot easier to manage it when you give it an alias, otherwise you won't be able to return the result as they will both be browser.

Anyway, the two variables returned, browser and bcount (1 row/higest count).