如何在数据输入表后24小时自动从mysql表中删除数据行？

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?

例如,我有一个数据输入程序我想在输入此数据1天后自动删除我的数据。我是怎么做到的?有人可以用代码解释一下吗?

Create.php

<!DOCTYPE html>
    <html>
        <head>
            <meta charset="utf-8">
            <title></title>

    <?php

    require_once "db.php";
    require_once "function-add.php";



    if( isset($_POST['submit']) ){

      $name_portofolio      = $_POST['name_portofolio'];
      $info_portofolio        = $_POST['info_portofolio'];
      $picture_portofolio  = $_FILES['picture_portofolio'];

      $tipe_file                                    = $_FILES['picture_portofolio'] ['type'];
        $tmp_file                               = $_FILES['picture_portofolio']['tmp_name'];
        $ukuran_file                        = $_FILES['picture_portofolio'] ['size'];


    if(!empty(trim($name_portofolio)) && !empty(trim($info_portofolio)) )  {

                if(!empty($_FILES['picture_portofolio']['tmp_name']) ){

                        if($tipe_file === "image/jpeg" || $tipe_file === "image/png" || $tipe_file === "image/jpg" ){

                                    if($ukuran_file <= 4000000){

                                        if(create_data($name_portofolio, $info_portofolio, $picture_portofolio)) {
                                              echo "success upload portofolio";
                                            }else{
                                              echo "fail upload portofolio";
                                            }

                                            }else{
                                                echo "max file  1.5 mb";
                                            }


                            }else{
                                echo "only.jpeg, .jpg";
                            }

                }else{
                    echo "try again ";
                }

      }else{
        echo 'try again';
      }
    }
    ?>

    </head>

    <body>


    <form action="" method="post" enctype="multipart/form-data">

    <table>
      <tr>
        <td>name portofolio</td>
        <td>:</td>
        <td><input type="text" name="name_portofolio"></td>
      </tr>

      <tr>
        <td>Info portofolio</td>
        <td>:</td>
        <td><textarea name="info_portofolio"></textarea></td>
      </tr>

      <tr>
        <td>picture portofolio</td>
        <td>:</td>
        <td><input type="file" accept="image/*"  name="picture_portofolio"></td>
      </tr>

      <tr>
        <td></td>
        <td></td>
        <td><button type="submit" name="submit">Save</button></td>
      </tr>
      </table>
    </form>

    </body>
    </html>

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;
}
}

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 .

2 个解决方案

#1

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 utitization 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 ;

#2

-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

在您的表“portofolio”中添加列created_at(datetime)。然后在Cron Job中,用24小时检查当前日期时间超过created_at(datetime)并通过mysql查询删除记录

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

And run the cron job file every minute

并且每分钟运行一次cron作业文件

#1