Insert data into one table where data came from multiple tables.

newphpcoder · #1 (**permalink**) 12-05-11, 04:44

Hi,

I have tables that i need to insert all data from that table into one table.

here is tables that I need to get the data.

1. totalpay AS t

EMP_NO, Amount, TotalEarnings, TotalDeductions, TakeHomePay

2. other_earnings AS e

EMP_NO, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt

3. deductions AS d

EMP_NO,SSS, TAX, PCHL, HDMF

4. hdmfloan AS h
EMP_NO, HDMFAmor

5. sssloan AS s

EMP_NO, SSSAmor

6. udtloan AS u
EMP_NO, UDTAmor

7. other_deductions AS o
EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther

And here is the table where all data should be inserted

1. generate_payroll

EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay

I used this code:

Code:

$result = mysql_query("INSERT INTO generate_payroll (EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay) SELECT t.EMP_NO, t.Amount, e.OTReg_Amt, e.SunReg_Amt, e.OTSun_Amt, e.HolReg_Amt, e.HolRegOT_Amt, e.HolLeave_Amt, e.NP_Amt, e.Meal_Amt, e.COLA_Amt, t.TotalEarnings, d.SSS, d.TAX, d.PCHL, d.HDMF, h.HDMFAmor, s.SSSAmor, u.UDTAmor, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay FROM totalpay t, other_earnings e, deductions d, hdmfloan h, sssloan s, udtloan u, other_deductions o WHERE t.EMP_NO = e.EMP_NO AND d.EMP_NO = h.EMP_NO AND s.EMP_NO = u.EMP_NO = o.EMP_NO ON DUPLICATE KEY UPDATE EMP_NO = t.EMP_NO, Basic_Pay = t.Amount, OTReg_Amt = e.OTReg_Amt, SunReg_Amt = e.SunReg_Amt, OTSun_Amt = e.OTSun_Amt, HolReg_Amt = e.HolReg_Amt, HolRegOT_Amt = e.HolRegOT_Amt, HolLeave_Amt = e.HolLeave_Amt, NP_Amt = e.NP_Amt, Meal_Amt = e.Meal_Amt, Cola_Amt = e.Cola_Amt, TotalEarnings = t.TotalEarnings, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF, HDMF_Amor = h.HDMFAmor, SSS_Amor = s.SSSAmor, UDT_Amor = u.UDTAmor, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, TotalDeductions = t.TotalDeductions, TakeHomePay = t.TakeHomePay")  or die(mysql_error());

for inserting and updating data, It dit not inserted data, and I think the problem is came from the where clause, honestly, right now i don't have any idea how can I check that all EMP_NO from the seven tables is equal.As you can see i check the EMP_NO in where clause, when I tried that in my where clause i only have this:

Code:

WHERE t.EMP_NO = e.EMP_NO

all data was inserted but is was duplicated a lot..

i hope someone could help me, while I'm waiting for help, i will search also for the solution..

Thank you

baburajv · #2 (**permalink**) 12-05-11, 05:13

will these tables have multiple records for a given employee? Where are you storing the month/year for this employee's salary?

newphpcoder · #3 (**permalink**) 12-05-11, 05:24

Now, I resolved it by this code:

Code:

$result = mysql_query("INSERT INTO generate_payroll (EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay) SELECT t.EMP_NO, t.Amount, e.OTReg_Amt, e.SunReg_Amt, e.OTSun_Amt, e.HolReg_Amt, e.HolRegOT_Amt, e.HolLeave_Amt, e.NP_Amt, e.Meal_Amt, e.COLA_Amt, t.TotalEarnings, d.SSS, d.TAX, d.PCHL, d.HDMF, h.HDMFAmor, s.SSSAmor, u.UDTAmor, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay FROM totalpay t, other_earnings e, deductions d, hdmfloan h, sssloan s, udtloan u, other_deductions o WHERE t.EMP_NO = e.EMP_NO AND t.EMP_NO = d.EMP_NO AND t.EMP_NO = h.EMP_NO AND t.EMP_NO = s.EMP_NO AND t.EMP_NO = u.EMP_NO AND t.EMP_NO = o.EMP_NO ON DUPLICATE KEY UPDATE EMP_NO = t.EMP_NO, Basic_Pay = t.Amount, OTReg_Amt = e.OTReg_Amt, SunReg_Amt = e.SunReg_Amt, OTSun_Amt = e.OTSun_Amt, HolReg_Amt = e.HolReg_Amt, HolRegOT_Amt = e.HolRegOT_Amt, HolLeave_Amt = e.HolLeave_Amt, NP_Amt = e.NP_Amt, Meal_Amt = e.Meal_Amt, Cola_Amt = e.Cola_Amt, TotalEarnings = t.TotalEarnings, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF, HDMF_Amor = h.HDMFAmor, SSS_Amor = s.SSSAmor, UDT_Amor = u.UDTAmor, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, TotalDeductions = t.TotalDeductions, TakeHomePay = t.TakeHomePay")  or die(mysql_error());

