Logical Exclusion PHP

Question

I’m starting my studies in PHP. I need to make a logical deletion and although I’ve already found enough content on the subject, I can’t find how to exactly make the logical deletion in PHP. This is how I did mine (physics):

function remove( $table = null, $id = null ) {
  $database = open_database();

  try {
    if ($id) {
      $sql = "DELETE FROM " . $table . " WHERE id = " . $id;
      $result = $database->query($sql);
      if ($result = $database->query($sql)) {       
        $_SESSION['message'] = "Registro Removido com Sucesso.";
        $_SESSION['type'] = 'success';
      }
    }
  } catch (Exception $e) { 
    $_SESSION['message'] = $e->GetMessage();
    $_SESSION['type'] = 'danger';
  }

So far I understood that I need to put the date of deletion and just edit instead of delete, but I didn’t really understand how it works?

Answer 1

Logical exclusion is a practice to prevent data loss. Instead of erasing the database record, you signal in a field that that record is deleted.

One thing to consider is that PHP itself does not change the database, it just sends SQL commands in string form for DBMS to execute.

Example

You can create a column "deleted" timestamp. Instead of deleting the record, just update this column with the current timestamp.

See the example in PHP/Mysql:

$sql = "UPDATE $table SET deletado=NOW() WHERE id = $id";

This command will update the 'deleted' field of the record with a timestamp. Mysql’s NOW() function returns a timestamp automatically.

From this, you will select only the records that have the null deleted field. These will be the "active" records. Example:

$sql = "SELECT * FROM $table WHERE deletado IS NULL";

When you want to return the logically deleted records, do a query to select only the records where the deleted field is not null, for example:

$sql = "SELECT * FROM $table WHERE deletado IS NOT NULL";

To "reactivate" a record, update the deleted field to NULL:

$sql = "UPDATE $table SET deletado = NULL WHERE id = $id";