使用STR_TO_DATE存储日期到mysql datetime数据类型 - Store date using STR_TO_DATE into mysql datetime data type

Store date using STR_TO_DATE into mysql datetime data type

这是我的INSERT SQL:

$insert_pg = "INSERT INTO tblPhotoAlbum (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) VALUES ('".$pg_title."',  'STR_TO_DATE('".$pg_date_post."', '%d-%m-%Y')', '".$current_time."' , '".$pg_detail."')";

在"STR_TO_DATE"部分有错误，php会回显这个mysql错误消息:

Warining: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01/05/2014', '%d-%m-%Y')', '17:24:24' , '
ABC Opening 2014
')' at line 1

去掉STR_TO_DATE()函数周围的单引号

$insert_pg = "INSERT INTO tblPhotoAlbum (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) VALUES ('".$pg_title."',  STR_TO_DATE('".$pg_date_post."', '%d-%m-%Y'), '".$current_time."' , '".$pg_detail."')";

这是我的修改，我找到了我的解决方案。

$insert_pg = "INSERT INTO tblPhotoAlbum (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) VALUES ('".$pg_title."',  STR_TO_DATE('".$pg_date_post."','%m/%d/%Y') , '".$current_time."' , '".$pg_detail."')";

试着让你的查询变量在单引号

并从'STR_TO_DATE('".$pg_date_post."', '%d-%m-%Y')'中删除引号

$insert_pg = "INSERT INTO tblPhotoAlbum (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) VALUES ('$pg_title',STR_TO_DATE('$pg_date_post','%Y-%m-%d'), '$current_time' , '$pg_detail')";

或

$insert_pg = "INSERT INTO tblPhotoAlbum (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) VALUES ('".$pg_title."',  STR_TO_DATE('".$pg_date_post."', '%Y-%m-%d'), '".$current_time."' , '".$pg_detail."')";

日期格式设置错误。在mysql中输入日期时，应该使用YYYY-MM-DD格式:

'-%Y-%m-%d'

插入内容如下:

$insert_pg = "INSERT INTO tblPhotoAlbum 
    (photo_album_title, photo_album_date, photo_album_time, photo_album_detail) 
    VALUES ('".$pg_title."',  '".date('Y-m-d', strtotime($pg_date_post))."', '".$current_time."' , '".$pg_detail."')";