如何在数据进入表后24小时自动从mysql表中删除数据行? [英] How do I delete rows of data from mysql table automatically with 24 hours after data into table?
问题描述
例如，我有一个数据输入程序，我想在输入此数据1天后自动删除我的数据.我该怎么做?
有人可以用代码解释吗?
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
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
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";
}
?>
谢谢.
推荐答案
尝试使用常规事件.首先，请使用
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 ;
这篇关于如何在数据进入表后24小时自动从mysql表中删除数据行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
