NYCPHP Meetup

[Peter Lehrer]

INSERT INTO table (date) VALUES(now());

Peter Lehrer
----- Original Message -----
From: "Phil Powell" <soazine at erols.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
Sent: Sunday, September 07, 2003 11:57 AM
Subject: Re: [nycphp-talk] mySQL datetime fields default now() fails


> Ok, w/o spending 4.5 hours reinventing the wheel, is there an online
example
> of how to insert the current date/time into a mySQL datetime column field?
> I only have to put it in one place for now but better to learn it now,
> thanx!
>
> 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
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>