CODEWITHSUNDEEP
phpjquerymysqldatetime
user975582
user975582

Reputation: 205

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

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

Upvotes: 1

Views: 1764

Answers (4)

mwiercinski
mwiercinski

Reputation: 16

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

Upvotes: 0

Alexey Gerasimov
Alexey Gerasimov

Reputation: 2141

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

Upvotes: 0

Brendan DeBeasi
Brendan DeBeasi

Reputation: 21

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

Upvotes: 1

NullUserException
NullUserException

Reputation: 85458

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

Upvotes: 6

Related Questions