Update MySQL date field using Active Record |
[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')"); Any ideas? Thanks!
[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
[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.
[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);
|
Welcome Guest, Not a member yet? Register Sign In |