PDA

View Full Version : problem in inserting datetime into database



newphpbees
18 Mar 2012, 07:58 PM
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.




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

jassmee
02 Apr 2012, 12:18 AM
when you insert the date field in database then you may used date time () in sql. when you used insert query then you may used now() function...