NYCPHP Meetup

[Adam Maccabee Trachtenberg]

On Sun, 7 Sep 2003, Phil Powell wrote:

> I have a mySQL database table with a column field datatype of
> datetime that I set up in the original create table statement like
> this:
> 
> create table nnet_usermetadata (
> ...
>  nnet_record_entered datetime default 'now()'
> )
> 
> However, upon execution, each time a record is entered into
> nnet_usermetadata the results are:
> 
> 00-00-0000 00:00:00

I believe this is because you are trying to enter the string 'now()'
into the field instead of the results of the function named
now(). However, I bet you tried it without the quotes first and got a
MySQL error message like this: "You have an error in your SQL syntax
near 'now())'"

> Is there a way I can always ensure that the current date and time
> are always entered into the datetime field?  I would prefer to not
> have a server-side PHP or any other scripting solution because by
> doing so would mean I would have to alter my table to do so, and
> after the barrage of questions you've received from me would you
> trust me to do so effectively?

I went to Google and typed in "default now() mysql" and unless things
have changed in MySQL since 2001 (which is possible), you cannot do
this because default values must be constants. (That's why MySQL spews
that SQL syntax error.)

However, I don't think you'd actually need to alter your table to make
sure you always entered the date -- you'd just need to alter all your
INSERTs and UPDATEs by adding some code to generate the date as part
of your SQL. Of course, that may be worse, but what you need to
do. Alternatively, it looks like MSSQL might support this syntax. :)

-adam

-- 
adam at trachtenberg.com
author of o'reilly's php cookbook
avoid the holiday rush, buy your copy today!