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
    713
    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 10:13 AM.

  2. #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 :-)

  3. Users who have thanked XterM for this post:

    newphpcoder (11-30-2011)

  4. #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    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

  5. #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    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

  6. #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?

  7. Users who have thanked BluePanther for this post:

    newphpcoder (11-30-2011)

  8. #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    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
  •