Soft Delete In PHPMaker

PHPMaker Tutorial Series

Hi All, welcome to PHPMaker tutorial series !

What is Soft Delete?

Based on metabase, the definition of soft delete is marks a record as no longer active or valid without actually deleting it from the database. We need to implement the soft delete to ensure the data integrity is retained. Here’s the tutorial to implement soft delete in PHPMaker.

Study Case

Say we have a table named KP DKPU as specified below

Picture 1. Table Structure

We will soft delete the second data in the table. We will update the “deleted_at” column with deletion action date and the “updated_by” column with user identity key or nip_baru once the deletion action already performed. Then we will show the data which deleted date is not defined yet.

Existing Condition of The Website

Picture 2. List Data Before Soft Delete

As we can see, there two records displayed on the website. Id_table represent it’s id which will be the key for next step.

Setting The Session Variable

We need to identify who performed this action so we need to stored the key user (nip_baru_user) into session variable and then pass it into database, spesifically into added_by column. So, first things to do is assign user key to session variable as shown below

Picture 3. Add new Session Variable

click the table then click on Code Tab then write these code on Server Event>Global>User_Validated. We named the new session variable with “nip_baru_user” and assign the “nip baru” variable in this session. Array Variable $rs[] in this function contain columns defined in current table.

// User Validated event
function User_Validated(&$rs)
{
    // Example:
    //$_SESSION['UserEmail'] = $rs['Email'];
    if (IsLoggedIn() && !IsSysAdmin()) {
        
        $_SESSION['nip_baru_user'] = $rs['nip_baru'];
    }
}

Setting The Fields

Setting the fields by clicking on Table we want to implement the soft delete method,

Picture 4. Click The Table

then click on field tab and then make sure the deleted_at column and updated_by column already configured like shown below.

Picture 5. Setting The Fields

Make sure user can’t edit the historical columns and those columns does not show in the add page.

Write The Server Event Code

Picture 6. Write The Code

Write the code on Code (Server Event) Tab and then choose Table Spesific > Row_Deleting

function Row_Deleting(&$rs)
{
    // Enter your code here
    // To cancel, set return value to False

    $conn = $this->getConnection();
    $conn->executeUpdate("update <your_table> set deleted_at = getdate(), updated_by = ".$_SESSION['nip_baru_user']." where id_table =".$rs['id_table']);
    $conn->commit();
    $conn->beginTransaction();
    $this->setSuccessMessage(Language()->phrase("DeleteSuccess"));
    $this->terminate("kpdkpulist");
    return false;

    return true;
}

Make Sure the update statement is correct based on our table’s name and our column name. Performed it in SQL editor (like dbeaver or MSSQL) first to ensure the sql code is correct.

Filter The Data

We will show only undeleted data, so write this on FIlter options like shown below

Picture 7. Filter the data

"deleted_at is null"

This code means that only undeleted data which the deleted_at column is null will be display as a valid data. Make sure to use quotation marks.

Result

After we delete Second Data in the table, we will see this on The website

Picture 8. Website Result

Only the first data will be displayed, the second data will not be appear. But if we see in the database, the second data still exists with the deleted_at column contain a date and updated_by column contain a user key.

Picture 9. Database Result

And Done! Soft Delete Feature has already implemented.