php mysql array of dates posted as datetime

Question

I've not found an answer to my question, nor have I seen anyone ask this particular question. I have a php purchase order form where I dynamically add line items via javascript and am able to capture all data for each line item in an array and post the data to a mysql database. The only field I am having issues with is the required by date.

I generally use date(strtotime()) to convert a date such as 01/01/01 to 2001-01-01 for purposes of storing in mysql however I am unable to properly capture this date, reformat to an appropriate datetime string and then post with my other data by iterating through my array.

In my haste I've tried a number of different approaches such as putting the date("Y-m-d",strtotime($Item_Date[$a])) within my INSERT Query along with declaring a variable and assigning date("Y-m-d",strtotime($Item_Date[$a])) with the thought of that value being reassigned with each pass through my foreach loop. Examples of each mentioned attempt are outlined below.

EXAMPLE 1

foreach($Cust_PN as $a => $b) {

$query1 = "INSERT INTO SO_Items (Timestamp,SO_Num,SO_Rev,SO_Line_Item,Cust_PN,Cust_PN_Rev,E3_PN,E3_PN_Rev,Description,
Qty,Sale_Price,UOM,Program,Required_Date) 
SELECT NOW(),'$SO_Num','$SO_Rev','$i','$Cust_PN[$a]','$Cust_PN_Rev[$a]','$PN[$a]','$PN_Rev[$a]','$Description[$a]','$Qty[$a]','$Sale_Price[$a]','$UOM[$a]','$Program[$a]','date("Y-m-d", strtotime($Item_Date[$a]))'" or die ('Error posting data');

mysql_query($query1);
$i++;
}

EXAMPLE 2

foreach($Cust_PN as $a => $b) {

$query1 = "INSERT INTO SO_Items (Timestamp,SO_Num,SO_Rev,SO_Line_Item,Cust_PN,Cust_PN_Rev,E3_PN,E3_PN_Rev,Description,
Qty,Sale_Price,UOM,Program,Required_Date) 
SELECT NOW(),'$SO_Num','$SO_Rev','$i','$Cust_PN[$a]','$Cust_PN_Rev[$a]','$PN[$a]','$PN_Rev[$a]','$Description[$a]','$Qty[$a]','$Sale_Price[$a]','$UOM[$a]','$Program[$a]','$Item_Date[$a]'" or die ('Error posting data');


$Item_Date = date("Y-m-d", strtotime($Item_Date[$a]));
mysql_query($query1);
$i++;
}

Any assistance would be much appreciated.

can you confirm if your $Item_Date[$a] is of the form "01/01/01" ? Also do you see any mysql errors? if so, what are they? — Eswar Rajesh Pinapala
– Eswar Rajesh Pinapala, Commented Jun 22, 2012 at 4:38
no mysql errors. I can enter 05/31/12 and echo 2012-05-31 on screen just fine... but my date posts to mysql as 0000-00-00. If I enter 05/31/2012 I echo 2012-05-31 on screen but post 0000-00-00. If I enter 2012-05-31 I echo 2012-05-31 and post 2012-05-31. I figured there is something getting lost in the conversion of the date in the array. — user1459766
– user1459766, Commented Jun 22, 2012 at 5:29
Just noticed something else as well... on my javascript added rows the entered date of 2012-05-31 echos as 1970-01-01 and posts as 0000-00-00. In fact the echo and post is the same for all formats. It appears as though the array isn't being created properly. Still doesn't address the crux of my question but there may be something more going on. — user1459766
– user1459766, Commented Jun 22, 2012 at 5:36
ah, whats the field type of the column in mysql? DATETIME or DATE?? try changing it to DATE and retry inserting, ignore if its already date! — Eswar Rajesh Pinapala
– Eswar Rajesh Pinapala, Commented Jun 22, 2012 at 5:40
Sorry, I've been out of town the last few days without internet connectivity. The field type for the specific column is already DATE. Anyone else have any thoughts or guidance on where I could go to find a solution? Thank you. — user1459766
– user1459766, Commented Jun 26, 2012 at 20:08

user1459766 · Accepted Answer · 2012-06-26 20:48:59Z

OK Thank you guys for your help. I suspected the data within the array was either not getting processed properly or was being obliterated in the foreach loop. The following tweak made all the difference in the world:

Instead of :

foreach($Cust_PN as $a => $b) {

$query1 = "INSERT INTO SO_Items (Timestamp,SO_Num,SO_Rev,SO_Line_Item,Cust_PN,Cust_PN_Rev,E3_PN,E3_PN_Rev,Description,
Qty,Sale_Price,UOM,Program,Required_Date) 
SELECT NOW(),'$SO_Num','$SO_Rev','$i','$Cust_PN[$a]','$Cust_PN_Rev[$a]','$PN[$a]','$PN_Rev[$a]','$Description[$a]','$Qty[$a]','$Sale_Price[$a]','$UOM[$a]','$Program[$a]','$Item_Date[$a]'" or die ('Error posting data');


$Item_Date = date("Y-m-d", strtotime($Item_Date[$a]));
mysql_query($query1);
$i++;
}

I used:

foreach($Cust_PN as $a => $b) {

$Item_Date[$a] = date("Y-m-d", strtotime($Item_Date[$a]));

$query1 = "INSERT INTO SO_Items (Timestamp,SO_Num,SO_Rev,SO_Line_Item,Cust_PN,Cust_PN_Rev,PN,PN_Rev,Description,
Qty,Sale_Price,UOM,Program,Required_Date) 
SELECT NOW(),'$SO_Num','$SO_Rev','$i','$Cust_PN[$a]','$Cust_PN_Rev[$a]','$PN[$a]','$PN_Rev[$a]','$Description[$a]','$Qty[$a]','$Sale_Price[$a]','$UOM[$a]','$Program[$a]','$Item_Date[$a]'" or die ('Error posting data');


mysql_query($query1);
$i++;
}

Collectives™ on Stack Overflow

php mysql array of dates posted as datetime

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related