2 replies to this topic

[newphpcoder]

Good day!

I got a problem in updating my data where the data came from other table.

this is the scenario..

i have 3 tables:

1. other_deductions
2 . deductions
3. test_other_deductions //combination of other_deductions and deductions data

I want to happen is when I press the button generate data all the data from other_deductions and deductions will insert or update if it is exist in test_other_deductions table.

I have code for insert like this:

$result = mysql_query("INSERT INTO test_other_deductions(EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, SSS, TAX, PCHL, HDMF) SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, d.SSS, d.TAX, d.PCHL, d.HDMF FROM other_deductions o, deductions d WHERE o.EMP_NO = d.EMP_NO") 

  or die(mysql_error());

my problem now is on the update statement and the if else statement like if exist update data else insert data

I tried this code but the update did not work..the output is the data was insert again even though it is already exist.

 IF (("SELECT COUNT(*) FROM test_other_deductions") > 0)   {

 $result = mysql_query("update test_other_deductions set (EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, SSS, TAX, PCHL, HDMF) = (SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, d.SSS, d.TAX, d.PCHL, d.HDMF FROM other_deductions o, deductions d WHERE o.EMP_NO = d.EMP_NO)")  or die(mysql_error());  

 } ELSE {

    $result = mysql_query("INSERT INTO test_other_deductions(EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, SSS, TAX, PCHL, HDMF) SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, d.SSS, d.TAX, d.PCHL, d.HDMF FROM other_deductions o, deductions d WHERE o.EMP_NO = d.EMP_NO") 

  or die(mysql_error());

}

Thank you so much

[Vaielab]

Did you know about the mysql function insert ... on duplicate? MySQL :: MySQL 5.0 Reference Manual :: 12.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
This will insert the data, but if the primary key already exist, it will update it.

[newphpcoder]

I tried this code and it works:

$result = mysql_query("INSERT INTO test_other_deductions (EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, SSS, TAX, PCHL, HDMF) SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, d.SSS, d.TAX, d.PCHL, d.HDMF FROM other_deductions o, deductions d WHERE o.EMP_NO = d.EMP_NO ON DUPLICATE KEY UPDATE EMP_NO = o.EMP_NO, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF");  

Thank you so much....