Php mysql if data exist update else insert data from one table to another table

PHP
1

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
  2. 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

2

What is this?

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

I tried this new code:


$result = mysql_query("UPDATE test_other_deductions, other_deductions, deductions SET 
           test_other_deductions.EMP_NO=other_deductions.EMP_NO,
           test_other_deductions.BurialSeparationCont=other_deductions.BurialSeparationCont,
           test_other_deductions.TaxAjt=other_deductions.TaxAjt,
           test_other_deductions.CashAdvance = other_deductions.CashAdvance,
           test_other_deductions.AdvanceShirt=other_deductions.AdvanceShirt,
           test_other_deductions.AdvanceMed = other_deductions.AdvanceMed,
           test_other_deductions.AdvanceOther = other_deductions.AdvanceOther,
           test_other_deductions.SSS = deductions.SSS, 
           test_other_deductions.TAX = deductions.TAX,
           test_other_deductions.PCHL = deductions.PCHL,
           test_other_deductions.HDMF = deductions.HDMF
           WHERE 
           test_other_deductions.EMP_NO=other_deductions.EMP_NO")  or die(mysql_error());          
if (mysql_affected_rows()==0) {  
  $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());
}

but it did not update the data…

4

I also tried this update query:


$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());

and I got an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, Ad’ at line 1

and i also tried this code:


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

and i got this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM other_deductions o, deductions d WHERE o.EMP_NO = t.EMP_NO’ at line 1

I don’t know what syntax should I need to solve my problem in updating table

Thank you so much…

5

i would use INSERT … ON DUPLICATE KEY UPDATE

this is one call to the database instead of two

6

I tried that by using this code:


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 t.EMP_NO = o.EMP_NO, t.BurialSeparationCont = o.BurialSeparationCont, t.TaxAjt = o.TaxAjt, t.CashAdvance = o.CashAdvance, t.AdvanceShirt = o.AdvanceShirt, t.AdvanceMed = o.AdvanceMed, t.AdvanceOther = o.AdvanceOther, t.SSS = d.SSS, t.TAX = d.TAX, t.PCHL = d.PCHL, t.HDMF = d.HDMF FROM other_deductions o, deductions d WHERE o.EMP_NO = t.EMP_NO;

and i got an error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM other_deductions o, deductions d WHERE o.EMP_NO = t.EMP_NO’ at line 1
(0 ms taken)

Thank you so much

7

you are not allowed to use FROM in the ON DUPLICATE KEY UPDATE portion

8

I tried this code:


$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");

When I tried this and i tried to update data the output is instead of updating data it was inserted again…

Thank you so much…