select, count and group by from nuke_nsnst_ip2country

Worker Joined: Apr 26, 2005 Posts: 114

My purpose is to get the top five most visited modules based on NukeSentinels tracked ips table. I keep track for 48 hours.

Here is what i've got so far (which is kinda working, but...):

Code:

SELECT `page`, COUNT( * ) AS `mhits` FROM `nuke_nsnst_tracked_ips` WHERE 


(`page` LIKE '%name=Gallery') OR 


(`page` LIKE '%name=Forums') OR 


(`page` LIKE '%name=Discography') OR 


(`page` LIKE '%name=Calendar') OR 


(`page` LIKE '%name=Biography') OR 


(`page` LIKE '%name=Web_Links') OR 


(`page` LIKE '%name=Shout_Box') OR 


(`page` LIKE '%name=Downloads') OR 


(`page` LIKE '%name=Fans_Worldwide') OR 


(`page` LIKE '%name=News') OR 


(`page` LIKE '%name=Stories_Archive') OR 


(`page` LIKE '%name=Jukebox') 


GROUP BY 1 ORDER BY `mhits` DESC LIMIT 5

This will output a table similar to

Code:

|----------------page-------------|----mhits----|


|-/modules.php?name=Gallery-------|-----100-----|


|-/modules.php?name=Forums--------|------90-----|


|-/modules.php?name=Dischography--|------70-----|


|-/modules.php?name=Biography-----|------65-----|


|-/modules.php?name=Web_Links-----|------50-----|

Now, this seems ok, however it won't count any other pages, like /modules.php?name=Forums&file=index
If i use an additional wildcard like:

Code:

SELECT `page`, COUNT( * ) AS `mhits` FROM `nuke_nsnst_tracked_ips` WHERE 


(`page` LIKE '%name=Gallery%') OR 


(`page` LIKE '%name=Forums%') OR 


(`page` LIKE '%name=Discography%') OR 


.....


GROUP BY 1 ORDER BY `mhits` DESC LIMIT 5

That won't work either, now it counts the most visited page, not module.

Code:

|----------------page--------------------------|----mhits----|


|-/modules.php?name=Forums&file=index----------|-----350-----|


|-/modules.php?name=Forums&file=viewforum&f=4--|-----220-----|


|-/modules.php?name=Gallery--------------------|-----100-----|


...

So, what do i need to do to count all rows containing "Forums", "Gallery", etc and group them properly?

Posted: Mon Nov 06, 2006 3:40 pm

Hmm I see the SQL problem, I'm not sure how to fix it. You need to some way to grab a substring and count on that.

I know MS_Analysis kept track of module usage, but I believe they actually generate a count that is stored in seperate tables.

Posted: Mon Nov 06, 2006 6:16 pm

True, MS_Analysis does keep track. However, it's from-date-to-date module visits only...or totals.
I could of course grab data from there and make the task simple....and somehow inaccurate..., but getting the data from the NS-tables would be more than great since those data is based on latest 48 hours (or how long you chose to track) visits, not the date.

Posted: Mon Nov 06, 2006 6:19 pm

NoFantasy, how often do you need to generate the stats? If it is not "real-time", then you could write code to do this in two steps. First do what you are doing, but use PHP code to do the eregi or similar other string function to populate a temporary table with your data, but the PHP code would strip out the module name. Then, you could do another SUM/GROUP BY to get the final tally.

That is a bit "brute-force", but it would work if you didn't need it to be real-time.

Posted: Mon Nov 06, 2006 6:35 pm

It's being generated on every page actually..in a block i made me for showing some basic info on the traffic. I could of course do like

Code:

SELECT COUNT( * ) AS `Forums`


FROM `nuke_nsnst_tracked_ips` 


WHERE (`page` LIKE '%name=Forums%')





SELECT COUNT( * ) AS `Gallery`


FROM `nuke_nsnst_tracked_ips` 


WHERE (`page` LIKE '%name=Gallery%')

for every module i wanted to get data of, but it would generate about 15 queries, for every page view, which i feel is a bit...well, too much.

Posted: Mon Nov 06, 2006 6:41 pm

Ok, so you ARE thinking real-time then... bummer. I looked up using RegEx on the MySQL documentation, but could not find what could work. The problem is that you really cannot substr it as the length of the module name is not consistent. You might be better off writing something in modules.php (or mainfile.php) which stores the data you need on your own table.

Posted: Tue Nov 07, 2006 7:05 am

I played around with subqueries as i understood Evaders suggested and accidently pasted wrong code into the query window...guess what, it worked, lol

Code:

SELECT


COUNT(`page`) AS `Total`,


COUNT(`page` LIKE '%name=Gallery%' OR NULL) as `Gallery`,


COUNT(`page` LIKE '%name=Forums%' OR NULL) as `Forums`,


COUNT(`page` LIKE '%name=Discography%' OR NULL) as `Discography`,


COUNT(`page` LIKE '%name=Calendar%' OR NULL) as `Calendar`,


COUNT(`page` LIKE '%name=Biography%' OR NULL) as `Biography`,


COUNT(`page` LIKE '%name=Web_Links%' OR NULL) as `Web_Links`,


COUNT(`page` LIKE '%name=Shout_Box%' OR NULL) as `Shout_Box`,


COUNT(`page` LIKE '%name=Downloads%' OR NULL) as `Downloads`,


COUNT(`page` LIKE '%name=Fans_Worldwide%' OR NULL) as `Fans_Worldwide`,


COUNT(`page` LIKE '%name=News%' OR NULL) as `News`,


COUNT(`page` LIKE '%name=Stories_Archive%' OR NULL) as `Stories_Archive`,


COUNT(`page` LIKE '%name=Jukebox%' OR NULL) as `Jukebox`


FROM `nuke_nsnst_tracked_ips`

Don't ask me why the OR NULL has to be in there, but if it's not, the query will count totals.
This query will show as

Code:

|--Total---|--Gallery--|--Forums--.....


|---xxxx--|---xxxx---|---xxxx----.....

Well, at least it works in some way, i get the numbers i want. Now i have to figure out the php code to show the 5 highest numbers only, heh...any hints?

Posted: Tue Nov 07, 2006 8:40 am

Hmm I take it they are coming out as columns rather than rows, so you couldn't use LIMIT

You could write some PHP code to put them into an array and sort it. Then pull the top 5 entries off the array.

Posted: Tue Nov 07, 2006 9:43 am

...true, they are. Having them coming out as

Code:

|--Gallery------|-----xxx-----| 


|--Forums------|-----xxx-----| 


|--Discography-|-----xxx-----|

instead of

Code:

|--Total---|--Gallery--|--Forums--|


|---xxxx--|---xxxx---|---xxxx----|

would make everything alot easier, so im still looking out for a better query. Can't give up when walked this far Very Happy