Update query did not work

PHP
1

Good day!

I have if and else statement for update and insert data to database…

But only the insert statement was work or satisfied,

here is my code:


<?php
include 'config.php';

$currentEmpID = $_SESSION['empID'];
 
 
$sql = "SELECT EMP_ID, CONCAT(LNAME, ', ' , FNAME, ' ', MI) AS FULLNAME FROM PERSONAL ORDER BY LNAME ASC";
$recPersonalNav = $conn->GetAll($sql);
$smarty->assign('personalAll', $recPersonalNav); 
//$EMP_NO = $_POST['EMP_NO'];
//$EMP_NO = mysql_real_escape_string($EMP_NO);

$sql = "SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.') AS FULLNAME FROM PERSONAL p, EMPLOYMENT em  WHERE p.EMP_ID='$currentEmpID' AND em.EMP_ID = '$currentEmpID'";     
    
$recPersonalHead = $conn->Execute($sql);
$fullName = $recPersonalHead->fields["FULLNAME"];
$empno = $recPersonalHead->fields["EMP_NO"];

$smarty->assign('empid', $currentEmpID);
$smarty->assign('fullname', $fullName);
$smarty->assign('empno', $empno); 

//==============================Other Deductions=
 $BurialSep = $_POST["BurialSep"];
 $TaxAjt = $_POST["TaxAjt"];
 $CashAdvance = $_POST["CashAdvance"];
 $AdvancesShirt = $_POST["AdvancesShirt"];
 $AdvancesMed = $_POST["AdvancesMed"];
 $AdvancesOthers = $_POST["AdvancesOthers"];
 
$sql = "SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther FROM $PAYROLL.other_deductions o WHERE o.EMP_NO = '$empno'";
  $rsOtherDed = $conn2->Execute($sql);
  
  $numrows1 = $rsOtherDed->RecordCount();
  
 // var_dump($numrows1);
  
  if($numrows1 > 0){  
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;

  $updateSQL = $conn2->GetUpdateSQL($rsOtherDed, $saverec); 
  $conn2->Execute($updateSQL); 
   //$conn2->debug = true;
  
  }
  else{
  $sql = "SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther FROM $PAYROLL.other_deductions o, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
  $rsOtherDed = $conn2->Execute($sql);
  
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;

  $insert = $conn2->GetInsertSQL($rsOtherDed, $saverec); 
  $conn2->Execute($insert); 
 }

I tried to var_dump the $numrows1 and it has value 1 because I have data already in my database with the o.EMP_NO, but when I tried to edit the data, it did not change in database…

Thank you so much…

2

If you are saying this code does not work as expected:



  $updateSQL = $conn2->GetUpdateSQL($rsOtherDed, $saverec); 
  $conn2->Execute($updateSQL);

Then you need to debug it, follow the variables as they go into that $conn2 object and echo some of them out onto the screen and check against your expectations OR go to your mysql log file and look carefully at the last instructions mysql was given. Pick up the query, test it.

3

I test my query in mysql and it works…it displayed data with an existing EMP_NO…

Thank you

4

I tried to var_dump the $updateSQL and the result is:

UPDATE payroll.other_deductions SET BurialSeparationCont = 5, TaxAjt = 10, CashAdvance = 10, AdvanceShirt = 10, AdvanceMed = 10, AdvanceOther = 10 WHERE o.EMP_NO = ‘00300395’"

On my testing the BurialSeparationCont i change it from 10 to 5 and as you can see the burial was updated but it did not save the updating data in database, the 10 did not change to 5.

Thank you so much

5

UPDATE payroll.other_deductions SET
BurialSeparationCont = 5,
TaxAjt = 10,
CashAdvance = 10,
AdvanceShirt = 10,
AdvanceMed = 10,
AdvanceOther = 10
WHERE o.EMP_NO = ‘00300395’"

I do not see how that update can work when you do not stipulate what table o is a shortcut for

as usually seen in something like:

UPDATE payroll.other_deductions as o SET

If EMP_NO is a field in payroll.other_deductions then the o. would seem to be redundant.

6

Thanks… I resolved my removing the alias o…

Thank you

7

i would use INSERT … ON DUPLICATE KEY UPDATE

one call to the database instead of two

8

How??? I tried it on my other case of update but it did not work…

My new code now that work is:


 $BurialSep = $_POST["BurialSep"];
 $TaxAjt = $_POST["TaxAjt"];
 $CashAdvance = $_POST["CashAdvance"];
 $AdvancesShirt = $_POST["AdvancesShirt"];
 $AdvancesMed = $_POST["AdvancesMed"];
 $AdvancesOthers = $_POST["AdvancesOthers"];
 
$sql = "SELECT EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther FROM other_deductions WHERE EMP_NO = '$empno'";
  $rsOtherDed = $conn2->Execute($sql);
  
  $numrows1 = $rsOtherDed->RecordCount();

  
  if($numrows1 > 0){  
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;


  $updateSQL = $conn2->GetUpdateSQL($rsOtherDed, $saverec); 
  //echo $updateSQL;
  $conn2->Execute($updateSQL);

  
  }
  else{
  $sql = "SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther FROM $PAYROLL.other_deductions o, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
  $rsOtherDed = $conn2->Execute($sql);
  
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;

  $insert = $conn2->GetInsertSQL($rsOtherDed, $saverec); 
  $conn2->Execute($insert); 
  
 }

Thank you…