How to invoke MySQL NOW() function using Active Record?

[eluser]leonardteo[/eluser]
Hi guys,

I'm trying to find the cleanest way to use the MySQL NOW() function when inserting into a database using Active Record. Anyone know how to do this?

In my controller I have this:

Code:

function _doregister(){
        
        //Hash the password
        $password = dohash($this->input->post('password'));
        
        //set the data, load the model, insert user
        $data = array(
            'email' => $this->input->post('email'),
            'firstname' => $this->input->post('firstname'),
            'lastname' => $this->input->post('lastname'),
            'password' => $password,
            'username' => $this->input->post('username'),
            'created' => 'now()'   //THIS ISN'T WORKING!!!!
        );
        
        $this->load->model('User_model');
        $this->User_model->insert($data);
        
        echo "Inserted user";
    
    }

In the model I have this:

Code:

function insert($data){
        $this->db->insert('users', $data);
    }

I think what's happening above is that the 'created' => 'now()' is wrapping the NOW() function in quotes during the actual mysql query, so the date always sets to "0000-00-00 00:00:00" in the database.

Help?

Thanks,

Leonard

[eluser]Jamie Rumbelow[/eluser]
Because CodeIgniter's ActiveRecord class automatically escapes the input to insert(), just passing through "now()" won't work. The best thing to do is just call PHP's date() function and insert the date in manually in the correct format.

Code:

$data = array(
    'created' => date('Y-m-d H:i:s')
);

Alternatively, you could run the SQL query yourself by running the $this->db->query() command - but it's much easier to just put in the date manually!

Jamie

[eluser]Phil Sturgeon[/eluser]
Jamie is right, escaping happens on ActiveRecord set functions and this is for a good reason. MySQL functions are non-standard and so it breaks support for other database types (which is one of the biggest reasons for database abstraction in the first place).

Doing it via PHP as Jamie has suggested is the best option, or you can do this:

Code:

$this->db->set('date', 'NOW()', FALSE);

Setting FALSE in the 3rd parameter tells ActiveRecord not to escape your value.

[eluser]leonardteo[/eluser]
Thanks for the help guys. That's awesome.

L.

[eluser]CroNiX[/eluser]
I usually let the database handle that automatically, like if Im updating a record...like for a field called "modified" the DDL looks like:

`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

then it will auto insert a timestamp in that field if a value isnt explicitly set. You can use NOW as well...

Why bother coding it with php if the db will do it for you?

[eluser]leonardteo[/eluser]
Does the "on update current_timestamp" update the field when the actual field is updated or the entire row? I had avoided that feature as I thought originally that it would automatically update the field if I updated any other field in the database. I didn't have time to try it out myself....

[eluser]CroNiX[/eluser]
It will update the timestamp only when something in that row is updated...like when you

Code:

$this->db->where('id', $id);
$this->db->update('table', $data);

it will update the timestamp for that row where the id = $id

[eluser]leonardteo[/eluser]
There you go. See, that would be useful for something like "last modified". In this case I needed something that only inserted the creation date and would never update again.......

[eluser]Jondolar[/eluser]
Using the ON UPDATE CURRENT_TIMESTAMP feature will not work across different databases. If you are writing an application and planning on using different databases for the back-end, you'll want to put all functionality in PHP and just stuff in the data into the database. Don't rely on the database to do anything.

On the other hand, if you are just using MySQL, updating the timestamp field is very convenient.

[eluser]CroNiX[/eluser]
Then you would just use:
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

when the record is created, the default value in that field would be CURRENT_TIMESTAMP or NOW