Deleting data automatically after certain time

Asked

Viewed 2,815 times

7

Example:

I have a table with three fields: id int pk, varchar name, int category.

The category field can receive 2 values: 1 and 2; By default always when a data is entered it will have a category 1.

Here’s what I want. When a die is entered, it has up to 10 days to be changed to 2. Otherwise, it will be deleted.

Is there any way I can do this?

Grateful!

php mysql

  • You’re the bank manager?

  • @Ricardo Yes, I am.

2 answers

5


Usa create-Event.

Would be more or less thus:

Create the process:

CREATE PROCEDURE delete_rows_links()
    BEGIN
       DELETE FROM minhatabela WHERE categoria = 1 AND data < CURRENT_DATE()-10;
     END

CREATE EVENT myevent
    ON SCHEDULE EVERY 5 SECOND (vc escolhe)
    DO
      CALL delete_rows_links();

The link has more definitions where you can learn more.

  • Ricardo delete_rows_links is a Procedure? I know it’s not the main point of the answer, but if you put in her sample code it would add a lot of value to her answer.

  • I put a template there, but it would have to help in relation to the best name for it, put a date column on the table to control the inclusion date, make your own adjustments, but already gave an improved yes.

3

Record the initial and final date he entered the category and leave the server scheduled to check if it is between the 10-day period, if it did not pass it removes the data.

You can program a crontab script on the server as follows, it will drip your script according to the period you program, and in this script, you do the date check:

Will be executed:

  • Once a year: 0 0 1 1 *.
  • Every month: 0 0 1 * *.
  • Once a week: 0 0 * * 0.
  • Once a day: 0 0 * * *.
  • Every hour: 0 * * * *.

0 0 * * 0 php /path/complete/do/seuscript.php

o seuscript:

<?php

$mysqli = new mysqli("localhost", "root", "senha", "banco");

/* verifica se está conectado */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "DELETE FROM evento
          WHERE status =1
          AND now() NOT BETWEEN data_inicial AND data_final";
$stmt = $mysqli->prepare($query);

And to record the 10-day period:

$mysqli = new mysqli("localhost", "root", "senha", "banco");

/* verifica se está conectado */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$nome = $_POST['nome'];
$dataInicial = new DateTime();
$dataFinal   = new DateTime('+10 days');


$query = "INSERT INTO evento (nome, categoria, status, data_inicial, data_final) VALUES(?,null, 1, ?, ?) ";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("sss", $nome, $dataInicial, $dataFinal);

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.