I want to delete old records in my database that stores an entry from an HTML form which consists of
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
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
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.
DELETE FROM mytable WHERE date_of_entry <
DATE_ADD( CURDATE() ,INTERVAL validity*-1 DAY) ;
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
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
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...
I am trying to connect from a PHP application to a MySQL database using SSL
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...