| Basic help with php Date()
|
impact
msg:4195400 | 2:37 am on Sep 2, 2010 (gmt 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,
|
Anyango
msg:4195427 | 5:09 am on Sep 2, 2010 (gmt 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);
|
dreamcatcher
msg:4195436 | 6:52 am on Sep 2, 2010 (gmt 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
|
rocknbil
msg:4195666 | 5:12 pm on Sep 2, 2010 (gmt 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);
|
AlexK
msg:4195679 | 5:36 pm on Sep 2, 2010 (gmt 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";
|
impact
msg:4195854 | 12:45 am on Sep 3, 2010 (gmt 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?
|
Matthew1980
msg:4195988 | 10:07 am on Sep 3, 2010 (gmt 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
|
rocknbil
msg:4196151 | 5:02 pm on Sep 3, 2010 (gmt 0) |
Well, you'd want to quote it and add his/her delimiters. :-)
select date_format('THE_COLUMN_NAME', "%M-%d-%Y");
|
Matthew1980
msg:4196159 | 5:12 pm on Sep 3, 2010 (gmt 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
|
rocknbil
msg:4196574 | 5:57 pm on Sep 4, 2010 (gmt 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");
|
|
|
