page statistics (views)

[Destramic]

hey guys im trying to make a page statistics, where i get a view count of certian pages users visit.

now im after a bit of advise on how the best way to achieve this

 

here are my tables:

CREATE TABLE IF NOT EXISTS `statistics` (
  
`statistic_id` int(11) NOT NULL AUTO_INCREMENT,
  
`statistic_page_id` int(11) NOT NULL,
  
`user_id` int(11) NOT NULL,
  
`reference_id` int(11) NOT NULL,
  
`ip_address` varchar(15) NOT NULL,
  
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`statistic_id`)
)
 

CREATE TABLE IF NOT EXISTS `statistic_pages` (
  
`statistic_page_id` int(11) NOT NULL AUTO_INCREMENT,
  
`page_name` varchar(90) NOT NULL,
  
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`statistic_page_id`)
) 

what i was thinking is when a user views the page it will check if user has already visited in the database

SELECT user_id 
FROM statistics
WHERE user_id = '1' OR ip_address = INET_ATON('1.1.1.1')
AND statistic_page_id = '1' AND refernce_id = '10'

if user or guest hasnt visited then add to database

INSERT INTO statistics (statistics_page_id, user_id, refernece_id, ip_address, created_timestamp) 
                VALUES ('1', '1' '10', INET_ATON('1.1.1.1'), UTC_TIMESTAMP())

statistics_page_id, would be the id representing a page ie. ITEMS

and reference_id would be the id of the ITEM

 

any advise on a better way on how it should be done would be super...cheers guys

[requinix]

Don't bother checking that - just add the new record. It's okay to have more than one row for it. Plus you get a record of every time the user visited, not just the first time.

[Jacques1]

Any reason why you don't use existing analytics tools? 

[Destramic]

Any reason why you don't use existing analytics tools? 

like what?...basically im just after the views of items users browse

 

 

Don't bother checking that - just add the new record. It's okay to have more than one row for it. Plus you get a record of every time the user visited, not just the first time.

well i was thinking of only adding to view count if user hasnt viewed page within 1 day.  although i could get all records and DISTINCT the results?

[requinix]

well i was thinking of only adding to view count if user hasnt viewed page within 1 day.  although i could get all records and DISTINCT the results?

You can always filter out information you don't want care about, but if you want to see it and it's not there you can't somehow make it appear.

 

Add a new record every time they view. Then you can look at anything you want to look at: overall statistics, "unique" views, etc.