Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts

    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...
    Last edited by newphpcoder; 11-24-2011 at 09:13 AM.

  • #2
    New Coder
    Join Date
    Jul 2011
    Location
    Kediri - Indonesia
    Posts
    61
    Thanks
    2
    Thanked 19 Times in 19 Posts
    try var_dump($saverec) bro.
    I am sorry my english is very bad. But I am very interest to discusse here :-)

  • Users who have thanked XterM for this post:

    newphpcoder (11-30-2011)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I tired to var_dump the $saverec and the output is:

    array(7) { ["EMP_NO"]=> string(8) "00300395" ["BurialSeparationCont"]=> string(1) "5" ["TaxAjt"]=> string(2) "10" ["CashAdvance"]=> string(2) "10" ["AdvanceShirt"]=> string(2) "10" ["AdvanceMed"]=> string(2) "10" ["AdvanceOther"]=> string(2) "10" }
    Payroll System


    I change burialseparationcont from 10 -5...

    but it did not change in database


    I also tried this:

    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

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I also tried to var_dump the $conn2

    and the output is:

    object(ADODB_mysql)#4 (66) { ["databaseType"]=> string(5) "mysql" ["dataProvider"]=> string(5) "mysql" ["hasInsertID"]=> bool(true) ["hasAffectedRows"]=> bool(true) ["metaTablesSQL"]=> string(11) "SHOW TABLES" ["metaColumnsSQL"]=> string(20) "SHOW COLUMNS FROM %s" ["fmtTimeStamp"]=> string(13) "'Y-m-d H:i:s'" ["hasLimit"]=> bool(true) ["hasMoveFirst"]=> bool(true) ["hasGenID"]=> bool(true) ["upperCase"]=> string(5) "upper" ["isoDates"]=> bool(true) ["sysDate"]=> string(9) "CURDATE()" ["sysTimeStamp"]=> string(5) "NOW()" ["hasTransactions"]=> bool(false) ["forceNewConnect"]=> bool(false) ["poorAffectedRows"]=> bool(true) ["clientFlags"]=> int(0) ["dbxDriver"]=> int(1) ["__db"]=> array(1) { [10]=> string(7) "payroll" } ["_genIDSQL"]=> string(38) "update %s set id=LAST_INSERT_ID(id+1);" ["_genSeqSQL"]=> string(33) "create table %s (id int not null)" ["_genSeq2SQL"]=> string(26) "insert into %s values (%s)" ["_dropSeqSQL"]=> string(13) "drop table %s" ["database"]=> string(7) "payroll" ["host"]=> string(9) "localhost" ["user"]=> string(4) "root" ["password"]=> string(0) "" ["debug"]=> bool(false) ["maxblobsize"]=> int(256000) ["concat_operator"]=> string(1) "+" ["fmtDate"]=> string(7) "'Y-m-d'" ["true"]=> string(1) "1" ["false"]=> string(1) "0" ["replaceQuote"]=> string(2) "\'" ["charSet"]=> bool(false) ["hasTop"]=> bool(false) ["readOnly"]=> bool(false) ["genID"]=> int(0) ["raiseErrorFn"]=> bool(false) ["cacheSecs"]=> int(3600) ["arrayClass"]=> string(18) "ADORecordSet_array" ["noNullStrings"]=> bool(false) ["numCacheHits"]=> int(0) ["numCacheMisses"]=> int(0) ["pageExecuteCountRows"]=> bool(true) ["uniqueSort"]=> bool(false) ["leftOuter"]=> bool(false) ["rightOuter"]=> bool(false) ["ansiOuter"]=> bool(false) ["autoRollback"]=> bool(false) ["fnExecute"]=> bool(false) ["fnCacheExecute"]=> bool(false) ["blobEncodeType"]=> bool(false) ["_oldRaiseFn"]=> bool(false) ["_transOK"]=> NULL ["_connectionID"]=> resource(10) of type (mysql link persistent) ["_errorMsg"]=> string(0) "" ["_queryID"]=> bool(false) ["_isPersistentConnection"]=> bool(true) ["_bindInputArray"]=> bool(false) ["autoCommit"]=> bool(true) ["transOff"]=> int(0) ["transCnt"]=> int(0) ["fetchMode"]=> bool(false) ["databaseName"]=> string(7) "payroll" }


    Honestly, i dit not understand this output...

    i can't configured out why it did not update in database

    Thank you

  • #5
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    UPDATE payroll.other_deductions SET BurialSeparationCont = 5, TaxAjt = 10, CashAdvance = 10, AdvanceShirt = 10, AdvanceMed = 10, AdvanceOther = 10 WHERE o.EMP_NO = '00300395'"
    That doesn't really make sense to me. You're updating the payroll table right? Why is it based on a condition from the o table?

  • Users who have thanked BluePanther for this post:

    newphpcoder (11-30-2011)

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Thank you....

    I resolved it my deleting the o.


  •  

    Posting Permissions

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