[thelist] MySQL/PHP - copy record?

rudy r937 at interlog.com
Tue Aug 13 15:55:01 CDT 2002


>  Is there an "ALL BUT" or something?

nope, but i like how you think  ;o)

you're right, you'll just have to grit your teeth and list all the columns
except for the date...

... and the auto_increment column

<tip type="sql">
if your INSERT sql statement permits you to omit the name of a column, and
have its value be automatically generated, do so rather than providing the
default value in the INSERT statement
</tip>

some people write mysql INSERT statements supplying NULL for auto_increment
columns, a naughty practice (double entendre pun definitely intended) that
will not work in other databases

the tip applies equally well to any columns defined NOT NULL with DEFAULT

just leave them out of the INSERT statement altogether

sadly, mysql, unlike most databases, won't let you use a function in a
DEFAULT clause, so you must set it yourself if it's a DATETIME data type,
e.g.

  insert into yourtable ( foo, credate, bar)
     values ('foo', now(), 'bar')

however, if you make it a TIMESTAMP...

  "The TIMESTAMP column type provides a type that you can use
   to automatically mark INSERT or UPDATE operations with the
   current date and time. If you have multiple TIMESTAMP columns,
   only the first one is updated automatically."

http://www.mysql.com/documentation/mysql/bychapter/index.html#DATETIME

i've used timestamp as follows --

create table foo
 ( id integer not null auto_increment primary key
 , credate timestamp not null
 , revdate timestamp null
 ...

my initial INSERT statement mentions neither credate nor revdate, so
credate gets set to the current timestamp automatically, while revdate is
set to null (meaning that the row hasn't been updated yet)

subsequent updates always start off with

    set revdate=now()

thus explicitly setting the revised date and time using the database's
timestamp

(other assignments follow, each starting with a comma  ;o) )

in neither case is it necessary to pass in the date and time from the
calling script


helps?


rudy







More information about the thelist mailing list