Date on form INSERT into mySQL 2

[d0nny]

Hi

I think this should be relatively simple, but I can't work it out.
I have a form which users complete and it has a date field. Currently, I default the value to today's date in the format dd/mm/yy

value="<? print(Date("d/m/y")); ?>"

The user can change this date if they wish and I am looking at adding a Javascript calendar drop-down of some fashion to allows users to pick a date.

My question is this: How do I get that date into my mySQL DB upon a POST?
mySQL only likes the date in the format yyyy-mm-dd so how do I pass this format to the database?

 

[vacunita]

Using any number of functions you can modify the date into something mysql will like before you insert it.

Try using the DATE function or the strtotime function.

http://www.php.net/manual/en/function.date.php

http://www.php.net/manual/en/function.strtotime.php

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[d0nny]

Thanks for the advice.
I tried using the strtotime function in my POST.
So I have a date in my form, say 30/12/2008, and I POST that as 'date'. My processing script should then have:

$date = strtotime($_POST['date']);

Is that right? It doesn't seem to pass a date into my INSERT query.

 

[vacunita]

That's just going to modify it into something useful. You then plug it into your query.

[green]//Build query[/green]
$sql="INSERT INTO mytable VALUES($somefield, [red]$date[/red], $someotherfield";

[green]//execute query[/green]
$res=mysql_query($sql);

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[d0nny]

Yes, that's exactly what I've done, but it doesn't work.
How can I simply take the date in the form of dd/mm/yyyy, POST it as a variable and then load it in my INSERT statement?
I find the only way this works is if I take the date in the form as yyyy-mm-dd and this get's inserted with my data.
If the date is in any other format, the date field in my table remains at 0000-00-00.

 

[vacunita]

How about something like this:

$origdate=$_POST['date'];
$date_parts=explode("/",$origdate);
$newdate=$date_parts[2] . "-" . $date_parts[1] . "-" . $date_parts[0];

This will take the date from the POST variable, which means it comes from your form, and change it to fit your date format. and put it in the $newdate variable. You can then use that to insert it into your DB.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

d0nny, whether the database accepts a value in a field is partly to do with whether you have properly escaped the data and mostly to do with whether the data is in the right form for the database.

for example a mysql date type field will only accept data in the form yyyy-mm-dd (generally).

varchar fields can take pretty much whatever you throw at them. but if you use forward slashes in your data you will need to escape it before use.

 

[d0nny]

OK, that worked a treat.
I note your comments about the right form of data and yes I understand that the DB wants the data like "yyyy-mm-dd".
It's understandable why my data input wouldn't work (as I didn't escape the "/" character), but what I am confused about with this solution is that if I change the default date in my form to yyyy/mm/dd, the data is passed to the DB (without me doing any escaping).

But the solution above worked. Many thanks.

 

[jpadie]

most probably because you are not using a mysql date type, but a string or varchar. and secondly you probably have magic_quotes_gpc or magic_quotes_runtime switched on. both are a bad idea and are removed from php6.

 

[d0nny]

Thanks.
Datatype is definately 'date'.
Not sure about the other parameters. Are these in the php.ini on the server? I'm on shared hosting so is there anyway of amending these locally for my hosting?

 

[jpadie]

you can test the values of all php parameters by querying phpinfo().

i need to correct something said above.

a mysql date type field will only accept data in the form yyyy-mm-dd (generally).

i was not precise enough. it is true that mysql will only accept dates in [yy]yy[-]mm-[-]dd format but [-] can, in fact, be just about any punctuation character, or, in some cases, no character at all.

if you use the forward slash, this will work fine, so long as it is escaped.

this link sheds light on the permitted values