Solved

counter question

Posted on 2002-07-26
7
224 Views
Last Modified: 2008-02-26
I have a counter system which counts the number of hits to a dynamic page.

so http://whatever.com/member/view.php?id=12

ups id 12 in a mysql database within a table called counter. Easy enough so far.

What I want is a system which has a record of number of hits today, this week, totals for each of the last 12 months, grand total.

I'm sure there is an elegant solution to this!
0
Comment
Question by:macgruder
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7179820
If all you do is increase a count for the ID, then you will not have enough data to track when these hits occur.

You can ask your ISP for the log of the hits to your site and filter the ones you want. From there, you can use one of many different log reporting tools or roll your own.

If you want to create your own log, then you will need to store each hit seperately with the date/time against the hit and any other information you want (agentstring, IP, etc).

Your reporting would be basically a select on this table with a date/time range applied, giving you an hour, a day, a week, month, year, 17.5 minutes, whatever.

You CAN use a text file (like a normal log file) and this would probably be quicker, but if you have a LOT of simultaneous hits, you MAY miss some. Using a DB would be safer (assuming queuing or multi-threading updates).

Richard.
0
 
LVL 4

Author Comment

by:macgruder
ID: 7179903
The script can choose any information such as time as I want. The question is what is the most efficient way to store the information. I'm not interested in a detailed stat analysis of my site - I have that with Webalizer. Rather just something that says X is the most popular this week/today or whatever.

I have this in my script

               $query = mysql_query("update $table SET count = LAST_INSERT_ID(count+1), date = now() where name = '$id'") or return_error_png("no up");
               
What I need is someway that at the beginning of the month totals are reset to zero and old months are shifted back one if you see what I mean.

i think there must be a nice way to do it, but I'm too busy to work it out right now.

I have my own server so i can do anything basically - within the small limits of my ability of course ;-)
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7179972
Ok. I understand this.

Say you have the following table.

UniqueID
Range Name
DateTimeStart
DateTimeEnd
Count

This table could then hold the following data ...

ID Name Start End Count
1,Year,2002 2002-01-01 00:00:00.000,2002-12-31 23:59:59.999,0
1,July 2002,2002-07-01 00:00:00.000,2002-07-31 23:59:59.999,0
1,Friday 26th July 2002,2002-07-26 00:00:00.000, 2002-07-26 23:59:59.999,0

Your SQL would then be something like ...

UPDATA table
SET count=count+1
WHERE start <= now() AND now() <= end

The "clever" bit is that you can insert into the table any record for any range you want. You can do this every time the table is loaded, so you can add in today, the month, this year.

You might want to hold another table holding the last entries added for each range. So you can retrieve the list of known range types (and when they where last added) see if the range has expired. If it has then insert a new record for that range. You then increment the count for all ranges that match.

I'm not sure I've explained myself very well, but I hope this makes sense.

Richard.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 7179985
Ranges Table

ID, Type, LastEndInserted
1,     Year,   2002-12-31 23:59:59.999
2,  Month,  2002-07-31 23:59:59.999
3,  Daily,  2002-07-26 23:59:59.999
4,  Weekly, 2002-07-27 23:59:59.999

You can query this table as ...

SELECT * FROM ranges WHERE LastEndInserted < NOW()

This will give you a list of all the new range records you need to create.

Say we did this tomorrow, based on the above data we would get ...

3,  Daily,  2002-07-26 23:59:59.999

If we had no updates until Monday we would get ...

3,  Daily,  2002-07-26 23:59:59.999
4,  Weekly, 2002-07-27 23:59:59.999


You may want to create empty records for missed days, but I think you shouldn't. Waste of space.

0
 
LVL 2

Accepted Solution

by:
td234 earned 100 total points
ID: 7180936
Hey macgruder.

I do similar logging and I do it diffeenlty than the other solution. Not saying mine is better, just different.

I create a table entry for each date and count the hits. So, when the page loads, I do something like this:

if(isset($property_id)) {
     $ignore_ips = array("127.0.0.1");
     if(!in_array("$REMOTE_ADDR",$ignore_ips)) {
          $qid_co = db_query("
          SELECT clicks
          FROM .link_clicks
          WHERE id ='$id'
          AND date = NOW()
          ");
          if(db_num_rows($qid_co)>0) {
               $qid = db_query("
               UPDATE link_clicks
               SET clicks = clicks+1
               WHERE id ='$id'
               AND date = NOW()
               ");
          } else {
               $qid = db_query("
               INSERT INTO link_clicks (id, date, clicks)
               VALUES('$property_id', NOW(), '1')
               ");
          }
     }
}

As you can probably tell, I first see if there is an entry for the current date. If yes then I increment that counter. If not then I create one and set the counter to 1. I also exclude counting from certain IP addresses to be honest.

Once your data is in the table like this, it is easy to create the lookups you wanted. Here is how you can pull all the records for this month:

     $search_date = date('Y-m');
SELECT l.date,l.clicks
          FROM clicks l
          WHERE date like '$search_date%'
          AND l.id = $id
          ORDER by l.date

You can even use SUM(clicks) to sow the total of all the clicks or COUNT(click) to show the number or records (also equal to the number of rows in this case).

I hope this helps.

THOM
0
 
LVL 4

Author Comment

by:macgruder
ID: 7184991
Thanks guys,
I'll be a little busy over the next few days, but I'll try to test these two and let you know how I got on and then to the points!
0
 
LVL 4

Author Comment

by:macgruder
ID: 7198757
Thanks a lot for all the comments, guys. Thom's answer is easy to implement into my code.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question