PHP/SQL update DB issue

bryanscott53
bryanscott53 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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?

Author

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 Developer

Commented:
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.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
if all 3 differ i would expect all 3 changed, when i test i update 1 field but it changes all 3?
Expert of the Year 2008
Top Expert 2008

Commented:
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']. "'");

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial