Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    136

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

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    will these tables have multiple records for a given employee? Where are you storing the month/year for this employee's salary?
    Cheers....

    baburajv

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    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

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •