SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem in inserting datetime into database

    Hi,

    I need to add datetime field to my table and I don't know if how can I insert datetime with condition.

    I add this fields:

    chemicalweighing_dateEntry
    compounding_dateEntry
    extrusion_dateEntry
    forming_dateEntry
    deflashing_dateEntry
    kanban_dateEntry
    virtual_dateEntry
    total_dateEntry


    here is the scenario.

    first I need to used query insert which have now that I only need is to add datetime fields.

    I have 5 forms , first I have a form for chemicalweighing so in my query I need to save datetime for chemicalweighing_dateEntry, then after chemicalweighing the second is compounding, i need to update the table and because I am on the second form the chemicalweighing_dateEntry should be 0000-00-00 00:00:00 or null and the dateEntry will be on the compounding_dateEntry because I am on the second form. also in the other forms.

    here is my code now where I need your help to add code condition for dateentry.

    Code:
    $dateEntry = date("Y-m-d H:i:s");   
    
    $sql = "INSERT INTO kanban_data (REFNUM, LOT_CODE, PCODE, wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashing,
               chemicalweighing_dateEntry, compounding_dateEntry, extrusion_dateEntry, forming_dateEntry, deflashing_dateEntry,
               kanban_dateEntry, virtual_dateEntry, total_dateEntry) 
               (SELECT REFNUM, LOT_CODE, SUBSTRING(LOT_CODE, 9,4), ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing,
               ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding,
               ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion,
               ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming,
               ROUND(SUM(deflashing),2) AS wip_deflashing
               FROM kanban WHERE REFNUM = '$currentReference' AND LOT_CODE = '$lotCode' GROUP BY REFNUM)
               ";
    
    
     $sql = "UPDATE kanban_data kd SET wip_chemicalweighing = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
             wip_compounding = (SELECT ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), 
             wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), 
             wip_forming = (SELECT ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM), 
             wip_deflashing = (SELECT ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM)
             ";

  2. #2
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your declaring two different functions under the same variable name. That might be a problem.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    How about
    Code:
    $sql = "INSERT INTO kanban_data (REFNUM, LOT_CODE, PCODE, wip_chemicalweighing, wip_compounding, wip_extrusion, wip_forming, wip_deflashing,
               chemicalweighing_dateEntry, compounding_dateEntry, extrusion_dateEntry, forming_dateEntry, deflashing_dateEntry,
               kanban_dateEntry, virtual_dateEntry, total_dateEntry) 
               (SELECT REFNUM, LOT_CODE, SUBSTRING(LOT_CODE, 9,4), ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing,
               ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding,
               ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion,
               ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming,
               ROUND(SUM(deflashing),2) AS wip_deflashing
      , '$dateEntry'
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
      , NULL
               FROM kanban WHERE REFNUM = '$currentReference' AND LOT_CODE = '$lotCode' GROUP BY REFNUM)
               ";
    This is for chemical weighing.
    You'll have to adjust the part in red for the other forms.


Bookmarks

Posting Permissions

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