Solved

recording last access to file in mysql db

Posted on 2008-06-18
6
291 Views
Last Modified: 2013-12-12
Hi,
I have a download site written in php, currently it doesn't record last access to a file in my db , but I want to add this feature , so I can remove the files which are not accessed for a long time.
first I need to know what type of data should be the field I add to mysql table , timestamp or timedate or int ?
also I need to update this field whenever a user downloads a file , I just need the mysql update statement , I know where to put it in my code :D
lastly I need to know how may I compare the last access time recorded in my db with current time , so I can delete old files.
Regards
0
Comment
Question by:fifthelement80
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:Nellios
Comment Utility
You could use any field that is easy for you to update.

The easiest approach though is to use timestamp. You can have a timestamp update its value whenever the record is updated. This way when some user downloads a file you can update the coresponding record and the timestamp value update by itself (e.g. assume you have a field NumberOfDownloads, you update this field and timestamp gets the current date time value).

If you wish to remove rows that haven't been updated you can use date_sub,date_add etc with the appropriate interval.
Example:
a) You want to delete all downloads that haven't been accessed for a week or more.

delete * from downloads where date_add(downloads.MyTimeStamp,INTERVAL 7 DAYS) < sysdate();

There are unlimited combinations on how you can perform this task.
0
 
LVL 7

Author Comment

by:fifthelement80
Comment Utility
thx for your reply , please tell me what statement should I use to add this timestamp field which updates itself.
0
 
LVL 10

Accepted Solution

by:
Nellios earned 500 total points
Comment Utility
Assuming that the table is called downloads you can use the following snipet (note it works only under mysql 5+).
If your mysql version is prior to 5, you could use just a datetime filed and update it manually with something like bellow:
/*On mysql versions after 5.0 you can add the following field*/

alter table downloads add field LastDownload timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
 

/*On mysql versions before 5.0 you can add the following field*/

alter table downloads add field LastDownload DateTime NOT NULL;

/*you can manually update it*/

update downloads set LastDownload=sysdate();

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 7

Author Comment

by:fifthelement80
Comment Utility
my sql version is 4.1.20 so I used the second solution , but this statement :
alter table downloads add field LastDownload DateTime NOT NULL;
didnt work , it was giving an error and I removed the NOT NULL and it worked , may be it needs a default value.
for deleting the old files , I would prefer to do the compare in PHP , so I can remove the files from disk too , any suggestions ?
0
 
LVL 10

Expert Comment

by:Nellios
Comment Utility
You can specify a default value according to your needs.

Since you want to perform the check with php then instead of a delete statement you can have a select statement and process the result with php. I am not the php-type of guy so I can't help you further with that :-).
0
 
LVL 7

Author Closing Comment

by:fifthelement80
Comment Utility
Thank you , I managed to do the PHP part myself
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
function to display dynamic data in dropdown 8 27
php image upload 3 24
session dropped in IE 10 18
Page showing diff display 4 19
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now