Solved

recording last access to file in mysql db

Posted on 2008-06-18
6
302 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 500 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Backup Strategy 15 44
Complex MySQL Query 2 33
How would I construct this INSERT statement in PDO? 6 18
Extracting store locations from Google maps or site 2 23
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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…
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.

829 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