Welcome to WebmasterWorld Guest from 54.145.13.215

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Basic help with php Date()

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

Junior Member

5+ Year Member

joined:Sept 12, 2009
posts: 137
votes: 0


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 Sept 2, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


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 Sept 2, 2010 (gmt 0)

Senior Member

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

joined:Mar 30, 2003
posts:3719
votes: 0


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 Sept 2, 2010 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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 Sept 2, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 7, 2004
posts:660
votes: 0


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 Sept 3, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 12, 2009
posts:137
votes: 0


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 Sept 3, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


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 Sept 3, 2010 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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 Sept 3, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
posts:1396
votes: 0


^^^

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 Sept 4, 2010 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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