Welcome Guest, Not a member yet? Register   Sign In
Update MySQL date field using Active Record
#1

[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!
#2

[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
#3

[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.
#4

[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');
#5

[eluser]Constantin Iliescu[/eluser]
Thank you, Armchair Samurai!!!




Theme © iAndrew 2016 - Forum software by © MyBB