view counter using Mysql, views this week? Caching Possibly?

[jordz]

Hey All,

 

I just wondered how I would go about doing a views this week script?

Basically my hit counter will probably be inserting into the database one view per click something along the lines of this.

 

$currentviews = mysql_fetch_array(mysql_query('SELECT totalviews FROM photos WHERE photo_id = 1');
$views = $currentviews + 1;

$insert = mysql_query("INSERT INTO photos ('totalviews') VALUES ($views) WHERE photo_id = 1");

 

How would I go about have a weekviews field and it automatically reseting after 7 days?

Will this involve caching? Could someone please explain?

 

Thanks in advanced,

Jordan

[inversesoft123]

 

$timeout = 604800;
  $timeon = time()-$timeout;
  $getcalc = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM tab_lename WHERE valid>'0' AND hits>'".$timeon."'"));

 

something like this

[Dennis1986]

Create a table called photos_view or similar with the following fields:

- photo_id int(11)

- ip varchar(15)

- date datetime()

- id int(11) primary unique and with auto_increment on

 

Then on every page load you should use the following query:

"INSERT INTO photos_view VALUES ($photo_id, '$ip', now())"

 

When you need to calculate the weekly views of a picture for the current week you compare the date with today's date:

"SELECT * FROM photos_view WHERE YearWeek(date,1 ) = YearWeek(now(),1 )"

Note: YearWeek(..., 1) is if your week begins Monday. Leave out ",1" if you want it to begin on a Sunday.

 

 

Edit: The ip field is for later if you want some stats with unique views etc. Might as well be prepared for that