Search code examples
mysqlcodeigniterdatedatetimemysql-error-1064

SQL syntax error when inserting date into database (framework: codeigniter)

I'm trying to insert a date into my MySQL Database, but I get an SQL syntax error, this is my code (about the date):

$h = "2";
$hm = $h * 60; 
$ms = $hm * 60;
$data['date'] = gmdate("Y-m-d g:i:s", time()+($ms));
$thismoment = $data['date'];

$this->db->query('INSERT INTO gotcha_user_task (gotcha_user_id, task_id, target_gotcha_user_id, date) 
VALUES ('.$gotchauserid.','.$notactivetask.','.$randleasttargetedvalue.','.$thismoment.') ');

And this is my error:

Error Number: 1064
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 '9:31:50)' at line 2
INSERT INTO gotcha_user_task (gotcha_user_id, task_id, target_gotcha_user_id, date) VALUES (6,4,7,2014-05-22 9:31:50)

I've manually added two rows with phpMyAdmin thus I know that the date should be put in this order YYYY-MM-DD HH-MM-SS (that is at least how they are in the table), thus what I've tried with that (since I have read that as a solution for others) hasn't helped me (yet). I've also tried a 'solution' with using ` because there is a space between the first part and second part, didn't help me neither.

This is my first 'bigger' project (it's still really small ^^), so it could probably be anything, cause almost everything I write is something I've learned while working on this project.

Solution

  • Your query must be this:

    $this->db->query('INSERT INTO 
      gotcha_user_task (gotcha_user_id, task_id, target_gotcha_user_id, date) 
VALUES ('.$gotchauserid.','.$notactivetask.','.$randleasttargetedvalue.',"'.$thismoment.'") ');

    You 2014-05-22 9:31:50 this value should be in quota mark. that's why it is giving error.