How do I delete old records in my database using Mysqli?

Question

I'm creating an online noticeboard and I would like to find out how to delete old notices a day after they have been put up so it doesn't clutter the noticeboard. I have no code for it atm because I don't have a clue how to do it. Any help would be appreciated, sorry if this question is a bit tedious.

Answer 1

Your "notices" table should have a field for a "creation_time", this field would be filled in with NOW() when you insert a new row. Then you should create a query which would compare creation_time against the current time, and if it has been longer than 24 hours, you would delete the row.

TIMESTAMPDIFF(DAY, creation_time, NOW()) should get you started as far as code is concerned. http://www.w3resource.com/mysql/date-and-time-functions/mysql-timestampdiff-function.php

As for automating it, this post could be helpful: Run a mySQL query as a cron job?

Answer 2

Just add a column with a timestamp for your notices and then you run a script periodically that deletes old ones.

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "DELETE FROM notices WHERE time_created < timestampadd(hour, -24, NOW())";

if ($conn->query($sql) === TRUE) {
    ...
} else {
    ...
}

$conn->close();