How to Delete MySQL Row After Time Passes

How to delete mysql row after time passes?

You can use PHP script and use cron job on your cpanel.

Example:

cronjobcommand.php

<?php 
 include 'your_db_connection';
 mysql_query("DELETE FROM your_table_name WHERE Date < NOW()");
?>

I have attached a screenshot below for your more reference.

AUTO-DELETE ROW 1 Day After Inserted (MySQL)

Here's your scripts.

Insert into table1 (id, name, timestamp) values (1, 'test', now())

after insert

Delete from table1 WHERE timestamp < now() - interval 1 day;

Make SQL row delete itself after a certain time

If you want to delete user automatic read about cron job http://www.thesitewizard.com/general/set-cron-job.shtml.

Else you can check user's registration time in index.php, and delete user MySQlL Query

DELET FROM USER_TABLE WHERE time < NOW() - INTERVAL 1 WEEK

PHP: Delete row from MySQL where time passed expiration date and id not equal to 1

The STR_TO_DATE function will return null, when you use it like this - (STR_TO_DATE(expiry,'%a %d-%M-%Y%H:%i:%s') for your expiry date format.

Can you try like this ?

if($data['expiry']!=null) {
    $id = $data['id'];
    $expiry_date = date('Y-m-d H:i:s', strtotime($data['expiry']));
    $del = "DELETE FROM $itemlist WHERE id = $id AND $expiry_date<=CURRENT_TIMESTAMP)";
    if (mysqli_query($conn, $del)) {
        continue;
    }
}

How do I automatically Delete Rows from MySQL Database on specific date?

A basic query:

DELETE * FROM cms_table WHERE delete_date='$the_date_to_delete'

( see: http://dev.mysql.com/doc/refman/5.0/en/delete.html )

Delete rows older than 14 days in MySQL

I understand your point about using safe mode. If you try to use UPDATE or DELETE against a non-indexed expression, it complains, because it can't make an estimate of whether you will accidentally delete your whole table.

Using an expression on DATE(dateHeureModification) > ...
is naturally unindexed. MySQL can't do an index lookup against the result of a function.

You can use LIMIT in your delete query to make it satisfy the safe-updates mode. MySQL treats it as sufficient protection against accidentally deleting all the rows in the table, if you use LIMIT.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

It's a good idea to run the delete in limited-size batches anyway, so it doesn't create too many locks or add too much to the undo segment.

Just keep doing DELETE in a loop, deleting batches of 1000 rows at a time, and check rows-affected after each batch. Stop the loop when rows-affected reaches 0.

Another idea: I don't think you really need the DATE() function in your WHERE clause. So you might be able to do the DELETE like below, and it will be able to use an index. Besides, it should be faster to the query to check for any rows if you have an index on dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

---

Related Topics