Convert Date format from form to MySQL

[rogerzebra]

Hi all,
I've a problem when I'm trying to convert my submitted american date format from a form to my MySQL database (european date time). I've tried to solve this on my own and it doesn't work, so I would greately appreciate if someone could take a look at my code and perhaps give me some guidelines on what's wrong with it. When I'm echo the query it gives me a succesful insert. Everything works fine except that the column for date recieved leaves me with no changes. As you can see I have other columns which contains date formats aswell like date_submitted and effective_date which I'll change also. How do I write that in the same string as date recieved and in a string who works?

$date_recieved = (int)$_POST['Year'].'-'.(int)$_POST['Month'].(int)$_POST['Day'];
$query= mysql_query("INSERT INTO submissions (class_code, date_recieved, date_submitted, producer, 
			name_insured, effective_date, primary_state)
		VALUES ('$class_code', '$date_recieved', '$date_submitted', '$producer', '$name_insured', '$effective_date',
		 '$primary_state')")
		 or die (mysql_error());
		
if(!$query){
	echo 'There has been an error creating your submission.';
} else {
echo "success in database entry.";

Thanks for your time in advance.
/rz

 

[Bastien]

try

$date_recieved = date("Y-m-d",(int)$_POST['Year'].'-'.(int)$_POST['Month'].(int)$_POST['Day'];

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[rogerzebra]

Thanks Bastien,

At least a bit closer, with your addition it register the date 1969-12-31. Any ideas?..of course I didn't put that date in my form.

 

[jpadie]

are the POST vars coming in displaying what you expect them to if you do a print_r($_POST)

 

[rogerzebra]

Hi jpadie,
here's the print result:

Array ( [submissions] => 0 [date_recieved] => 02-09-2005 [date_submitted] => 02-09-2005 [producer] => Test [name_insured] => test1 [effective_date] => 01--01-2005 [primary_state] => California [submit] => submit ) DEBUG: 1 success in database entry.

 

[Bastien]

then you just need
$date_recieved = date("Y-m-d",strtotime($_POST['date_recieved']);

from your orignal post i assumed the date was coming in as distinct elements (ie from three drop downs)

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[rogerzebra]

Bastien,
Ha..wow this is strange, with your new code it register 2008-02-26 and take no regards what I typing in.
??

 

[jpadie]

i think that is because the incoming date is not a GNU valid format.

if you want to use the "-" delimiter for numberic (non-literal) dates the only valid format is year-month-day. see here for more on this topic.

either you should change your input format or you need to parse the incoming variable, split it into three chunks and then reassemble to be a valid date.

 

[rogerzebra]

jpadie,
Yeah, you're right it has to do with the gnu validation. I found this article abit more understandable than your link but I've still don't know how to implement it in my code.

The mySQL time stamp is of the form YYYYMMDD for 8 characters and YYYYMMDDHHMM for 16 charcters. To convert to a UNIX timestamp use the mySQL UNIX_TIMESTAMP() function as in the example below.

	$result=mysql_query ("SELECT UNIX_TIMESTAMP(timestamp_column) as epoch_time FROM table");

	$unix_timestamp = mysql_result ($result, 0, 0);

any ideas?

 

[jpadie]

how do you generate the input variable? do you just type it in or is it generated from a function?

 

[rogerzebra]

jpadie,
Sorry went out for a while. Earlier I just typed it in now I've tried to use this variable

$date_recieved = date('m-d-Y', $unix_timestamp);

together with the part beneth which I supposed you ment with your earlier comment, to use the explode function.

$date_array = explode (" ", $date_recieved);
$unix_timestamp = mktime(0, 0, 0, $date_array[1], $date_array[2], $date_array[0]);

hmm still not there though, it takes it whitout errors.

 

[jpadie]

the way you explained it above the incoming variable has a dash delimiter. thus the explode function would look more like

$date_array = explode("-", $date_recieved);

then i would use strtotime as follows if you want a unix timestamp:

$unix_timestamp=strtotime($date_array[2] ."-" $date_array[0] . "-" . $date_array[1])
//assume that [2] is year. [0] is month and [1] is day

if you want a display date then you could either just rearrange the $date_array series into another string (if you wanted a numeric date) or use the date function as you suggest.

 

[rogerzebra]

okay, it still doesn't register the date, so I think I need you to explain parts of this just to be sure I understand it correctly. I also decided to show you the insert code so it's earier to see what's happening here.

the first part of the code beneth is supposed to tell php that I typing in the dates on my form the american way, right? (months,day and year).

$date_recieved = date('m-d-Y', $unix_timestamp);

..and next part of the code doing the conversion from US dates to MySQL which using the European date system, right? starting with the array[2]=year,[1]=months,[0]=days.

$date_array = explode ("-", $date_recieved);
$unix_timestamp = strtotime($date_array[2] ."-" .$date_array[0] ."-" .$date_array[1]);
 
$query= mysql_query("INSERT INTO submissions (class_code, date_recieved, date_submitted, producer, name_insured, effective_date, primary_state) VALUES ('$class_code', '$date_recieved', '$date_submitted', '$producer', '$name_insured', '$effective_date',
		 '$primary_state')")
		 or die (mysql_error());
if(!$query){
	echo 'There has been an error creating your submission.';
} else {
//if(!mysql_db_query($database_name,$query)) die(mysql_error());
echo "success in database entry.";

$result = mysql_query($query);

what else is missing? $date_array[], gets their values from the explode $date_recieved right? does it need a $result function?
would appreciate if you could explain this part if i have understood this completely wrong. you see, i'm still learning php and i'm very greatful for your help and your patience. what do you think is wrong here?

 

[jpadie]

it all looks ok to me. i have written some quick debug code below to double check. try on your setup too.

<?
//dummy the incoming array
$date_recieved ="11-12-1970" ; //american style date mm-dd-yyyy


$date_array = explode ("-", $date_recieved);	//explode the string into an array

//echo the array for debug
print_r ($date_array);

$unix_timestamp = strtotime($date_array[2] ."-" .$date_array[0] ."-" .$date_array[1]);

echo "<br>";
echo "timestamp = $unix_timestamp which is in human readable form (year-month-date) ". date("Y-m-d",$unix_timestamp);
?>

one thing to check: is the datatype in your mysql table for the date_recieved field "date" (which it should be for this type of insert) or "timestamp" (in which case you need to insert the $unix_timestamp value)? it could also be a datetime datatype which might require you to submit the time as well (not sure about this last one).

 

[rogerzebra]

This is killing me,
as you can see in the print the array[0] is being treated as the day value and not the month value which is should. and the array[1] as the months value when it's should have the value of the date???
Even if I change the value in the array brackets it refuses to take it. what's up with that?

print result:
Array ( [0] => 11 [1] => 23 [2] => 1970 ) 28195200
timestamp = 28195200 which is in human readable form (year-month-date) 1970-11-23 success in database entry.

????

 

[jpadie]

i'm gobsmacked. the explode function is non-locale so i can see no reason why it should function for you different to me.

assuming you are positive that you have identically copied and pasted, any ideas anyone?

 

[Bastien]

from your little code snippet the array is the way you want it

print result:
Array ( [0] => 11 [1] => 23 [2] => 1970 ) 28195200
timestamp = 28195200 which is in human readable form (year-month-date) 1970-11-23 success in database entry.

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[rogerzebra]

Bastien,
Yeah you're right It's prints correctly but it doesn't register my database.

 

[jpadie]

my post of a couple of days ago didn't make it to the forum.
i was basically asking what field type you had set in your database.

if it is a timestamp datatype you will need to insert the $unix_timestamp. to use the date datatype you should insert the formatted human readable date (or the string compiled date from the array - both are identical).

 

[rogerzebra]

jpadie,

Again and I'm very grateful for your patience. I need your help on this i'm really lost. I decided to print the mysql dump for the submission table.

CREATE TABLE `submissions` (
`submission_id` int(25) NOT NULL auto_increment,
`class_code` enum('0','1') NOT NULL default '0',
`date_recieved` date NOT NULL default '0000-00-00',
`date_submitted` timestamp NOT NULL default '0000-00-00 00:00:00',
`producer` varchar(50) NOT NULL default '',
`name_insured` varchar(50) NOT NULL default '',
`effective_date` date NOT NULL default '0000-00-00',
`primary_state` varchar(25) NOT NULL default '',
PRIMARY KEY (`submission_id`),
UNIQUE KEY `name_insured` (`name_insured`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15000608 ;

anything here you want me to change?