current date into MySQL values list

auto insert in such a manner that I can get it into the values list

[Clair]

I can't believe I've spent more time on this than on my whole DB setup/page programming but I have.

I am using an HTML form for adding a record to a db. That's fine.

I want every record to have its date field be the date on which I created the record.

I don't think I should have to type this in myself every time I create a record.

I want it in the form of

11/24/2009

I can't believe this is so obscure or that I'm this dumb!
Help much appreciated. (Using PHP and db date field is of type text, which is just fine by me.

Thanks to someone!
Clair

[rocknbil]

Since you're using PHP, what you need to do is accept the mySQL date and datetime formats and just standardize how you extract them. The internal date formats make sorting and selecting so much easier.

... order by created desc;

The only other way would be to save as a varchar format, and sorting will be weird at best. It will also be slower, as of version 4.0 (? or something) date and datetime fields are now read as numeric values instead of text values, so you get a little more performance.

To extract, you can use date_format() in PHP [us3.php.net] or (better) the mysql date format [dev.mysql.com]:

select date_format('2009-11-24', '%d/%c/%Y');
--> 11/24/2009

Substitute your field name for the literal value:

select date_format(create_date, '%d/%c/%Y');

Getting it into the database is easier. For a date only,

insert into table (created) values (curdate());

for datetime, (yyyy-mm-dd hh:mm:ss)

insert into table (created) values (now());

For input values, only a little wizardry is involved. You set select lists (so users can't munge it up) and do

$mm = $_POST['month'];
$dd = $_POST['day'];
$yyyy = $_POST['year'];
$insert = "$yyyy-$mm-$dd";

if (! preg_match('/^\d{4}\-\d{2}\-\d{2}$/',$insert)) {
die ("Invalid date entered, possible mySQL injection attack");
}

insert into table (dt) values ('$insert');

Compiling it as a string like that makes it easier to validate values and avoid injection.

[Clair]

Hi rock --
What a great reply and it gets filed in the good folder!

However, while I understand the MySQL part -- how in *@#$&@%# do I get the current date in the first place?

In other words, how do you get the 'month'', 'day', and 'year' that you used in the $_POST actionss? Obviously they don't have to come in via a form. I just want to get the sucker into the db field like 11/24/2009 for the purpose of record keeping.

And, this is only a page for me to enter records. No one else; so no worries there.

Thanks again!
Clair

Or, is the really sad news "it can't be done"?

[Demaestro]

You don't need the date in the form the user fills out, just pass all the form info to your function and in the INSERT statement use a date function.

I would use the now() function because it grabs your server time right as the insert statement is being executed.

insert into table (id, date) values ('xytr56', now())

[rocknbil]

how in *@#$&@%# do I get the current date in the first place?

LOL . . . you should use curdate() for date only, and now() for datetime. Datetime is important if it's something that needs to be recorded multiple times a day, such as a message board post, date for something like a membership expiration date.

select curdate();

--> 2009-11-24

select now();

--> 2009-11-24 14:18:01

insert into table (date_time_field,date_only_field) values (now(),curdate());

I just want to get the sucker into the db field like 11/24/2009 for the purpose of record keeping.

I'm telling you, this is a bad idea. You have to carry around a varchar field for it, when a date/datetime field does everything you need. But if you must, you can use day(), month(), year(), or the format functions above or extract [dev.mysql.com] in combination with concat [dev.mysql.com].

select concat(month(curdate()),'/', day(curdate()),'/',year(curdate()));

select concat(extract(month from curdate()),'/', extract(day from curdate()),'/',extract(year from curdate()));

select concat(date_format(curdate(),'%c'),'/',date_format(curdate(),'%d'),'/',date_format(curdate(),'%Y'));

--> 11/24/2009

To insert this in your table,

insert into table (date_display) values concat((month(curdate()),'/',day(curdate()),'/', year(curdate()));

insert into table (date_display) values (extract(year from curdate()), extract(month from curdate()), extract(day from curdate()));

insert into table (date_display) values (concat(date_format(curdate(),'%c'),'/',date_format(curdate(),'%d'),'/',date_format(curdate(),'%Y')));

[abushahin]

Hey the guys have given the answers already but heres one im using, obviously my field in the db is DATE type, so firstly i created a varible in php which holds the date
$date = date("y-m-d");
then when i insert i do insert into table values ($date).
that way the current date on the server gets inserted, now when i retrieve and have all the rows returned i call my date field $returndate = mysql_result($retrieveQuery,$i,"your datetime fieldname");
then to echo in a manner we can understand
itll be
$newdate = date("d M Y", strtotime($date));
that works for me, hope it helps abu

[Clair]

WAHOO and finally --
I learned a lot about MySQL but obviously not as much as I need to do what I wanted to. So, after all of this, here's what I did --

$date = date("m/d/Y");
and
into to VALUES list I put '$date',

and in my db there now sits: 11/25/2009

So, abushahin -- you are the winner. (And this works even though my field is a text field.)

I did spend about 3 more hours on all the SQL stuff in the above posts, but in vain. And, keep in mind, I'm an SQL idiot and I am the only operator on this db and I'll never be searching on date (it's a db of artwork) but I just wanted the date in there for history/curiosity. And the data I imported already has all the dates which I would lose if I changed the field to a date field. (That was one of the things I tried, and then I had to import it all again!)

And, I must admit -- I do like simple.

love to you all!
And, if it weren't 5 a.m., I'd have a beer!
I'm done, finished.

Cheers,
Clair

[topr8]

why didn't you just make the date field in the database a timestamp field, this way it would automatically be set when a new row is inserted, without you having to do anything.

you can extract the date in any format you like (using either sql or by processing the returned value with php)

[Clair]

Hi topr8--
Short answer: I had the original db on my computer and exported it as a .csv file and imported it into MySQL -- so all the dates I already had were strings. And, as I said, when I tried changing the field to a date field in MySQL I lost them all and had to import again.

But, yes, had I been starting with a clean plate, I would have done that!

Cheers,
Clair

[topr8]

ah, ok, i thought with LOAD DATA ... INFILE, you could import dates as strings and convert them into timestamp values - i've not had to do it, i just assumed you could, my mistake.

[Clair]

topr8 ---
Arghhhh - don't take my word for that I'm an MySQL greenhorn. You may well be able to do that -- but, I didn't even try! And I hope to blazes I never have to work with dates again. Ever.
Clair

[abushahin]

@claire, Glad i was able to help! I like simple too! oh yeah by the way changing from m to M makes a difference too try it with all the letters(d-m-y) to (D-M-Y) enjoy abu

[rocknbil]

why didn't you just make the date field in the database a timestamp field, this way it would automatically be set when a new row is inserted, without you having to do anything.

YES! LOL . . . well, Claire, in time, you will see the error of your ways . . . and what I've been trying to say since post #2 . . . the PHP solution (also mentioned in post #2) gets you what you want today, but when you go to actually use mySQL for selecting or sorting by date, you will return to this thread. :-)

[Clair]

Hi -rocknbil
I've been studying this thread and I agree with you -- however, I don't want to lose the strings in my earlier "date" field (which as you know is only a string field).

So, I've added a real DATE field to the table as 'date2' which I want filled in automatically with the date but only the date as in 2009-11-26.

BUT, all I get are zeroes in the field when I add a new record.

The field structure is:
date2 timestamp ATTRIBUTES contains ON UPDATE CURRENT_TIMESTAMP
NULL is NO and what shows up is

0000-00-00 00:00:00

in the query 'date2' is not included in the INSERT or the VALUES.

Can you help?

Thanks!

[Clair]

rock --
I've tried this with and without date2 in the query and in the values list. Neither produces anything but zeroes in the field. (Tried all the combinations I can think of and no date ever appears. I've also tried NOW and CURDATE etc. etc. etc.

Just wanted to add that bit of info.

[Clair]

rock -- MORE

I had CURDATE in single quotes in the VALUES list! ARggg --
So now I have 2009-11-27 00:00:00

in the table data for the new record.
Is there any way to just have
2009-11-27 and lose the time stuff?

Getting there slowly! lol
And thanks again.

[rocknbil]

haha . . . sure. :-) Use a date field type:

create table test datefield date default '0000-00-00';

(some like the default NULL, some like it zeroes, take your pick so you don't have to test against both in programming.)

Which gives you the format yyyy-mm-dd. As you saw, date() and now() are functions, not strings, which is why you do not need to quote them.

Judging by the last few posts, I guess I missed an important point. You want to create a proper date field using the text date data already in your database. Here's how you do that.

sample data:

id¦date_display
1¦11/24/2009
2¦11/25/2009
3¦11/26/2009

alter table test add mysql_datefield (date) not null default '0000-00-00';

Now you have

id¦date_display¦mysql_datefield
1¦11/24/2009¦0000-00-00
2¦11/25/2009¦0000-00-00
3¦11/26/2009¦0000-00-00

First, we make sure we have the select statement right. We will use substr() [dev.mysql.com] to extract the days from mm/dd/yyyy. Test on a literal value:

select substring('11/24/2009',1,2) as month, substring('11/24/2009',4,2) as day, substring('11/24/2009',7) as year;

¦month¦day¦year¦ 
¦11...¦24.¦2009¦ 

Dots are just for some form of formatting here and are not in results

Looks good!

Note that substring parameters are "this string," offset with first character in position 1, and length. So 'string', 2,2 prints 'tr', string 2,5 prints 'tring'.

Note also that this will fail if you have dates like 1/1/2009, make sure you have 01/01/2009, or devise some other way of doing this - there are many, don't have time to explore them all today.

Now do another test, this time against the database field instead of a literal date:

select substring(date_display,1,2) as month, substring(date_display,4,2) as day, substring(date_display,7) as year from test;

¦month¦day¦year¦ 
¦11...¦24.¦2009¦ 
¦11...¦25.¦2009¦ 
¦11...¦26.¦2009¦ 

Now just run an update against the entire database, but be sure to rearrange them in the right order, yyyy-mm-dd (7 on is year, 1,2 is month, 4,2 is day.)

update test set mysql_datefield = concat(substring(date_display,7), '-', substring(date_display,1,2), '-', substring(date_display,4,2));

This takes the current row's date_display field, creates a valid mysql date string, and updates the current row with it, all across the DB, all in one line.

And what do we have?

select * from test;
1¦11/24/2009¦2009-11-24
2¦11/25/2009¦2009-11-25
3¦11/26/2009¦2009-11-26

The beauty, you never even have to display this field but can use it for internal searches (although it would be much easier to drop the text field altogether as described:)

select date_display from test order by mysql_datefield desc;

11/26/2009
11/25/2009
11/24/2009

And I hope to blazes I never have to work with dates again.

Trust me, if you plan on working with databases and programming it will be a frequent flier . . . "go with the flow" - use the tools in place and work your way out to more difficult situations - and it's not all that bad. :-) Your first break-in is the opposite direction, so of course it's more difficult.