...

View Full Version : Update query did not work



newphpcoder
11-24-2011, 09:55 AM
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...

XterM
11-25-2011, 01:51 AM
try var_dump($saverec) bro.

newphpcoder
11-30-2011, 02:07 AM
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

newphpcoder
11-30-2011, 02:13 AM
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

BluePanther
11-30-2011, 02:15 AM
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?

newphpcoder
11-30-2011, 02:21 AM
Thank you....

I resolved it my deleting the o.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum