Popular Downloads , Download Counter Date Line

[acegames]

Hello , I have a database for downloads and a download counter but I want to add the "Popular downlads" for that week , so need to add a timeline somehow so the downloads counter only gets updated within a weeks timescale , instead of all the time , as my older downloads are allways at the top of the download counter

 

This is how I update the counter

mysql_query("UPDATE $table SET downloads=downloads+1 WHERE id='$id'");

 

 

This is my table layout

 

`id` int(5) NOT NULL auto_increment,
  `filename` varchar(200) NOT NULL default '',
  `dispname` varchar(200) NOT NULL default '',
  `member` varchar(50) NOT NULL default '',
  `timestamp` varchar(50) NOT NULL default '',
  `downloads` int( NOT NULL default '0',

 

 

Please help if you can im pulling my hair out

 

[MadTechie]

What about another table, ie download_log

 

`id` int(5) NOT NULL auto_increment, //uid

`did` int(5) NOT NULL , //download ID

`timedate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

 

then just insert a record on every download this will give you a better idea of stats

but remember to include a clean up,, ie delete all records where the timestamp is older that a week/month/year whatever!

[acegames]

I would prefer to have a new row on the excisting table as I have the downloads row on each of my tables

 

I have about 100 different tables for the different filetypes , but I just want one of the tables to be able to show the "Popular Downlaods" this week

[MadTechie]

Do all the tables have the same schema  ?

wouldn't it be easier to have an extra field ?

[acegames]

Do all the tables have the same schema  ?

wouldn't it be easier to have an extra field ?

 

Yes they are all the same

 

Sorry that is what I meant an extra field

[MadTechie]

Okay let give an example

if i was going to store details from 3 files types ie jpg,png and gif

 

I could create 3 tables

ie

Table: jpeg_file

Fields: height, width, filename

 

Table: png_file

Fields: height, width, filename

 

Table: gif_file

Fields: height, width, filename

 

But as each hold the same data it would be easier to create 1 table and add a field for each type

ie

Table: image_file

Fields: height, width, filename, filetype

 

filetype would be set to "jpg" or "png" or "gif"

 

[acegames]

Okay let give an example

if i was going to store details from 3 files types ie jpg,png and gif

 

I could create 3 tables

ie

Table: jpeg_file

Fields: height, width, filename

 

Table: png_file

Fields: height, width, filename

 

Table: gif_file

Fields: height, width, filename

 

But as each hold the same data it would be easier to create 1 table and add a field for each type

ie

Table: image_file

Fields: height, width, filename, filetype

 

filetype would be set to "jpg" or "png" or "gif"

 

 

Yes that looks better but I have allready got all my database set up now , I shuld have done it that way to start