[SOLVED] Displaying The 10 Most Viewed Pages

[suttercain]

Hi everyone,

 

I built a page view counter that collects the page id (comicId) and the user's IP addresss so it doesn't get counter twice.

 

I would now like to display the top 10 most viewed pages. I need to get a total count of every time the comicId appears in the table and display the ten highest totals.

 

I tried using

 

<?php $tabsOne = mysql_query("SELECT *, COUNT(*) FROM comicCounter GROUP BY comicId LIMIT 0, 10") or die(mysql_error()); ?>

 

But I don't think this is the right path. Any ideas?

 

Thanks.

 

SC

[pocobueno1388]

Can you show us how your database table is setup? Give us the fields, and an example of what could be in the field.

[suttercain]

No problemo

 

TABLE comicCounter

 

comicId    |      ipAddress    |    comicCounterId

4454        | 111.111.111.21  |              1

6767        | 111.111.111.21  |              2

7878        | 111.111.111.21  |              3

4454        | 421.121.121.21  |              4

 

That is the basic set up, times 1000's of records. But with this set up I would need 4454 to be counted as 2 and  6767 as 1 and 7878 as 1. Then I would need to display 4454 since it had two views and therefore is the most popular.

 

SC

[suttercain]

Update,

 

using the same mysql statement I posted originally, I can echo the total count for each and limit by 10, BUT I cannot get it to 'ORDER BY COUNT'...

 

Anyone?

 

Thanks

[pocobueno1388]

Try this

 

SELECT *, COUNT(*) AS count FROM comicCounter GROUP BY comicId ORDER BY count DESC LIMIT 0, 10

[suttercain]

That did it. Awesome. Was it the as clause that made the difference? Thanks for your help.

 

SC

[pocobueno1388]

Yeah, it was the AS clause. You had to name the count in order to order it by the count.