PHP - Insert date into mysql

Question

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.

I suggest using timestamps (time()) instead of date. and use the date() function to convert it to any format when displaying. — MakuraYami
– MakuraYami, Commented Aug 6, 2012 at 11:19
you have two problems (sql injection vulnerability and usage of an old, soon to be deprecated extension) that can be both solved if you switch to PDO (or mysqli) — mishu
– mishu, Commented Aug 6, 2012 at 11:19
@mishu yes I am aware of both problems :) it's just I found about PDO and mysqli yesterday and I want to finish up the coding then convert the extension and fix security problems. — Hassan
– Hassan, Commented Aug 6, 2012 at 11:34
@xperator, When you use timestamp it saves your time as a int. which will never give problems as you are having now. and it has the advantage of being able to be written out in ANY date format without any risky string-to-date functions. — MakuraYami
– MakuraYami, Commented Aug 6, 2012 at 11:45

WatsMyName · Accepted Answer · 2012-08-06 11:16:07Z

16

try

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

answered Aug 6, 2012 at 11:16

WatsMyName

4,5025 gold badges49 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mishu Over a year ago

I agree with @Mike here, what's the difference between the two? codepad.org/KFF4pi5u (except for making the script a bit more complicated)

WatsMyName Over a year ago

Well in past I encountered with same problem, and the way i did work. Again, I just tried to insert date as xperator did, the date was inserted. @xperator, will you mind printing the sql statement?

mickmackusa Over a year ago

This answer is nonsense -- it makes no change to the string. No one should be implementing this non-solution. Code-only answers are low value on Stack Overflow because they do very little to educate/empower thousands of future researchers.

Omesh · Accepted Answer · 2012-08-06 11:17:08Z

14

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());

answered Aug 6, 2012 at 11:17

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

4 Comments

Omesh Over a year ago

try again there was one extra comma

Omesh Over a year ago

whats is the exact data type of your field date_of_event?

Hassan Over a year ago

oops I had a tiny problem with that comma. fixed and works now :)

mickmackusa Over a year ago

A prepared statement should be used for security/stability. This snippet should not be used in any professional code.

DavidS · Accepted Answer · 2012-08-06 11:19:28Z

2

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

answered Aug 6, 2012 at 11:19

DavidS

1,7001 gold badge12 silver badges27 bronze badges

2 Comments

Hassan Over a year ago

No it's not the current date, today date was just an example :)

mickmackusa Over a year ago

A prepared statement should be used for security/stability. This snippet should not be used in any professional code.

John Woo · Accepted Answer · 2012-08-06 11:26:26Z

0

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());

edited Aug 6, 2012 at 11:26

answered Aug 6, 2012 at 11:20

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

mickmackusa Over a year ago

A prepared statement should be used for security/stability. This snippet should not be used in any professional code.

Maksim Burnin · Accepted Answer · 2012-08-06 11:36:37Z

0

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

answered Aug 6, 2012 at 11:36

Maksim Burnin

3604 silver badges11 bronze badges

1 Comment

mickmackusa Over a year ago

A prepared statement should be used for security/stability. This snippet should not be used in any professional code.

Rohit Suthar · Accepted Answer · 2017-12-26 07:48:13Z

-1

$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."')";

edited Dec 26, 2017 at 7:48

Rohit Suthar

3,6282 gold badges48 silver badges49 bronze badges

answered Dec 26, 2017 at 7:14

anilv0001

12 bronze badges

1 Comment

mickmackusa Over a year ago

A prepared statement should be used for security/stability. This snippet should not be used in any professional code. Furthermore, code-only answers are low value on Stack Overflow because they do very little to educate/empower thousands of future researchers.

Collectives™ on Stack Overflow

PHP - Insert date into mysql

6 Answers 6

3 Comments

4 Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

4 Comments

2 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related