Error when trying to insert inside a DateTime field in database mysql

Question

I tried doing this after going through many forums,

$date=date('Y-m-d H:i:s');

$query = "INSERT INTO temp_order(user_id ,item_id ,name ,price ,quantity, date) VALUES ({$_GET['cust_id']},{$_GET['item']},'{$content['name']}',{$content['price']},{$_GET['quan']},{$date})";

But still getting this error " 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 '09:23:12)' at line 1".

I don't know what that means. Here date column inside database has type of 'datetime'.

Do not insert variables directly into the query string. Use something like Zend_Db to ->quote() the variables first, or use its ->insert() method. — Will Palmer
– Will Palmer, Commented Jun 30, 2013 at 9:26
@Pekka i tried using $dates, but still getting the same error — Zohaib
– Zohaib, Commented Jun 30, 2013 at 9:27
Your actual problem is that you're missing the quotes around '{$date}', but you'd be much better off using mysqli or pdo with parameterized queries instead. — Joachim Isaksson
– Joachim Isaksson, Commented Jun 30, 2013 at 9:28
@Pekka웃: nah, see the manual: "MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list: ... DATE..". — Wrikken
– Wrikken, Commented Jun 30, 2013 at 9:29

Barmar · Accepted Answer · 2013-06-30 09:32:29Z

2

You're missing quotes around the date. It should be:

$query = "INSERT INTO temp_order(user_id ,item_id ,name ,price ,quantity, date)
          VALUES ({$_GET['cust_id']},{$_GET['item']},'{$content['name']}',
                  {$content['price']},{$_GET['quan']},'{$date}')";

answered Jun 30, 2013 at 9:32

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

davek · Accepted Answer · 2013-06-30 09:41:31Z

0

It looks as though your date value is unquoted and it (the sql) looks thus to the database

..., 09:23:12)

which won't work. Try a) inspecting the actual value before using it, making sure it is quoted (if you have to use a string) and/or b) use one of the mysql built-in conversion functions so that you are passing a date object and not some kind of string.

e.g STR_TO_DATE documented here

edited Jun 30, 2013 at 9:41

answered Jun 30, 2013 at 9:28

davek

23k11 gold badges80 silver badges97 bronze badges

3 Comments

Zohaib Over a year ago

But when i echo out $date, it is giving this 2013-06-30 09:30:19, which i think is suitable for datetime field.

Barmar Over a year ago

Since MySQL automatically converts strings using the same method as STR_TO_DATE, why is that necessary?

davek Over a year ago

@Barmar: because it's bad practice to mix data types. Always assign a date object to a date field etc. And you want to know asap if your supposed value is valid or not, before you send stuff over the wire.

Collectives™ on Stack Overflow

Error when trying to insert inside a DateTime field in database mysql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related