Delete a row after 1 month or 3 month based on selected validity

143
April 10, 2018, at 09:02 AM

I want to delete old records in my database that stores an entry from an HTML form which consists of

  1. ID
  2. Name
  3. email
  4. date_of_entry (timestamp recorded when the user submits form)
  5. validity (user can choose either 30days or 90days as validity)

I want to expire the row and delete it after 30/90 days based on the validity period selected from the date_of_entry

I found this, but how do I modify it to suit my need

DELETE FROM myTable WHERE date_of_entry < DATEADD(MONTH, -1, GETDATE())

Also please note that i'm using a form which sends the value validity as 30 or 90$duration = $_POST['duration'];

And this check should be performed by the MySQL itself. I'm using phpMyAdmin

Answer 1

Basically you must delete obsolete rows where current date exceeds date_of_entry plus validity days in this way:

now() > date_add(date_of_entry, interval validity day)
create table tbl (id int, date_of_entry datetime, validity int);
insert into tbl values
(1, DATE_ADD(NOW(), INTERVAL -31 DAY), 30),
(1, DATE_ADD(NOW(), INTERVAL -29 DAY), 30),
(1, DATE_ADD(NOW(), INTERVAL -92 DAY), 90),
(1, DATE_ADD(NOW(), INTERVAL -89 DAY), 90);
delete 
from   tbl
where  now() > date_add(date_of_entry, interval validity day)
select * from tbl;
id | date_of_entry       | validity
-: | :------------------ | -------:
 1 | 2018-03-11 18:16:43 |       30
 1 | 2018-01-10 18:16:43 |       90

dbfiddle here

Answer 2

In MySQL you would do something like:

delete t from mytable t
    where date_of_entry < curdate() - interval validity day;

This assumes that validity is either the number 30 or 90.

Answer 3
DELETE FROM mytable WHERE date_of_entry <
  DATE_ADD( CURDATE() ,INTERVAL validity*-1 DAY) ;
Rent Charter Buses Company
READ ALSO
Audit Trail concept in php, mysql [on hold]

Audit Trail concept in php, mysql [on hold]

I want to use Audit trail concept in php and mysql web applicationI need to track which user edit which field at what time frame? Also i need to compare the current version of saved data with previous version

132
Laravel eloquent query table names case insensitive

Laravel eloquent query table names case insensitive

Is it possible to make eloquent queries case insensitive? An issue came when I decided to move my website to production (move from my local machine to the host server)MariaDB on my host server is case sensitive and I don't have an access to its config...

240
MySQL SSL connection php 5.3.3

MySQL SSL connection php 5.3.3

I am trying to connect from a PHP application to a MySQL database using SSL

173
Why are html and php files not properly exchanging information?

Why are html and php files not properly exchanging information?

I am creating a website where a user logs in, enters information that is submitted to a database, and then can call information from the database that will be inserted into an html text elementI have separate html files for each website page, and multiple...

145