Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
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 Jun 22 '12 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 Jun 22 '12 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 Jun 22 '12 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 Jun 22 '12 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 Jun 26 '12 at 20:08

1 Answer

up vote 0 down vote accepted

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++;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.