What kind of structure will I need? (php/mysql)

[JonD]

Im creating site for someone and they want a hit counter that has this information:

60 visits (today)
57 visits (yesterday)
476 visits (month)
2244 visits total

What kind of table structure will I need and what PHP stuff will I need to do to work this out?

Not got my thinkin hat on today.

Thx for your help.
JD

 

[Mr^B]

If it's just number of hits and nothing else...

Table Structure:

tblHits
hit_id (int) (auto_increment/Identity)
hit_date (datetime)

Add code to a top-level include/header/master page that runs this bit of sql

"INSERT INTO tblHits VALUES(Now());"

Which will store the date/time of the hit

Then just use different SQL queries to bring back the correct data

"SELECT COUNT(hit_id) As noHits FROM tblHits where hit_date between <start_date> AND <end_date>;"

For example, will give the number of hits between the two dates.

Probably.

 

[JonD]

I thought about that but the site has had over 5000 hits in about 5 days. In a years time this could easily be in the 500000+ figure mark?

So is it the best way to store each hit coz the table could get potentially large?

I was thinkin like:

table: hits
totalhits
dayhits
weekhits
monthhits

but how can i calculate when to reset each week/month/day etc?

jd

 

[happytechie]

500000 records isn't a big deal for a database to manage, if you cared you could easily run a scheduled job at the end of every month to prune the previous month's records and store them in another table.

HT

 

[Mr^B]

Agreed, 500,000 rows is nothing; 500,000 small rows is less than nothing.

Even with 50,000,000 rows the database isn't going to be much more than half a Gb in size (about 575Mb @ 12 bytes per row).

 

[JonD]

Heheheh

ok point made chaps. Thx.

So what queries can work out how many hits today, this week and this month?

 

[happytechie]

If it's just number of hits and nothing else...

Table Structure:

tblHits
hit_id (int) (auto_increment/Identity)
hit_date (datetime)

Add code to a top-level include/header/master page that runs this bit of sql

"INSERT INTO tblHits VALUES(Now());"

Which will store the date/time of the hit

Then just use different SQL queries to bring back the correct data

"SELECT COUNT(hit_id) As noHits FROM tblHits where hit_date between <start_date> AND <end_date>;"

For example, will give the number of hits between the two dates.

Probably.

that query

 

[JonD]

Yeh I understand that its how I calculate the start date of the queries?

now() - start of month, start of week etc.

jd

 

[toString]

<?php
$tomorrow  = mktime(0, 0, 0, date("m")  , date("d")+1, date("Y"));
$lastmonth = mktime(0, 0, 0, date("m")-1, date("d"),  date("Y"));
$nextyear  = mktime(0, 0, 0, date("m"),  date("d"),  date("Y")+1);
?>

From http://uk.php.net/date

 

[JonD]

oh right I get it now.

Thx

jd