+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Update MySQL date field using Active Record (/showthread.php?tid=31065)
Update MySQL date field using Active Record - El Forum - 06-05-2010
[eluser]Constantin Iliescu[/eluser]
I'm trying to update an MySQL date field but it won't work. The row updates with a null date.
Here is how I'm trying:
From view, the date is sent as a string like '17.06.2010'.
In model:
Code:
$this->db->set('birth_date', "STR_TO_DATE('" . $birth_date . "', '%d.%m.%Y')");
$this->db->update('pages');Any ideas?
Thanks!
Update MySQL date field using Active Record - El Forum - 06-05-2010
[eluser]falkencreative[/eluser]
Since I don't know what code the STR_TO_DATE() function holds... The first thing I would check is what format are you putting this date in after it comes out of the STR_TO_DATE() function, and make sure it matches up with the column type in the database. I'd also print out the results of "STR_TO_DATE('" . $birth_date . "', '%d.%m.%Y')" just to make sure it is formatting things like you expect it to.
For example, if the column type is "DATE" you'd need your input to follow this format "YYYY-MM-DD", but if you are using "TIMESTAMP" you'd use "YYYY-MM-DD HH:mm:SS" (where the data is the amount of time that has passed since January 1, 1970).
A quick refresher if you need it: http://www.tizag.com/mysqlTutorial/mysql-date.php
Update MySQL date field using Active Record - El Forum - 06-05-2010
[eluser]Constantin Iliescu[/eluser]
Thanks for the reply, falkencreative!
STR_TO_DATE() holds a string like '17.06.2010' and the field is of type DATE.
It seems that active record just can't detect the STR_TO_DATE function, because when I try an update in phpmyadmin with the same data, the row is updated correctly.
Update MySQL date field using Active Record - El Forum - 06-05-2010
[eluser]Armchair Samurai[/eluser]
You need to prevent AR from automatically escaping the data - add FALSE as the third parameter as per the User Guide.
Code:
$birth_date = $this->db->escape($birth_date);
$this->db->set('birth_date', "STR_TO_DATE($birth_date, '%d.%m.%Y')", FALSE);
$this->db->update('pages');Update MySQL date field using Active Record - El Forum - 06-05-2010
[eluser]Constantin Iliescu[/eluser]
Thank you, Armchair Samurai!!!