Getting user input date into MySQL - novice question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Getting user input date into MySQL - novice question

Post by rhecker »

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
BETA
Forum Commoner
Posts: 47
Joined: Fri Jul 25, 2008 3:21 am

Re: Getting user input date into MySQL - novice question

Post by BETA »

mmm i know how u can retrieve them formatted with DATE_FORMAT...
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'
hope it does :roll:
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Getting user input date into MySQL - novice question

Post by rhecker »

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.
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Re: Getting user input date into MySQL - novice question

Post by LiveFree »

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).

Code: Select all

 
<?php
$data = date('m/d/Y', strtotime($dateFroMDatabase));
?>
 
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
User avatar
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

Post by Bill H »

You're going to need to get the parts of the date from the $_POST var using substr() and reassemble it using sprintf() function.
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Getting user input date into MySQL - novice question

Post by rhecker »

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.
User avatar
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

Post by Bill H »

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.
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Getting user input date into MySQL - novice question

Post by rhecker »

Thank you , Bill. Kind of you to spoonfeed it to me! I will use it.

Rob
Post Reply