date field help

[tbobker]

Hi i have got a mysql database that is storing information for an online petition. One of the fields is a date field, but it is not recording the date. When i display all the fields to see who has signed the petition, the date field displays 0000-00-00.  How can i activate the date field so it reads the actual date of insert?

[bluedoor]

As a personal preference, and maybe somebody knows a reason against doing it this way, I don't use the data type at all in MySQL.

Instead, I set the column to be of type integer. Then, in PHP, when I update a row, I set the particular field by using the time() function.
The result is that the field holds this big integer value. The advantage is that you can now call this data back into PHP and format it as you want using the PHP date() function.

[tbobker]

Ok thanks for the feed back, yes that does seem like a good way, however how can i get the date field to work?

[obsidian]

[quote author=bluedoor link=topic=113452.msg461063#msg461063 date=1162386771]
As a personal preference, and maybe somebody knows a reason against doing it this way, I don't use the data type at all in MySQL.
[/quote]

You'll save yourself a lot of headache down the road if you'll learn to use the DATE type and run as many of your calculations right in the SQL itself. Since there are so many simple ways to change the DATE type back to a timestamp in PHP anyway, you're much better off using the datatypes for what they are intended.

To get the DATE column to enter the time the petition is signed, you need to use the SQL function NOW(). This function works for any date or time datatypes, and it records the current date/time. For instance:
[code]
INSERT INTO myTable (name, age, date) VALUES ('obsidian', '26', NOW());
[/code]

Hope this helps.

[bluedoor]

[quote author=obsidian link=topic=113452.msg461075#msg461075 date=1162388107]
... you're much better off using the datatypes for what they are intended.
[/quote]

Although I do like my way, you make an excellent point - I'm sure somebody else has thought about how to handle types far more than I have. I'm going back to try it in some old code now...

[fenway]

Yeah, don't be shy to use the datatypes the way there were intended... MySQL can give you a back an integer if you want (and obviously, that's how it's stored interally anyway).