Submitting a date to Database from datepicker

[popcop]

im using the jquery UI datepicker to submit a date to my database but i keep storing 0000-00-00 instead of the date

 

anyone know why this would be happening?

[PFMaBiSmAd]

Did you configure it to either use a primary dateFormat with a mysql date format or to use the altField with an altFormat with a msyql date format?

[popcop]

ive not done anything really i just create the row for date am trying to send it from the form

 

ive added print_r($_POST);  to my page to see whats actually being send over and this is the resuts

 

Array ( [firstname] => tom [lastname] => mckay => tom@gmail.com [date] => 07/20/2011 ) Database updated with:tommckaytom@gmail.com

 

do i need to change anything in the actually DB??  or from the form??

 

Im actually wanting the date to be sent in this format for example  Saturday 30th July 2011  so that i can pull if from the database and display it on the page in that format

[PFMaBiSmAd]

The mysql DATE type exists for a few reasons. The format used is YYYY-MM-DD (or YYYYMMDD when treated as a number) because that format permits dates to be sorted and compared using greater-than/less-than comparisons. The DATE type also has the minimum storage requirements and it allows you to use a few dozen built-in date functions to operate on dates directly in your queries.

 

You need to get any submitted date into the YYYY-MM-DD format when you put it into a query. In your case, you can get the datepicker to do this for you. You should always validate all submitted data to insure that it is what you expect, in case someone is feeding your script unexpected data in an attempt to trigger errors or to break into your script or database.

 

You can format a DATE type any way you want when you retrieve it by using the mysql DATE_FORMAT() function in your query.

[popcop]

could you maybe show me how i could do this as im kinda new to php

[PFMaBiSmAd]

Several different points have been discussed in this thread. Exactly which one did you get stuck on when you tried?

[popcop]

should my field type be DATE in the database?

 

im gettina  little confused.

 

this is the return of whats being send to the DB from the form

Array ( [firstname] => hhh [lastname] => lll => gerrymckay@live.com [date] => 08/25/2011 ) Database updated with:hhhlllgerrymckay@live.com

 

from what youve said, i need to change the format to YYYYMMDD before the database will accept it.

 

im not 100% sure where to change this

[ngreenwood6]

When you post the page like you said your date is coming back as 07/20/2011, it needs to come back as 2011-07-20 but that does not look nice for the user. So thankfully we are using php and there are built in date functions. You can do the following:

 

//get the correct format
$new_date = date('Y-m-d',strtotime($_POST['date']));

//then you can insert that date
$insert = 'INSERT INTO some_table (field1) VALUES("'.mysql_real_escape_string($new_date).'")';

 

Just as a note in case you dont know the strtotime function will take a date and return you a timestamp. Using the date function the first argument is format the date should be in and the second argument is a timestamp that you want formatted to the first argument. Hopefully this helps.

[popcop]

excellent, that worked

 

thank you

[webcode]

When you post the page like you said your date is coming back as 07/20/2011, it needs to come back as 2011-07-20 but that does not look nice for the user. So thankfully we are using php and there are built in date functions. You can do the following:

 

//get the correct format
$new_date = date('Y-m-d',strtotime($_POST['date']));

//then you can insert that date
$insert = 'INSERT INTO some_table (field1) VALUES("'.mysql_real_escape_string($new_date).'")';

Just as a note in case you dont know the strtotime function will take a date and return you a timestamp. Using the date function the first argument is format the date should be in and the second argument is a timestamp that you want formatted to the first argument. Hopefully this helps.

 

 

What do u mean with INSERT INTO some_table (field1) ?

 

//get the correct format

$new_date = date('Y-m-d',strtotime($_POST['date']));

 

//then you can insert that date

$insert = 'INSERT INTO some_table (field1) VALUES("'.mysql_real_escape_string($new_date).'")';

 

Can you give explain what i have to type in the "some_table(field) ?

 

Where does the date come from ($_POST['date'] ?

$new_date = date('Y-m-d',strtotime($_POST['date']));