NYCPHP Meetup

[Phil Powell]

I figured it out but it's a PHP solution, not a mySQL solution:

date("Y-m-d H:i:s")

How inferior.  You can employ pre-defined constants in Oracle, SQL Server,
even stinkin' ACCESS, but not mySQL?

Phil
----- Original Message ----- 
From: "Adam Maccabee Trachtenberg" <adam at trachtenberg.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Sunday, September 07, 2003 11:36 AM
Subject: Re: [nycphp-talk] mySQL datetime fields default now() fails


> 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!
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk