Firstly, my question doesn't have to do with form validation. I have a form in which I need users to enter dates (NOT the current date) and would like them entered as m/d/y or m-d-y. This goes into a MySQL date field. PHP/MySQL always sees the order as Y/m/d, so it interprets the date wrong. How do I get PHP to convert the text string into a format that makes sense to the date field? Do I use the strtotime function? I've tried that, but may not know how to manipulate it.
Any help greatly appreciated
Getting user input date into MySQL - novice question
Moderator: General Moderators
Re: Getting user input date into MySQL - novice question
mmm i know how u can retrieve them formatted with DATE_FORMAT...
but im not sure if that works when INSERTing
anyway:
hope it does 
but im not sure if that works when INSERTing
anyway:
Code: Select all
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'Re: Getting user input date into MySQL - novice question
Thanks, but your response it too cryptic for me to understand. I think my question is quite simple, and I am looking for a simple code example.
Re: Getting user input date into MySQL - novice question
Yes, you are correct, the MySQL engine does see dates as Y-M-D
What you can do, really simple, to convert them into the English system of dates (i.e. M/D/Y).
Replace the $dateFroMDatabase with the variable that contains the date retrieved from the database. The 'm/d/Y' tells PHP to take the date and format it into month/day/year
What you can do, really simple, to convert them into the English system of dates (i.e. M/D/Y).
Code: Select all
<?php
$data = date('m/d/Y', strtotime($dateFroMDatabase));
?>
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Getting user input date into MySQL - novice question
You're going to need to get the parts of the date from the $_POST var using substr() and reassemble it using sprintf() function.
Re: Getting user input date into MySQL - novice question
Thanks for all the responses.
Upon looking at it again, I do see the value of response #1. However, only response #4 seemed to understand that I am trying to SEND the formatted date into to the database, not retrieve it. Formatting the retrieved date is easy, as responses #1 and #3 show. With so many date functions in PHP, it's strange that there isn't a comparable fuction to date() that goes the other direction--takes a formatted date like 02/28/2008 and converts it to 20080228 for the database.
For now I guess I will require my users to enter the date in the format the database requires.
Upon looking at it again, I do see the value of response #1. However, only response #4 seemed to understand that I am trying to SEND the formatted date into to the database, not retrieve it. Formatting the retrieved date is easy, as responses #1 and #3 show. With so many date functions in PHP, it's strange that there isn't a comparable fuction to date() that goes the other direction--takes a formatted date like 02/28/2008 and converts it to 20080228 for the database.
For now I guess I will require my users to enter the date in the format the database requires.
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Getting user input date into MySQL - novice question
MySQL date format is in the YYY-MM-DD order to make sorting straightforward. If The month were first it would not sort by year as it should.
I understand your frustration, but this is actually one of the smaller challenges that programming presents.
$m = substr($_POST['date'],0,2) will give you the month of a MM-DD-YYYY format
$d = substr($_POST['date'],3,2) will give you the day
$y = substr($_POST['date'],6,4) will give you the year
$mdt = sprintf("%s-%s-%s",$y,$m,$d) will give you the date in MySQL format.
I understand your frustration, but this is actually one of the smaller challenges that programming presents.
$m = substr($_POST['date'],0,2) will give you the month of a MM-DD-YYYY format
$d = substr($_POST['date'],3,2) will give you the day
$y = substr($_POST['date'],6,4) will give you the year
$mdt = sprintf("%s-%s-%s",$y,$m,$d) will give you the date in MySQL format.
Re: Getting user input date into MySQL - novice question
Thank you , Bill. Kind of you to spoonfeed it to me! I will use it.
Rob
Rob