How do I delete rows of data from mysql table auto

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

<!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 .

标签： php html mysql database mysqli

2条回答

爷的心禁止访问

2楼-- · 2019-01-28 08:53

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

0人赞添加讨论(0) 举报

倾城　Initia

3楼-- · 2019-01-28 09:08

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 ;

0人赞添加讨论(0) 举报

How do I delete rows of data from mysql table auto

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间