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

 

[jpadie]

a few things to note about your create statement:
* for me the default charset doesnt work. deleting that segment works fine.
* the default for the date_submitted data field cannot work. the timestamp datatype takes a unix timestamp (being the number of seconds after the beginning of the unix epoch). this will not stop the table being created but the mysql engine will ignore the default directive.
*also am not sure why you are mixing date/time datatypes. there are sometimes good reasons (for calculations etc). is there a reason behind your choice?

the code works fine on the table.

try going back to basics again. create a table with only two fields. try the following create:

CREATE TABLE `test` (
  `date_recieved` date NOT NULL default '0000-00-00',
  `date_recieved_2` varchar(50) NOT NULL default '',
) TYPE=MyISAM;

then try the following code to inject some data and then retrieve it

<?
/////////////////
//complete these vars for db access
$host = "";
$username = "";
$password = "";
$database = "";
/////////////

mysql_connect ($host, $username, $password)
	or die (mysql_error());
mysql_select_db($database);
//////////////
$humanreadabledate = "2005-11-23"; //change this around to test different dates
//////////////
$timestamp = strtotime($humanreadabledate);
$sql = "insert into test set `date_recieved` = '$humanreadabledate', `date_recieved_2` ='$timestamp'"; 
mysql_query($sql)
	or die (mysql_error());

//debug output
echo "+++++debug output+++++<br>";
echo "sql string is $sql<br>";
echo "+++++end debug output+++++<br>";

//now retrieve the data

$sql2 = "Select `date_recieved` as hr, `date_recieved_2` as ts from test";
$result = mysql_query($sql2);
echo '<table border = "1">';
echo "<tr><td>Human Readable</td><td>timestamp</td><td>formatted and translated timestamp</td></tr>";
while ($row = mysql_fetch_assoc($result))
{	
	extract($row);
	echo "<tr><td>";
	echo "$hr";	//human readable output from the  database
	echo "</td><td>";
	echo "$ts"; //timestamp output from the database
	echo "</td><td>";
	echo date("l j F Y",$ts); //formats the timestamp into a formal human readable date
	echo "</td></tr>";
}
echo "</table>";
?>

 

[rogerzebra]

jpadie,

thank you. I think you're right to try this in it's own table. I see the essence and the logic in your way to troubleshoot this, execellent. It register the data when I typing the dates as above, but still not the american way. I'll find a solution and let you know how it goes.
thanks again

 

[Bastien]

Roger

I don't understand your last post, particularly:

It register the data when I typing the dates as above, but still not the american way.

What problem are you [still] having?



Bastien

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

 

[rogerzebra]

hehe..
I'm sorry, that it didn't come out that clear.

$humanreadabledate = "2005-11-23"; //change this around to test different dates

My problem all along has beent to convert american date format from a form to register with an insert query in the database. The format above works fine "2005-11-23" it does register in the database. But if I change this dates around like the american way of insert dates from on a form "11-23-2005" it doesn't register in the database.
/rz

 

[Bastien]

The easiest solution might be just to split the date on the hyphens and recombine it

$date = "11-23-2005'; //US date format

$dates = explode("-",$date); //array of values

$eu_date = $dates[2]."-".$dates[0]."_".$dates[1]; //euro date format

//insert as normal

The other option depending on how you get your data from the user is to have three drop downs to form the date(1 each for Month, Day, Year) and then validate and combine into EU date format on the server, before insertion.



Bastien

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

 

[jpadie]

which is what we did earlier up the chain and it worked for roger.

although curiously the us date 11-12-1970exploded to [0]=11,[1]=12,[2]=1970. i suspect this is a typo however.

but, im a bit lost as to what doesn't work now.

 

[rogerzebra]

*SOLVED*
bastien, that last code took off nicely...wow...thank you guys my hat is off. as you mentioned jpadie we did something similar earlier and i think the reason why it didn't work was that i used date as datatype instead of datetime. i need to check that out...next i'll implement it with a java calender but that's another issue.
thanks again and great job
/rz

 

[jpadie]

i'd start off with a javascript calendar, rather than trying to implement a full fledged java applet just for a calendar function.

i use jscalendar personally (but there are loads out there that are good). remember that you will still need to parse the date you get out of the form into a suitable format for insertion into a db.

glad you're fixed now.
Justin