How to delete records using ActiveRecord with mySQL function as WHERE condition

Question

I have a table named ChatSessions where I keep track of active users in the chatroom. I need to prune expired user sessions from the table every 10 minutes. Using pure php-mysql is plain simple but I'm totally clueless how to convert this into ActiveRecord in CodeIgniter. The plain SQL query is below:

SELECT *
FROM `ChatSessions`
WHERE `SessionExpires` < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )

Can anybody tell me what is the equivalent code in CodeIgniter using ActiveRecord?

Elliot · Accepted Answer · 2010-06-08 22:57:21Z

4

The following code should do it:

// You can use custom strings in the where() function
$where = "SessionExpires < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
// although you may need to set the third parameter to FALSE in order to stop CI from protecting your fields.
$this->db->where($where, null, false);
$this->db->delete('ChatSessions');

You could also try the following (but I don't know if this will work):

$where_condition = "DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
$this->db->where("SessionExpires <", $where_condition, false);
$this->db->delete('ChatSessions');

edited Jun 8, 2010 at 22:57

answered Jun 8, 2010 at 20:06

Elliot

1,60117 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jhourlad Estrella Over a year ago

I don't think ActiveRecord allows functions on WHERE conditions, the same reason why we need to use set($field,$value) for Select()

Elliot Over a year ago

The user guide gives this example: $this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);

Stephen Curran · Accepted Answer · 2010-06-07 19:23:18Z

2

Converting it to ActiveRecord if you already have the SQL seems like a waste of time. I'd just execute the SQL directly.

$sql = "DELETE FROM ChatSessions 
        WHERE SessionExpires < DATE_SUB( NOW( ) , INTERVAL 10 MINUTE )";
$this->db->query($sql);

answered Jun 7, 2010 at 19:23

Stephen Curran

7,4332 gold badges33 silver badges22 bronze badges

1 Comment

Jhourlad Estrella Over a year ago

Thanks for the reply. Yeah, that crossed my mind but I was trying to steer away from "direct" queries in order to retain some kind of consistency on my code. I still haven't figured out yet how to do with ActiveRecord though.

Collectives™ on Stack Overflow

How to delete records using ActiveRecord with mySQL function as WHERE condition

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related