Basic help with php Date()

[impact]

Hello,

I am trying to insert date('M-d-Y') into mysql database using PHP. I have set the mysql table as DATE

Here is my code:
$creationDate = date('M-d-Y');

mysql_query("INSERT INTO eventDate (A,B,creationDate,D) VALUES ('','','$creationDate','') ");

The query runs without any error but in the database all I can see is
0000-00-00 and nothing actually gets updated.

In the next step, I want to subtract the current date with the date in the database to calculate how many days has passed since the creation date.

Any help will be appreciated.

Thank you,

[Anyango]

i would suggest save UNIX timestamp in the db and then run date time functions to make it look like a date. it makes life much easier and faster for me.

for that you will need to have a mysql field INT(11) and from php you will send

$ts=time();

then you can easily run date() function to display it, for example

if you pick $yourSavedTs from db then you could do


echo date("F j, Y, g:i a",$yourSavedTs);

[dreamcatcher]

all I can see is 0000-00-00

I`m assuming you have a MySQL 'date' field? The reason why it doesn`t work is the format for the date MUST be Y-m-d. You can then use something like DATE_FORMAT to format the date into any format you like. But for storage, must always be US format.

dc

[rocknbil]

This

all I can see is 0000-00-00 and nothing actually gets updated.

Verifies (as you said) you are using a mysql date format for this field. This

$creationDate = date('M-d-Y');

Tells us you are trying to insert the current date. So don't make it harder than it has to be - skip the extra PHP programming and use the internal mysql curdate() function. :-)

$query = "INSERT INTO eventDate (A,B,creationDate,D) VALUES ('','',curdate(),'')";
mysql_query($query);

[AlexK]

dreamcatcher:

MUST be Y-m-d ... must always be US format

To be totally anal, `Y-m-d' is Japanese-format dates.

rocknbil:

use the internal mysql curdate() function

Another alternative is the `CURRENT_DATE' constant:

$sql = "INSERT INTO table SET ..., Date=CURRENT_DATE";

[impact]

Thank you all for replying.

CURDATE()inserts YYYY-MM-DD but i want to display to my clients in "M-d-Y" format. So after I insert CURDATE() into database how do i interpret the data into "M-d-Y" format?

Do you any such PHP script which will understand CURDATE() and represent into "M-d-Y" format?

[Matthew1980]

Hi there Impact,

[dev.mysql.com ]

Check the information out from that link, then you can use (for retrieval from DB):-

SELECT DATE_FORMAT('THE_COLUMN_NAME', %M %d %Y) AS `formatted date`

in the sql you are using. All you will need to do is experiment with the different formats until you are happy with what you see. This will just take the date in the column and re-order it into the format as you have specified and then store that into the temp var using the AS instruction.

Hopefully I have understood you correctly, and hopefully makes sense too ;)

Cheers,
MRb

[rocknbil]

Well, you'd want to quote it and add his/her delimiters. :-)

select date_format('THE_COLUMN_NAME', "%M-%d-%Y");

[Matthew1980]

^^^

Lol, I'm getting Déjà vu here Rocknbil: [webmasterworld.com ], and the column names (not needed but good practice imo) back ticks not quotes. I hadn't noticed that on first read/write.

Cheers,
MRb

[rocknbil]

Ack you're right, but - you still have to quote the format string.

select date_format(THE_COLUMN_NAME, "%M-%d-%Y");

or

select date_format(`THE_COLUMN_NAME`, "%M-%d-%Y");