...

View Full Version : Insert data into one table where data came from multiple tables.



newphpcoder
12-05-2011, 10:38 AM
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:



$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:



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

BubikolRamios
12-05-2011, 01:23 PM
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"




What was duplicated, entire rows ?
That can't be, unless you have wrong keys

I would allso reconemt using
JOIN instead of =
the whole thing is then much more clear

jassi.singh
12-05-2011, 02:22 PM
Try using join

Check this link

http://www.webmaster-forums.net/web-database-development/using-php-how-do-you-insert-data-multiple-tables-mysql

Old Pedant
12-05-2011, 11:46 PM
This is totally bogus:


AND s.EMP_NO = u.EMP_NO = o.EMP_NO

That says "compare s.EMP_NO and u.EMP_NO; if they are equal, then o.EMP_NO must be 1; if they aren't equal then o.EMP_NO must be 0."

MySQL does *NOT* use multiple assignment as is done in C/C++/Java!

It performs the comparisons strictly left to right, also.

So it's as if you said (in C/C++/Java)


AND ( s.EMP_NO == u.EMP_NO ) == o.EMP_NO

or maybe more accurately:


AND ( ( s.EMP_NO == u.EMP_NO ) ? 1 : 0 ) == o.EMP_NO

newphpcoder
12-06-2011, 02:08 AM
I resolved it using this 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());


I edit my where clause and i add unique in my EMP_NO

Thank you



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum