How do I delete rows of data from mysql table automatically with 24 hours after data into table?

Question

For example, i have a data input program And I want to delete my data automatically after 1 day of this data I input. how I do that?
Someone can explain in code?

Create.php these values are sent to the server

<form action="" method="post" enctype="multipart/form-data">
    <input type="text" name="name_portofolio">
    <textarea name="info_portofolio"></textarea>
    <input type="file" accept="image/*"  name="picture_portofolio"> 
    <button type="submit" name="submit">Save</button>
</form>

function-add.php

<?php

function create_data($name_portofolio, $info_portofolio,
                  $picture_portofolio)
{
   global $connect;
   $name_portofolio = mysqli_real_escape_string($connect, $name_portofolio);
   $info_portofolio = mysqli_real_escape_string($connect, $info_portofolio);


   $filePath = "picture/".basename($picture_portofolio["name"]);
   move_uploaded_file($picture_portofolio["tmp_name"], $filePath);

   $query = "INSERT INTO portofolio 
         (name_portofolio, info_portofolio, picture_portofolio) 
          VALUES ('$name_portofolio', '$info_portofolio', '$filePath')";

   if(mysqli_query($connect, $query))
   {
      return true;
   }else{
      return false;
   }
  } // create_data

db.php

<?php
$host = "127.0.0.1";
$user = "root";
$password = "";
$db = "wherco";

// create connection
$connect = new mysqli($host, $user, $password, $db);

// check connection
if($connect->connect_error) {
    die("connection failed : " . $connect->connect_error);
} else {
    // echo "Successfully Connected";
}

?>

thanks .

@wawanD [What is cron job](http://support.hostgator.com/articles/cpanel/what-are-cron-jobs) — hungrykoala, May 30 '17 at 05:05
@wawanD nope, unless you want to keep your script running 24/7 and that would be a waste of memory. So your best solution would be to use a cron job. — hungrykoala, May 30 '17 at 05:24
Does this really work? When I delete by detail, example "where status = 1"? — , May 30 '17 at 05:27
Please never use SQL queries like that, they are highly vulnerable for SQL injection — Nico Haase, Jul 02 '21 at 12:38

Alexander · Answer 1 · 2018-10-27T04:32:26.870

Try to use regular events. To get started, enable the Event Scheduler using

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time. For example

CREATE EVENT recycling ON SCHEDULE EVERY 1 HOUR ENABLE 
  DO 
  DELETE FROM MyTable WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 24 HOUR;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW 
  INSERT INTO LogTable (MainID, Created) VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table that was created before specific time.

delimiter |
CREATE EVENT cleaner ON SCHEDULE EVERY 1 HOUR ENABLE 
  DO 
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 24 HOUR;
    DELETE FROM MainTable 
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable WHERE LogTable.Created < MaxTime;
  END |
  delimiter ;

@wawanD, is it possible to get feedback?.. Did the proposed answer solve your question? — Alexander, Dec 22 '17 at 14:02

score -1 · Answer 2 · answered May 30 '17 at 05:27

-1

In your table "portofolio" add column created_at(datetime). Then in Cron Job, check the current datetime exceeds created_at(datetime) with 24hours and delete the records by mysql query like

DELETE FROM portofolio WHERE created_at<=DATE_SUB(NOW(), INTERVAL 1 DAY)

And run the cron job file every minute

answered May 30 '17 at 05:27

Gopalakrishnan

957
8
19

Since mysql is running anyway and the operation shall be performed inside mysql i would use mysql so to not rely on another service (cron). Why would you recommend to use [cron](https://stackoverflow.com/questions/21196613/)? – surfmuggle Jul 02 '21 at 12:35

How do I delete rows of data from mysql table automatically with 24 hours after data into table?

2 Answers2

Linked