Welcome to WebmasterWorld Guest from 54.161.25.142

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Basic help with php Date()

     
2:37 am on Sep 2, 2010 (gmt 0)

5+ Year Member



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,
5:09 am on Sep 2, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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);
6:52 am on Sep 2, 2010 (gmt 0)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
5:12 pm on Sep 2, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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);
5:36 pm on Sep 2, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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";
12:45 am on Sep 3, 2010 (gmt 0)

5+ Year Member



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?
10:07 am on Sep 3, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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
5:02 pm on Sep 3, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

select date_format('THE_COLUMN_NAME', "%M-%d-%Y");
5:12 pm on Sep 3, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



^^^

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
5:57 pm on Sep 4, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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");
 

Featured Threads

Hot Threads This Week

Hot Threads This Month