How do I make a rollback or "undo" button in my application?

Question

Is there any way to recover deleted data from Database? I’m making an application where I have the buttons to add, edit and delete and now I’m thinking about doing another to undo a delete. How can I do that? I believe there are several ways, could I quote some?

<?php 

require_once '../core/init.php';
include 'includes/head.php';
include 'includes/navigation.php';
//Get Brands database
$sql = "SELECT * FROM frutas ORDER BY frutas";
$results = $db->query($sql);
$errors = array();

//Editar
if(isset($_GET['edit']) && !empty($_GET['edit'])){
    $edit_id = (int)$_GET['edit'];
    $edit_id = sanitize($edit_id);
    $sql2 = "SELECT * FROM frutas WHERE id = '$edit_id'";
    $edit_result = $db->query($sql2);
    $eBrand = mysqli_fetch_assoc($edit_result);

}

//Deletar
if(isset($_GET['delete']) && !empty($_GET['delete'])){
    $delete_id = (int)$_GET['delete'];
    $delete_id = sanitize($delete_id);
    $sql = "DELETE FROM frutas WHERE id = '$delete_id'";
    $db->query($sql);
    header('Location: frutas.php');

}
//Se 'adicionar' for submetido
if(isset($_POST['add_submit'])){
    $frutas = sanitize($_POST['frutas']);
    //Checkar espaços em branco
    if($_POST['frutas'] == ''){
        $errors[] .= 'Adiciona alguma fruta!';
    }
    // Checar se alguma fruta existe no banco de dados
    $sql = "SELECT * FROM frutas WHERE frutas = '$frutas'";
    $result = $db->query($sql);
    $count = mysqli_num_rows($result);
    if($count > 0){
        $errors[] .= $frutas.' Já existe. Adicione outra.';
    }
    //Mostrar erros
    if (!empty($errors)) {
        echo display_errors($errors);
    }else{
        //Adicionar frutas no banco de dados
        $sql = "INSERT INTO frutas (frutas) VALUES ('$frutas')";
        $db->query($sql);
        header('Location: frutas.php');
    }
}

?>

<h2 class="text-center">PHP na feira da Fruta</h2><hr>
<!--Fruta form-->
<div class="text-center">
 <form class="form-inline" action="frutas.php" method="post">
    <div class="form-group">
        <label for="frutas">Adicionar Fruta:</label>
        <input type="text" name="frutas" id="frutas" class="form-control" value="<?=((isset($_POST['frutas']))?$_POST['frutas']:''); ?>">
        <input type="submit" name="add_submit" value="Adicionar" class="btn btn-success">
    </div>
 </form>    
</div><hr>

<table class="table table-bordered table-striped table-auto table-condensed" style="margin: 0 auto; width: auto;">
    <thead>
        <th></th><th>Frutas</th><th></th>
    </thead>
    <tbody>
    <?php while($frutas = mysqli_fetch_assoc($results)): ?>
        <tr>
            <td><a href="frutas.php?edit=<?=$frutas['id']; ?>" class="btn btn-sc btn-default"><span class="glyphicon glyphicon-pencil"></span></a></td>
            <td><?=$frutas['frutas'] ?></td>
            <td><a href="frutas.php?delete=<?=$frutas['id']; ?>" class="btn btn-sc btn-default"><span class="glyphicon glyphicon-remove-sign"></span></a></td>
        </tr>
    <?php endwhile?>
    </tbody>
</table>
</br>
<center><input type="submit" name="add_submit" value="DESFAZER DELETE" class="btn btn-success"></center>
<?php include 'includes/footer.php'; ?>

---

Answer 1

The DELETE cannot be undone directly unless it is in a TRANSACTION, in this case could use the ROLLBACK (using the mysqli_rollback($conexao));

However that’s not what you want, you want to delete and then the user can restore the data in a new request.

For this the best to do, or easier, is to use UPDATE, create a column of Deletado with the pattern of 0 and then use it to identify whether or not an item is deleted.

mysqli_query($conexao, 'UPDATE SET Deletado = 1 WHERE id = 1');

Then to "recover" use:

mysqli_query($conexao, 'UPDATE SET Deletado = 0 WHERE id = 1');

This way the item will be "recovered" since your application will always use the Deletado as a basis. It should therefore use a SELECT * FROM tabela WHERE Deletado = 0 to know that the item is not deleted.

You can also create a DataDeletado to know the date the item was deleted, so you can create a "cronjob" (or Scheduled Events) to actually delete (running DELETE) what is old, so you can allow the user to recover a data within 10 minutes and after that the information will actually be deleted.

Another option is to create another database/table and move the "deleted" data there, temporarily or permanently, then to "recover" copy to the source location.