PHP MySQL - Check if a specifc column was updated

Question

I am trying to see the best approach for this scenario - i want to send an email alert whenever a user updates a specific column. The column name is rep. If the rep column isnt updated, do not send an email.

Here's my attempt:

    <?php

    include_once("connection.php");

    if(isset($_POST['update'])) {   
        $id = mysqli_real_escape_string($mysqli, $_POST['record_update']);
        $record_update = mysqli_real_escape_string($mysqli, $_POST['record_update']);
        $comment = mysqli_real_escape_string($mysqli, $_POST['comment']);
        $status = mysqli_real_escape_string($mysqli, $_POST['status']);
        $rt = mysqli_real_escape_string($mysqli, $_POST['rt']);
        $reason = mysqli_real_escape_string($mysqli, $_POST['reason']);
        $username = mysqli_real_escape_string($mysqli, $_POST['username']);
        $rep = mysqli_real_escape_string($mysqli, $_POST['rep']);


       if(empty($record_update)  ) {

        if(empty($record_update)) {
            echo "<script type='text/javascript'>alert('Date/Time field is blank.');window.location.href='dashboard.php';</script>";
        }


} else { 

      $result = mysqli_query($mysqli, "UPDATE employee SET record_update='$record_update', comment='$comment', status='$status', rt='$rt', reason='$reason', username='$username', rep='$rep'  WHERE id='$id'");

      if($rep->(success() == true)) {

         //do email
      } 




    }
 ?>

so would it look like this?

    <?php

    include_once("connection.php");

    if(isset($_POST['update'])) {   
        $id = mysqli_real_escape_string($mysqli, $_POST['record_update']);
        $record_update = mysqli_real_escape_string($mysqli, $_POST['record_update']);
        $comment = mysqli_real_escape_string($mysqli, $_POST['comment']);
        $status = mysqli_real_escape_string($mysqli, $_POST['status']);
        $rt = mysqli_real_escape_string($mysqli, $_POST['rt']);
        $reason = mysqli_real_escape_string($mysqli, $_POST['reason']);
        $username = mysqli_real_escape_string($mysqli, $_POST['username']);
        $rep = mysqli_real_escape_string($mysqli, $_POST['rep']);


       if(empty($record_update)  ) {

        if(empty($record_update)) {
            echo "<script type='text/javascript'>alert('Date/Time field is blank.');window.location.href='dashboard.php';</script>";
        }


} else { 


    $query = mysqli_query($mysqli, "SELECT rep FROM employee WHERE id='$id'");
    $row = $query->fetch_assoc()[0];
    if($row['rep'] != $_POST['rep']) {
        //do nothing
    } else {
       //do email
    }



      $result = mysqli_query($mysqli, "UPDATE employee SET record_update='$record_update', comment='$comment', status='$status', rt='$rt', reason='$reason', username='$username', rep='$rep'  WHERE id='$id'");






    }

?>

If I remember correctly, MySQL has what are called "Triggers" in it. You can set a trigger to automatically do something (like update a record/column or even send alerts. I am now retired so I'm a bit out of date with current trends but I think you just need a column called something like "updated" with a true/false setting. Set it to false and then have the trigger set it to true. You just scan for the true flag. But I'm sure someone else may have a better way to do it. :-) — Mark Manning
– Mark Manning, Commented Apr 26, 2017 at 5:27

N. Hamelink · Accepted Answer · 2017-04-26 05:30:55Z

2

Select the current value, and compare it to the inserted value, if it's different it needs to be updated?

$query = mysqli_query($mysqli, "SELECT rep FROM employee WHERE id='$id'");
$row = $query->fetch_assoc()[0];
if($row['rep'] != $_POST['rep'])
  $record_update = true;

answered Apr 26, 2017 at 5:30

N. Hamelink

7285 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S4NDM4N · Accepted Answer · 2017-04-26 05:50:06Z

0

This might not be the best answer but I like to suggest that you capture the date and time of the first insert and then the update record them in a table columns and the compare the time or both when an update happens to the same data row.

$query = mysqli_query($mysqli, "SELECT time, date FROM employee WHERE id='$id'");
$row = $query->fetch_assoc()[0];
if($row['time'] > $_POST['time'] || $row['date'] > $_POST['date'])
  $record_update = true;

answered Apr 26, 2017 at 5:50

S4NDM4N

9382 gold badges13 silver badges31 bronze badges

2 Comments

Your Bub Over a year ago

that wouldnt work because lets say they update another field, that would trigger an email. There's multiple form fields and i'm only concerned if the rep field is updated.

S4NDM4N Over a year ago

I think this might help you link

Collectives™ on Stack Overflow

PHP MySQL - Check if a specifc column was updated

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related