phpmysqldatetimecodeigniterquery-builder

SELECT query WHERE a column is greater than a number and a DATETIME column is greater than 24 hours ago using CodeIgniter query builder

I want to build a query like: (pseudo-code)

SELECT username
FROM users
WHERE login_attempts > 3 AND last_attempt_time < [24 hours]

I have 2 questions to build the above query.

  1. I'm storing the datetime stamp in last_attempt_time in DATETIME format (2011-06-16 10:29:23).
    Can I directly select some row which has time difference less than 24 hours from now? (OR do I have to select a row, and then check with for example PHP the time difference? )

  2. How can I write this query using CodeIgniter Active Record? (Do I need to use get_where()?) I could not find any related example in their documentation.

Solution

  • Try this:

    $this->db->select('username');
$this->db->from('users');
$this->db->where('login_attempts >', 3);
$this->db->where('last_attempt_time <', date('Y-m-d H:i:s', strtotime('-24 hours')));

$query = $this->db->get();

    You can find the complete documentation here: http://codeigniter.com/user_guide/database/active_record.html#select.