the problem is instead of six rows was added, the added rows is 2, i think because ti have only 2 datas in HDMF_Amor, SSS_Amor and UDT_Amor

newphpcoder · #4 (**permalink**) 12-05-11, 05:38

it works in terms of Insert but when i run again the query, it was duplicated...insted of save the updated files.

here is my new code:

Code:

<?php
  $result = mysql_query("INSERT INTO generate_payroll 
  (EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay) 
  SELECT t.EMP_NO, t.Amount, e.OTReg_Amt, e.SunReg_Amt, e.OTSun_Amt, e.HolReg_Amt, e.HolRegOT_Amt, e.HolLeave_Amt, e.NP_Amt, e.Meal_Amt, e.COLA_Amt, t.TotalEarnings, d.SSS, d.TAX, d.PCHL, d.HDMF, l.HDMFLoan, l.SSSLoan, l.UDTLoan, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay 
  FROM totalpay t, other_earnings e, deductions d, loan_deductions l, other_deductions o 
  WHERE t.EMP_NO = e.EMP_NO AND t.EMP_NO = d.EMP_NO AND t.EMP_NO = l.EMP_NO AND t.EMP_NO = o.EMP_NO 
  ON DUPLICATE KEY UPDATE EMP_NO = t.EMP_NO, Basic_Pay = t.Amount, OTReg_Amt = e.OTReg_Amt, SunReg_Amt = e.SunReg_Amt, OTSun_Amt = e.OTSun_Amt, HolReg_Amt = e.HolReg_Amt, HolRegOT_Amt = e.HolRegOT_Amt, HolLeave_Amt = e.HolLeave_Amt, NP_Amt = e.NP_Amt, Meal_Amt = e.Meal_Amt, Cola_Amt = e.Cola_Amt, TotalEarnings = t.TotalEarnings, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF, HDMF_Amor = l.HDMFLoan, SSS_Amor = l.SSSLoan, UDT_Amor = l.UDTLoan, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, TotalDeductions = t.TotalDeductions, TakeHomePay = t.TakeHomePay")  or die(mysql_error());                  
?>

Thank you

r937 · #5 (**permalink**) 12-05-11, 06:48

[QUOTE=newphpcoder;6528233]

Code:

 WHERE t.EMP_NO = e.EMP_NO 
   AND d.EMP_NO = h.EMP_NO 
   AND s.EMP_NO = u.EMP_NO = o.EMP_NO

there's your problem, that's not going to work at all like you think it is

may i give you a bit of advice?

you have multiple threads going on at sitepoint for this same problem, and you are getting confused by them

don't repost the same questions in this (or any other) site and expect to alleviate your confusion -- that's only going to make things worse

newphpcoder · #6 (**permalink**) 12-05-11, 19:04

Right now, it works by this code:

Code:

$result = mysql_query("INSERT INTO generate_payroll (EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay) SELECT t.EMP_NO, t.Amount, e.OTReg_Amt, e.SunReg_Amt, e.OTSun_Amt, e.HolReg_Amt, e.HolRegOT_Amt, e.HolLeave_Amt, e.NP_Amt, e.Meal_Amt, e.COLA_Amt, t.TotalEarnings, d.SSS, d.TAX, d.PCHL, d.HDMF, l.HDMFLoan, l.SSSLoan, l.UDTLoan, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay FROM totalpay t, other_earnings e, deductions d, loan_deductions l, other_deductions o WHERE t.EMP_NO = e.EMP_NO AND t.EMP_NO = d.EMP_NO AND t.EMP_NO = l.EMP_NO AND t.EMP_NO = o.EMP_NO ON DUPLICATE KEY UPDATE EMP_NO = t.EMP_NO, Basic_Pay = t.Amount, OTReg_Amt = e.OTReg_Amt, SunReg_Amt = e.SunReg_Amt, OTSun_Amt = e.OTSun_Amt, HolReg_Amt = e.HolReg_Amt, HolRegOT_Amt = e.HolRegOT_Amt, HolLeave_Amt = e.HolLeave_Amt, NP_Amt = e.NP_Amt, Meal_Amt = e.Meal_Amt, Cola_Amt = e.Cola_Amt, TotalEarnings = t.TotalEarnings, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF, HDMF_Amor = l.HDMFLoan, SSS_Amor = l.SSSLoan, UDT_Amor = l.UDTLoan, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, TotalDeductions = t.TotalDeductions, TakeHomePay = t.TakeHomePay")  or die(mysql_error());

Thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech