PHP DateTime to MySQL Date

[ShadowFire]

I'm having trouble inserting a formatted PHP DateTime into a MySQL table.

 

I'm getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',2012-01-31)' at line 4

 

I get the error when I try to insert the date as a string from a DateTime object via:

$insertDate = $date->format('Y-m-d');

 

As well as when I try to recast it as a date object:

$insertDate = date("Y-m-d",strtotime($date->format('Y-m-d')));

 

I've also tried putting the date in ' ' 's and escaped " " 's.

 

 

My insert query is:

$insert = "INSERT INTO pendingordersdetails (PartNumber,Oqty,Pqty,Aqty,PartStatus,

PartPrice,idOrderNo,PrtTariffCode,PrtLocation,LineNum,PKqty,SPqty,AllocationDate)

VALUES ('$partNum',$oQty,0,$aQty,'Excess',$price,$orderNum,'$tariffCode',

'$location',$lineNum,$PKqty,$SPqty,'$allocDate');";

 

and the echoed output is:

INSERT INTO pendingordersdetails (PartNumber,Oqty,Pqty,Aqty,PartStatus, PartPrice,idOrderNo,PrtTariffCode,PrtLocation,LineNum,PKqty,SPqty,AllocationDate) VALUES ('0446535240 ',3,0,1,'Excess',163.89,66,' ', 'A0406E01',2,,,'2012-01-31');

 

Anyone have any ideas?

 

[PFMaBiSmAd]

The error is from the series of empty comma's ,,,

 

If you are listing a field in the query, you must supply a value of some kind that is appropriate for the type of field.

[ShadowFire]

Thank you! I actually just caught that before I read your reply, and went ahead and tossed in two zeros for the null values. Perhaps you can help me with what happened after that was fixed:

 

echoed insert:

INSERT INTO pendingordersdetails (PartNumber,Oqty,Pqty,Aqty,PartStatus, PartPrice,idOrderNo,PrtTariffCode,PrtLocation,LineNum,PKqty,SPqty,AllocationDate) VALUES ('0446535240 ',3,0,1,'Excess',163.89,66,' ', 'A0406E01',2,0,0,2012-01-24)

 

error:

Incorrect date value: '1987' for column 'AllocationDate' at row 1

 

This one is really bizarre.. since I can see the date value being inserted, and the date value is definitely not '1987' (though that probably was a pretty good year).

[PFMaBiSmAd]

I'm wondering why you removed the single-quotes from around the literal date value? Without them, you have a math expression: 2012 minus 01 minus 24 = 1987

[ShadowFire]

Haha! Wow that was definitely a miss on my part. So, you're saying I need the quotes.. =)

 

Thank you!