Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

recording last access to file in mysql db

Posted on 2008-06-18
6
Medium Priority
?
314 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 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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