MYSQL Date Entry shows 0000-00-00

[macleodjb]

I am new to MYSQL and i have a form with an input box that i want the user to type in the date. Everything works fine and the form submits. When i open the database and query the results, the date field shows only 0000-00-00. It doesn't show the date entered (ex 05/23/07). What am i doing wrong?

I'm thinking it has to do something with converting the string to a date? If so, how do i do that in PHP.

[guelphdad]

have pulldown menus for the user to add the date. then take those three variables and concat them to a string in the form of yyyymmdd to insert into the mysql table and make sure you are using DATE as the field type.

if a user enters this in the form:

04/05/08

how do you differentiate it from April 5th and May 4th for example?

[maghiel]

I am new to MYSQL and i have a form with an input box that i want the user to type in the date. Everything works fine and the form submits. When i open the database and query the results, the date field shows only 0000-00-00. It doesn't show the date entered (ex 05/23/07). What am i doing wrong?

I'm thinking it has to do something with converting the string to a date? If so, how do i do that in PHP.

thre result says it all: 0000-00-00. so the date above should be entered as 2007-05-23. you can ofcourse do some checks to convert dates to the proper format but:

have pulldown menus for the user to add the date. then take those three variables and concat them to a string in the form of yyyymmdd to insert into the mysql table and make sure you are using DATE as the field type.

if a user enters this in the form:

04/05/08

how do you differentiate it from April 5th and May 4th for example?

is probably a better idea. like us dutch would enter 04/05/08 as 04-05-08(well, ddmmyy anyway ;))

[guelphdad]

if you allow users to enter their dates what is to prevent them from entering 13 as the month or 33 as the day? or something else, not a date at all? that's why I prefer pull-downs.

[macleodjb]

Still need help. I tried letting php set the date with this "date(Y-n-j)", but i'm still getting 0000-00-00 in my database field. What do i gotta do get this thing to work? When i use a drop down box and concat the strings it works fine, but in this instance i want this to be a timestamp. Doesn't want to work.

[Fumigator]

When you echo out the value you are inserting into your table, what exactly do you see?

[guelphdad]

if you want it to be a timestamp then why are you not using a timestamp data type in your database? then you do not even have to insert the value, it is inserted automatically and also updated automatically should you go back and edit anything in that row.

[macleodjb]

Here is what i am using..


//This is from previous page
echo '<input type="hidden" name="rficreatedate" value="' . date(Y-n-j) . '" />';
//This is on my action page
$rficreatedate = $_GET['rficreatedate'];

$query2 = "INSERT INTO `rfi` (`rfiid`, `JobNumber`, `ItemNumber`, `Description`, `CreateDate`, `CompletionDate`, `Frequency`) VALUES ('', '$rfijobnumber', '$rfiitemnumber', '$rfidescription', '$rficreatedate', '', '$rfifrequency')";



When i go to the database to check that it was inserted properly or call the field out it returns/shows 0000-00-00

[macleodjb]

in response to the timestamp. It's not a date that i want to change. I don't want the date to update itself whenever the it's been queried. It's a creation date.

[Fumigator]

When you echo out the value you are inserting into your table, what exactly do you see?

I'm trying to get you to echo out your $rficreatedate variable so you can see what the problem is for yourself. I promise it will work. Follow my logic: You are asking "why is the field in the table 0000-00-00?" but you should be asking "Why is the value of $rficreatedate empty or not what I'm expecting it to be?" But in order to ask that question you need to know what the value of $rficreatedate is.

p.s. The proper use of date() function is date(string, [int timestamp]). You need to enclose your format in quotes: date('Y-n-j')

[macleodjb]

Thanks fumigator....thats all it was. I needed to add the quotes. thanks alot.

[guelphdad]

in response to the timestamp. It's not a date that i want to change. I don't want the date to update itself whenever the it's been queried. It's a creation date.
so then make it a DATETIME data type and use now() in your mysql insert to insert the values into it. no need to pound a square peg into a round hole.

and a TIMESTAMP does not change when the row is selected, only inserted or updated.