CODEWITHSUNDEEP
phpmysqlhtmldatabaseformshttp-post
ThN
ThN

Reputation: 3276

Why date stored in MySQL is all wrong?

I am building a PHP to MySQL webpage and I didn't notice it until now. My date from form field which is posted to PHP script is sent to MySQL table for storage. Even though in the date field within the form is being displayed correctly and POST date value is verified to be exact, it is not going into my MySQL table correctly. For some odd reason, the year is 2006 not 2012. However, the time is correct.

Here is my Form date field:

<input type=text id="date" name="srdate" value=<?php echo date("m/d/y"); ?>

Date Column in my MySQL table is of TYPE DATE.

I did check the date stored in my table and it has wrong date not year 2012 but 2006.

Any Idea,

Upvotes: 0

Views: 1666

Answers (3)

colonelclick
colonelclick

Reputation: 2215

From the manual:

Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Upvotes: 1

Marc B
Marc B

Reputation: 360692

MySQL's only acceptable date input format is YYYY-MM-DD HH:MM:SS. The date you're generating in PHP will look like (for today) 06/12/12 which MySQL will try to parse, but see as 12th December, 2006, and not the actual June 12/2012.

As well, note that your code is gaping wide open for SQL injection attacks. Read up and learn how to prevent those before you go ANY FARTHER with this code. Otherwise your server is going to get pwn3d.

Upvotes: 4

ThiefMaster
ThiefMaster

Reputation: 318518

You need to use the yyyy-mm-dd syntax.

While I'd expect MySQL to throw an error when trying to insert 06/12/12 it apparently parses it as 2006-12-12:

mysql> SELECT cast('06/12/12' as date);
+--------------------------+
| cast('06/12/12' as date) |
+--------------------------+
| 2006-12-12               |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT cast('2012-06-12' as date);
+----------------------------+
| cast('2012-06-12' as date) |
+----------------------------+
| 2012-06-12                 |
+----------------------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions