[thelist] Re: PHP/MySQL dates, times and autoincrements

[rudy]

> 1) I'm working A LOT with dates and times in PHP/MySQL.
> PHP seems to rely on timestamps, but the MySQL datetime
> type is a string.

hi john

um, no, it's a date datatype

i can't find it in their online docs but i believe the mysql datetime is
physically just a very large integer -- but physically doesn't matter

what it *displays* as, or how it is used in date calculations or
comparisons against a string, that's a horse of a totally different kettle
of fish

> Generally, it seems like I might be best off working with
> timestamps in my scripts, and converting to the MySQL datetime
> format when I store values in the db. Can anyone confirm that I'm
> thinking straight or clue me in to anything I might be missing?

my recommendation is to store only what you need -- DATE, DATETIME, and
TIMESTAMP all have different purposes, and you would use what makes sense

for example, most databases would use DATE, not DATETIME, to store a
person's birthdate -- not much point in having a time, since it ain't gonna
be accurate (how many people can really tell you what time they were born?
and does it matter?)

use DATETIME instead of TIMESTAMP unless you want mysql to insert the
current time for you automatically

> Does MySQL support using a SELECT statement for a value in
> an insert statment? (as in, 'insert into orders (ordCust) values
> (select custID from customers where custID = LAST_INSERT_ID())')?

yes, or mysql_insert_id()  (sorry, not sure what the difference is)

rudy