SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update query did not work

    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:
    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. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    If you are saying this code does not work as expected:

    PHP Code:

      $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. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I test my query in mysql and it works...it displayed data with an existing EMP_NO..

    Thank you

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks... I resolved my removing the alias o..

    Thank you

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I have if and else statement for update and insert data to database...
    i would use INSERT ... ON DUPLICATE KEY UPDATE

    one call to the database instead of two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How??? I tried it on my other case of update but it did not work...

    My new code now that work is:
    PHP Code:
     $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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •