PHP/SQL update DB issue

I am having trouble with this code and cant get it to work. I only want to update the fields that have been updated, but what is happening is, someone updates say the approvedby_line_manager field and then the code updates approvedby_line_manager, approvedby_so & approvedby_coo too! it shouldnt do that but need some help?

function commission_approvals_before_update(&$data, $memberInfo, &$args){                           
                                
                                $_SESSION['U_approvedby_line_manager']=sqlValue("select approvedby_line_manager from commission_approvals where refnum='" . $data['refnum']. "'");
                                $_SESSION['U_approvedby_so']=sqlValue("select approvedby_so from commission_approvals where refnum='" . $data['refnum']. "'");;
                                $_SESSION['U_approvedby_coo']=sqlValue("select approvedby_coo from commission_approvals where refnum='" . $data['refnum']. "'");
                                $_SESSION['U_approvedby_ges_finance']=sqlValue("select approvedby_ges_finance from commission_approvals where refnum='" . $data['refnum']. "'");
                                
                                return TRUE;
                }
                                
                                function commission_approvals_after_update($data, $memberInfo, &$args){
                                
                                table_after_change($_SESSION ['dbase'], $_SESSION['tablename'], $memberInfo['username'], $memberInfo['IP'], $data['refnum'], $_SESSION['tableID'], "UPDATE", $eo);
                                                                
if ($_SESSION['U_approvedby_line_manager'] != $data['approvedby_line_manager'])
                                                { 
                                                sql("UPDATE commission_approvals set LineManagerApprovalName='".getLoggedMemberID()."', date_approved_by_line_manager='".date('Y-m-d H:i:s')."' where refnum={$data['refnum']}", $eo);
                                                                }                              
elseif($_SESSION['U_approvedby_line_manager'] == $data['approvedby_line_manager']) 

return;

if ($_SESSION['U_approvedby_so'] != $data['approvedby_so'])
                                                { 
                                                sql("UPDATE commission_approvals set SalesOpsApprovalName='".getLoggedMemberID()."', date_approved_by_so='".date('Y-m-d H:i:s')."' where refnum={$data['refnum']}", $eo);
                                                                }                              
elseif($_SESSION['U_approvedby_so'] == $data['approvedby_so']) 

return;

if ($_SESSION['U_approvedby_ges_finance'] != $data['approvedby_ges_finance'])
                                                { 
                                                sql("UPDATE commission_approvals set ApprovedByGESFinanceName='".getLoggedMemberID()."', date_approved_by_finance='".date('Y-m-d H:i:s')."' where refnum={$data['refnum']}", $eo);
                                                                }                              
elseif($_SESSION['U_approvedby_ges_finance'] == $data['approvedby_ges_finance']) 

return;

                                unset($_SESSION['U_approvedby_line_manager']);
                                unset($_SESSION['U_approvedby_so']);
                                unset($_SESSION['U_approvedby_coo']);
                                unset($_SESSION['U_approvedby_ges_finance']);

                                return TRUE;
                }

Open in new window

bryanscott53Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Format your code. Readbility is the first step solving such puzzles.

Then: MySQL supports stored procedures. Use them!
Handle all the tests in that procedure. Pass the refnum and loggedMemberID as parameters.
Cause this will reduce the database round-trips from the worst case 8 (normal 5) to 1.

Finally: Remove the redundancy first. Instead of

if ($_SESSION['U_approvedby_line_manager'] != $data['approvedby_line_manager']){
    sql("UPDATE commission_approvals set LineManagerApprovalName='".getLoggedMemberID()."', date_approved_by_line_manager='".date('Y-m-d H:i:s')."' where refnum={$data['refnum']}", $eo);
} elseif($_SESSION['U_approvedby_line_manager'] == $data['approvedby_line_manager'])
    return;

Open in new window

write it as

if ($_SESSION['U_approvedby_line_manager'] != $data['approvedby_line_manager']){
    sql("UPDATE commission_approvals set LineManagerApprovalName='".getLoggedMemberID()."', date_approved_by_line_manager='".date('Y-m-d H:i:s')."' where refnum={$data['refnum']}", $eo);
} else {
    return;
}

Open in new window

I also strongly recommend to use always curly brackets.

Then you see, that when a value is written, that there is no return in that branch. Thus the next if is evaluated and eventually executed.

btw, the logic itself seems to be alright. Maybe you need to explain why you only want to update one column, even when all columns differ from the current member id?
bryanscott53Author Commented:
thanks, i tried your solution but it still updates all 3 dates and name fields (with the same info) rather than the 1 field it should.
ste5anSenior DeveloperCommented:
btw, the logic itself seems to be alright. Maybe you need to explain why you only want to update one column, even when all columns differ from the current member id?
I didn't post a solution, I especially asked why should be only one column changed at a time, even when all differ? What is the desired logic bind that. Cause the current logic seems to be ok.
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

bryanscott53Author Commented:
if all 3 differ i would expect all 3 changed, when i test i update 1 field but it changes all 3?
hieloCommented:
On the before_update function you have
  select approvedby_line_manager from ...

but on the after_update function you have
  UPDATE commission_approvals set LineManagerApprovalName=....

That mismatch seems suspicious to me.  Try updating the queries on the before_update function to include the actual name of the table field -ex:
$_SESSION['U_approvedby_line_manager']=sqlValue("select LineManagerApprovalName from commission_approvals where refnum='" . $data['refnum']. "'");
bryanscott53Author Commented:
approvedby_line_manager  is a yes/no/blank drop down, i am checking if this field changes. if it dies then record who changed it so this is why its different
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.