Finding affected fields in mysql Update Query

I have got a complexed question here let say i have 5 fields in a table and one of my user updated only 2 with the help of a form that开发者_运维百科 runs mysql update query.

So is there any way to find out which two fields have been changed

Well, I'm not sure if this is the best way but it will work. Note that the syntax is for tsql(SYBASE) but you get the idea.

1) Before updating the record: Use WHERE clause same as your UPDATE query. Note that the 'new value' is what you are going to update, if a field is not being updated then just send the old value. This will tell you the fileds that will be udpated by the UPDATE query.

SELECT CASE WHEN field1 <> 'new value' THEN 'field 1' ELSE '' END,
CASE WHEN field2 <> 'new value' THEN 'field 2' ELSE '' END,
CASE WHEN field3 <> 'new value' THEN 'field 3' ELSE '' END,
CASE WHEN field4 <> 'new value' THEN 'field 4' ELSE '' END,
CASE WHEN field5 <> 'new value' THEN 'field 5' ELSE '' END
FROM table_name t
WHERE t.field1 = "old value"

2) After updating the record: Use WHERE clause same as your UPDATE query. Note that the 'old value' is what was present before the update. if the key fo the record can be changed by an UPDATE, you might have to update the WHERE clause accordingly. This will give you the fields that have been changed by the UPDATE.

SELECT CASE WHEN field1 <> 'old value' THEN 'field 1' ELSE '' END,
CASE WHEN field2 <> 'old value' THEN 'field 2' ELSE '' END,
CASE WHEN field3 <> 'old value' THEN 'field 3' ELSE '' END,
CASE WHEN field4 <> 'old value' THEN 'field 4' ELSE '' END,
CASE WHEN field5 <> 'old value' THEN 'field 5' ELSE '' END
FROM table_name t
WHERE t.field1 = "old value"

You could do it a couple of ways. Probably the absolute easiest would be to have hidden fields on the form with the original values. Then on the submit, you could compare the new value with the original value.

I'm a beginner myself, but pulling bits from here (literally) and there, I've come up with something that works for me.

It's PHP/Database employee directory. When I want to edit an existing employee, I'm using a form that is pre-populated with the existing db information.

When the form is submitted, and before running the UPDATE query, I wanted to get the names of the fields that had changed so I could add it to the edit log.

Again, I need to reiterate, I'm a beginner. There is undoubtedly a shorter/simpler way to accomplish this. You've been warned :)

//Create array to use later in identifying the changed fields and set each to 0
$update = array('Firstname' => 0,
                'Lastname' => 0,
                'Extension' => 0);

//Set incoming POST data to variables
if (isset($_POST['e_fn'], $_POST['e_ln'], $_POST['e_ext'])) {
    $e_fn = $_POST['e_fn'];
    $e_ln = $_POST['e_ln'];
    $e_ext = $_POST['e_ext'];
}

//grab existing employee data with existing function
$thisEmp = $employee->fetch_emp($e_id);

//set old data to $old_*
    $old_fn = $thisEmp['firstname'];
    $old_ln = $thisEmp['lastname'];
    $old_ext = $thisEmp['extension'];

//compare $old_* to each field sent in POST and flip switch for changed fields
    if ($old_fn !== $e_fn) {$update['Firstname'] = 1;}
    if ($old_ls !== $e_ls) {$update['Lastname'] = 1;}
    if ($old_ext !== $e_ext) {$update['Extension'] = 1;}

//get the key value for each element with the switch == 1
    foreach ($update as $key => $value) {
        if ($value == 1) {
            $wasaffected .= $key . ', ';
        }
    }

Now you've got the names of which fields were changed Ok, well, it's not actually the names of the fields, but rather the names of the Keys, but you can name them the same if you want.

echo $wasaffected; //--> Firstname, Extension,

To handle lots of columns, try this - it saves the old record in an array, compares that to the record after the update and saves the changes in a new array:

$sql = 'SELECT * FROM `table0` where `id`='10'; // get existing record's data
$result = $conn->query($sql);
$old=[];changes=[];
$old = mysqli_fetch_assoc($result));

$sql0 = "UPDATE `table0` SET `type` = 'book', `title`='foo fighters' WHERE `id`='10'"; // save
$result = $conn->query($sql0);

$result = $conn->query($sql); // get record again
$new=[];
$new = mysqli_fetch_assoc( $result)){
foreach($new as $k=>$v){
    if($old[$k]!==$v){
        $changes[$k]['old']=$old[$k];
        $changes[$k]['new']=$v;
    }
}
print_r($changes); // display results