How do I insert a date into mysql table using jQuery?

Question

I am using a datetimepicker from jQuery, and when I select a date from the datetimepicker it puts it in this format:

  11/10/2011 14:02

Now when I try to insert it into my sql table like this

INSERT INTO MYTABLE (date, name) values ('$_POST[datepicker]','$_POST[name]')

it says the date is not in the correct format.

Can someone please help me determine how to format such date so that it is accepted by the datetime() field ?

Thank you

    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 '14:33:00,1,2,'Y')' at line 2

        $datetosave = date("Y-m-d H:i:s", strtotime($_POST[date]));

$insert_q = "INSERT INTO reservations (res_date, res_numofseats, user_id, rsvp)
                     VALUES ($datetosave,$_POST[cars],2,'Y')";

NullUserException · Accepted Answer · 2011-11-10 19:22:32Z

6

Use the YYYY-MM-DD HH:MM:SS format, eg: 2011-11-10 14:02.

Ideally, you'd want to set up your jQuery plugin to output in the correct format. If that's not possible, you can convert it to the correct format by parsing the date using PHP's strtotime() and date() functions:

echo date('Y-m-d H:i:s', strtotime('11/10/2011 14:02')); 
// prints 2011-11-10 14:02:00

Reference: MySQL Manual on DATETIME

answered Nov 10, 2011 at 19:22

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

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

7 Comments

user975582 Over a year ago

would be too much to ask for a quick example on how to modify the jquery to put the right format? or to how to use the strtotime().

NullUserException Over a year ago

@user975582 Do you have a link to the plugin in question?

Kato Over a year ago

To expand on that thought, the correct format for mysql column is date ("Y-m-d H:i:s", $unixtime);

Kato Over a year ago

Panos's answer to this thread shows how to do it in jQuery

user975582 Over a year ago

i still cant get it please see above for my code. if i remove the 14:00 thing.. it saves all zeros

|

Brendan DeBeasi · Accepted Answer · 2011-11-10 19:32:44Z

1

Use PHP's date function to convert it to MySQL's datetime format (YYYY-MM-DD HH:I:S)

$mytime = date('Y-m-d H:i:s', strtotime('11/10/2011 14:02'));

answered Nov 10, 2011 at 19:32

Brendan DeBeasi

213 bronze badges

Comments

Alexey Gerasimov · Accepted Answer · 2011-11-10 20:16:45Z

0

I believe the problem is lack of quotes around the inserted date:

$insert_q = "INSERT INTO reservations (res_date, res_numofseats, user_id, rsvp)
VALUES ($datetosave,$_POST[cars],2,'Y')";

gives you VALUES 2011-11-10 14:33:00,1,2,'Y'

Add ticks around $datetosave

$insert_q = "INSERT INTO reservations (res_date, res_numofseats, user_id, rsvp)
VALUES ('".$datetosave."',$_POST[cars],2,'Y')";

answered Nov 10, 2011 at 20:16

Alexey Gerasimov

2,14113 silver badges17 bronze badges

Comments

mwiercinski · Accepted Answer · 2011-11-10 21:33:43Z

0

This may be out of the scope of your question, but your code is vulnerable to SQL Injection attacks. Try to always use inbuilt escaping/quoting methods of your database library or use the prepared statements. Not sure what library are you using, so few links here (I would paste more but SE does not allow me to do so :) ):

http://php.net/manual/en/pdo.prepare.php

http://php.net/manual/en/mysqli.real-escape-string.php

answered Nov 10, 2011 at 21:33

mwiercinski

161 bronze badge

Collectives™ on Stack Overflow

How do I insert a date into mysql table using jQuery?

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related