Solved

recording last access to file in mysql db

Posted on 2008-06-18
6
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

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…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

739 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