how can i delete all the records in my mysql table automatically using php?

Question

i am trying to create some code to delete all the records in my mysql table automatically, i have done it in an older projet, but in this case, when i use the exact same script it does not work. i am trying to make it delete after 60 seconds just for testing but i must be doing something wrong.

all the help is much apreciated

my code:

<?php
    function apaga(){

     $dbconn = mysqli_connect("localhost", "root", "", "fct");

     if($dbconn->connect_error){

         die("Falha ao conectar ao servidor: ".$dbconn->connect_erro);

     }

    $queryselect=$dbconn->query("SELECT * FROM mostra1 ")or die(mysqli_error($dbconn));

    while($row=$queryselect->fetch_assoc()){

        $id=$row['id'];

    $sql= "DELETE * FROM mostra1 WHERE `data` < (NOW() - INTERVAL 60 SECONDS)";


    mysqli_query($dbconn,$sql);

     }
    }

    apaga();

    ?>

*i am trying to make it delete after 60 seconds* No, you delete rows which are 60+ second old, and you do it immediately. — Akina, Jun 15 '20 at 13:24
If you really want to delete ALL of the contents of the table the `truncate` SQL command is what you're looking for. — Dave, Jun 15 '20 at 14:20
It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jun 15 '20 at 15:03

score -1 · Answer 1 · answered Jun 15 '20 at 14:12

-1

Your database might be running in safe mode. Try adding the PHP equivalent of this SQL before the delete:

set SQL_safe_updates = 0;

then after the delete:

set SQL_safe_updates = 1;

answered Jun 15 '20 at 14:12

PeterMag

19
2

score -1 · Accepted Answer · answered Jun 15 '20 at 20:53

Try this mate :)

<?php
  
 function apaga(){
     $conexao = mysqli_connect("localhost", "root", "", "fct");

    if($conexao->connect_error){

  die("Falha ao conectar ao servidor: ".$conexao->connect_erro);

 }

    $queryselect=$conexao->query("SELECT * FROM mostra1")or die(mysqli_error($conexao));;

   while($row=$queryselect->fetch_assoc()){

      $id=$row['id'];

      $sql= "DELETE FROM mostra1 WHERE data < (NOW() - INTERVAL 1 MINUTE)";

      mysqli_query($conexao,$sql);

  }


}


apaga();

?>

thank u so much mate u saved my life!!!! i cant thank u enough :) — Ricardo Duarte, Jun 15 '20 at 20:54

how can i delete all the records in my mysql table automatically using php?

2 Answers2