Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

recording last access to file in mysql db

Posted on 2008-06-18
6
Medium Priority
?
318 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
ID: 21810695
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
ID: 21810733
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 1500 total points
ID: 21810785
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 7

Author Comment

by:fifthelement80
ID: 21810959
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
ID: 21810990
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
ID: 31468237
Thank you , I managed to do the PHP part myself
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

972 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