PHP and mysql pageviews

overall pageviws is ok but what about pageviws in last week?

[achshar]

ok i am making this articles and news posts for my site... for views i 'v been using my sql. increase the pageviews value every time the page loads...

but being a news system it has to show the popular posts on the homepage. I can do that the one with highest page views.. easy.. but it also has to be 'popular today' or 'last week'.. and some old stories can have more page views that the new ones...

so my question is how to take the pageviews for a specific time period... can also be in hours days weeks or months .... i know php could help..

i can code my self but here i dont know what to code..! like what has to be the frame or the basic idea/functioning for this.. plz help
Thanks :)

[chadhaajay]

You need to create a new table for article pageviews and news pageviews. In that table, you need to have 4 fields

view_id
type
id
view_datetime

where view_id is an autoincrement field, type can be an enum field with any of the 2 values 'article' or 'news', id is an INT field that contains the article ID if type is 'article' or news ID if type is 'news', view_datetime is a DATETIME field that will contain the date and time of every pageview.

You need not to create a hits_count or views_count field since you can get the count of pageview via SELECT COUNT(view_id) FROM tablaname WHERE type='article' AND id='121'

The above query will give you the total number of views of article id 121 from above table.

[achshar]

thanks ajay thanks a ton! That was exactly what i needed.....
but just one question i am on shred hosting and if the page views are in large quantity... wont that affect the my sql response time? not only to views but also to every thing else in my databases...?

plus is it the 'idle' way to do this... i.e do you (or you know anybody who) do your pageviws (if any) this way? like a new row for 'E V E R Y' pageview... i hope you know it can be in thousands or even millions.... wont that be a few extra mbs on my limited web space...

or i could delete the views which are older than a month or so... and still have an over all views in my main table (as i do it now)
would that be good to save that extra space and speed..?

[chadhaajay]

Yes, we have implemented the exactly same system to track page-views of articles in our knowledge base software. you need not to worry about a new row for every page-view as it is most easy and feasible approach to implement page views system with date and time for each page view.

Since you have date & time in your records, so you can easily delete records before 6 months, 1 year or whatever time period you need. It is always good to keep record of last one year of pageviews and delete the older records.

[achshar]

ok cool thanks then... i'll be doing the very same thing... :)