Using NOW() with active records

[eluser]Unknown[/eluser]
Hullo,

I've been searching the forums on how to find a way to use the sql function NOW() with active records. I couldn't find a solution that I liked so I came up with this.

Background

The example illustrates a simple post in a blog or news feed. There is also a updated_at field which uses CURRENT_TIMESTAMP and the On Update use CURRENT_TIMESTAMP is set on the updated_at column. This is the reason we need the NOW() function in the query.

The alternative is of course to use PHP to get the current time in variable instead of using NOW(), but it feels much cleaner to have it in the query.

Solution

Code:

$data = array('header' => $header, 'body' => $body, 'author_id' => $author_id);
$this->db->set('created_at', 'NOW()', false);
$this->db->insert('news', $data);

This works great for me. But I wonder if there is any drawbacks or bad practice with this solution? Some other flaws maybe? I also wonder if this becomes one single query or if it is divided into two?

Anyways, enjoy the solution.

[eluser]RaZoR LeGaCy[/eluser]
Yeah I was trying to do this and I just gave up and decided to hand write my query. LOL

But this definitely helps.

[eluser]soupdragon[/eluser]
thank you thank you :-)

was just about to rip my hair out with this !

[eluser]AgentPhoenix[/eluser]
Nice catch on this one, coffeepunk!

I guess the only thing that'd concern me is how the other database platforms handle NOW(). I'm not sure if it's the same as MySQL or not. I know, for instance, that PostgreSQL allows NOW(), but it's a reference to a different database function. That isn't to say it won't work, but that's the one thing that concerns me. I've always preferred to store dates as UNIX timestamps and use CI's built-in now() function and then convert it from a UNIX timestamp to a human readable date after it comes out of the database.

[eluser]chuckleberry13[/eluser]
It would be nice if you could so something like this

Code:

$this->db->update('table', array('timestamp'=>'NOW()', FALSE);

Coffeepunk you're work around worked just fine for me. Just more lines of code.