inserting date into mySQL datetime column

inserting date using NOW() into mySQL datetime column

[amdorsey]

I have a form that inserts some records into a table. I have a hidden field called date_opened that inserts the value...

<input name="date_opened" type="hidden" value="NOW()">

...into a mySQL datetime field.

Except after inserting when I view the DB it just shows the standard format not the actual date stamp. (0000-00-00 00:00:00 )

Any thoughts?

[ergophobe]

What do you mean by "standard" format?

I wrote out the following and then reread your message and think I've misunderstood what you're asking. For what it's worth, here it is anyway.

The mysql date column does not store data in the same form as a unix timestamp. You need to do some sort of conversion or just store the timestamp as an integer.

There are lots of ways to do it.

- Store as MySQL date and convert using PHP strtotime()

- Store as MySQL date and convert in your query using MySQL's unix_timestamp();

- Store unix timestamp as an int(10) and convert to human readable date using PHP's date() function.

Tom

[amdorsey]

Then what is the purpose of the NOW() mySQL function?

[ergophobe]

It creates a mysql timestamp. If I understand you right, the problem is that you want a unix-style timestamp, which is not how mysql stores date information.

[amdorsey]

Maybe. I assumed the format for the mySQL DateTime field (0000-00-00 00:00:00) coincided with the mySQL NOW() function.

Now that we got that out of the way. haha.

What piece of php code can I write to insert the dateTime into my table in the format 0000-00-00 00:00:00?

Would I just do...
value="<?php echo date("l, F d, Y h:i a",time());?>

later I want to be able to filter results based on a time frame.

[ergophobe]

I assumed the format for the mySQL DateTime field (0000-00-00 00:00:00) coincided with the mySQL NOW() function.

It does. I guess I don't understand your problem...

How are you looking at the dates in your MySQL db. You say they appear in "standard" format, without saying what that is. That's partly dependent on the client. If you look at them using the mysql interface, phpMyAdmin, MySQL-Front, the dates may be presented in somewhat different formats, but they are stored the same way.

value="NOW()">

Just to be clear. This is a string and does not return a timestamp. I'm assuming you are using it as a string in order to build a query.

[nostra]

solution:
without quotes
value=now()

try it =)

[ergophobe]

solution:
without quotes
value=now()

try it =)

Sorry Nostra. That's not going to do it. There is no now() function in php and certainly not in HTML. He is getting a date, just not in the format he wants. He is trying to use this in an <input> field

<input name="date_opened" type="hidden" value="NOW()">

So presumably, when the form gets submitted, he has something like

$query = "UPDATE table1 SET date_opened={$_POST['date_opened']}";

which resolves to

$query = "UPDATE table1 SET date_opened=NOW()";

What I haven't really understood is what his dates look like and what he wants them to look like since "standard" and "timestamp" can mean lots of things (or the same thing) depending on context.

now() should put a standard mysql timestamp in a datetime column.

[amdorsey]

OK. I think I'm confusing everybody way too much. I just need to be able to input the date in the datetime field, in the appropriate row.

I am inserting the date along with a few other values in the form, so I can't use $query = "UPDATE table1 SET date_opened={$_POST['date_opened']}"; because the form I have is inserting more than just the date. So I figured I had to pass the datetime field in mySQL somekind of value...thus my hidden field. But I understand now that the NOW() function can only be used when in a mySQL string.

So I guess I still have the smae question I'll just ask it a little more clear...

I have a form with 3 text fields (3 values). When the user submits my form I need to have a datetime stamp, so I can record when the user submits the support ticket (form).

[ergophobe]

Ahh! Easy.

$query = "INSERT INTO table1 (field1, field2, field3, time_submitted) VALUES ('{$_POST['field1']}', '{$_POST['field2']}', '{$_POST['field2']}', now())";

Just in case you don't recognize the syntax, the {} just make it so that variable substitution takes place properly. You could also concatenate as in

$query = "INSERT INTO table1 (field1, field2, field3, time_submitted) VALUES ('" . $_POST['field1']. "', '". $_POST['field2']. "', '." $_POST['field2']. "', now())";

In any case, the key thing is that there is no reason to put the current time in any form into a field in the form. Let MySQL do this.

Tom

[amdorsey]

Thanks buddy! Your a life saver!