How to Correctly Insert Dates from jQuery DatePicker into a MySQL Database Using PHP?

Incorrect Date Format for PHP MySQL Insert

When using jQuery's datepicker to input dates into a database using PHP's mysql_query function, it's crucial to ensure that the date format follows MySQL conventions.

MySQL Date Formats

As outlined in the MySQL documentation, valid date formats for insertion into a MySQL database include:

• 'YYYY-MM-DD' or 'YY-MM-DD' with or without delimiters
• 'YYYYMMDD' or 'YYMMDD' in a numeric format
• A UNIX timestamp passed to MySQL's FROM_UNIXTIME() function

Error with '08/25/2012' Date

In the given code, the datepicker is set to the format '08/25/2012', which is not recognized by MySQL as a valid date literal. This results in the insertion of only '0000-00-00 00 00 00' into the database.

Solutions

To rectify this issue, you have several options:

1. Configure Datepicker: Set Datepicker to provide dates in a supported format using the altField and altFormat options.
2. Use STR_TO_DATE Function: Convert the jQuery date string using MySQL's STR_TO_DATE() function.
3. Convert Date in PHP: Manually convert the string into a valid date within PHP using, for example, a DateTime object.
4. Parse Date Manually: Divide the date string into its components and assemble it into a valid MySQL format.

SQL Injection Vulnerability

It's important to note that the provided PHP code is vulnerable to SQL injection. Consider utilizing prepared statements to prevent this security risk.

Deprecated mysql_* Functions

Additionally, the mysql_* functions are deprecated in PHP and are recommended to be replaced with either mysqli or PDO_MySQL extensions.

Consider DATE Type

Finally, consider using the MySQL DATE type instead of DATETIME or TIMESTAMP columns for storing date values without time components.

The above is the detailed content of How to Correctly Insert Dates from jQuery DatePicker into a MySQL Database Using PHP?. For more information, please follow other related articles on the PHP Chinese website!