PHP - Insert date into mysql

问题:

I am trying to insert date into mysql but everytime it fails and comes out as 0000-00-00 in phpmyadmin

My date format is like 2012-08-06 (yyyy-mm-dd) and the date field type in database is date.

$date = "2012-08-06";
mysql_query("INSERT INTO data_table (title, date_of_event) 
             VALUES('". $_POST['post_title'] ."',
                    '". $date ."')") or die(mysql_error()); 

tried changing - to / or removing them, it doesn't work.

回答1:

try CAST function in MySQL:

mysql_query("INSERT INTO data_table (title, date_of_event)
VALUES('". $_POST['post_title'] ."',
CAST('". $date ."' AS DATE))") or die(mysql_error()); 

回答2:

try

$date = "2012-08-06";
$date=date("Y-m-d",strtotime($date));

回答3:

Unless you want to insert different dates than "today", you can use CURDATE():

$sql = 'INSERT INTO data_tables (title, date_of_event) VALUES ("%s", CURDATE())';
$sql = sprintf ($sql, $_POST['post_title']);

PS! Please do not forget to sanitize your MySQL input, especially via mysql_real_escape_string ()

回答4:

try converting the date first.

$date = "2012-08-06";

mysql_query("INSERT INTO data_table (title, date_of_event)
               VALUES('" . $_POST['post_title'] . "',
                      '" . $date . "')") 
           or die(mysql_error());

回答5:

How to debug SQL queries when you stuck

Print you query and run it directly in mysql or phpMyAdmin

$date = "2012-08-06";
$query= "INSERT INTO data_table (title, date_of_event) 
             VALUES('". $_POST['post_title'] ."',
                    '". $date ."')";
echo $query;
mysql_query($query) or die(mysql_error()); 

that way you can make sure that the problem is not in your PHP-script, but in your SQL-query

How to submit questions on SQ-queries

Make sure that you provided enough closure

• Table schema
• Query
• Error message is any

回答6:

$date=$year."-".$month."-".$day; 
$new_date=date('Y-m-d', strtotime($dob)); 
$status=0;  
$insert_date = date("Y-m-d H:i:s");  
$latest_insert_id=0; 

$insertSql="insert into participationDetail (formId,name,city,emailId,dob,mobile,status,social_media1,social_media2,visa_status,tnc_status,data,gender,insertDate)values('".$formid."','".$name."','".$city."','".$email."','".$new_date."','".$mobile."','".$status."','".$link1."','".$link2."','".$visa_check."','".$tnc_check."','".json_encode($detail_arr,JSON_HEX_APOS)."','".$gender."','".$insert_date."')";