CODEWITHSUNDEEP
phpcodeignitercodeigniter-2
oxvoxic
oxvoxic

Reputation: 325

codeigniter get database field of last 1 minute

I am trying to get the database result of last 1 minutes.. The database look like this:

id| from | to  | username | status | dbtime
-------------------------------------------------------
1 | u_1  | v_2 | myname   | 0      | 2015-02-19 14:30:0
2 | u_1  | v_2 | myname1  | 1      | 2015-02-19 14:30:0
3 | u_3  | v_1 | myname2  | 0      | 2015-02-19 16:30:0
4 | u_1  | v_2 | myname3  | 1      | 2015-02-19 16:30:0
5 | u_1  | v_2 | myname4  | 0      | 2015-02-19 16:30:0

Suppose current time is 2015-02-19 16:30:0 .. My target is to get the id number 4 from the database, as its in current time and status is 1 .. here is my query:

$this->db->select();
$this->db->order_by('dbtime', 'asc');
$result = $this->db->get_where('dbname', array('to' => $someid, 'status' => 1, 'dbtime >=' => time()- 90, ),1 );
if ($result->num_rows() > 0)
{
    return 1;
}
else {
  return 0;
}

But this query is not giving me the expected result. Its returning me the database column id 2 rather id no 4 ...

Upvotes: 1

Views: 1705

Answers (2)

CodeGodie
CodeGodie

Reputation: 12132

The reason to why your code is not working is that you're trying to compare two different date formats. Mysql will need to see equivalent data to whats stored in order to run comparisons. This is what I would do:

function getData($someid) {
    $curr = date('Y-m-d H:i:s');
    $last_min = date('Y-m-d H:i:s', strtotime('-1 minutes'));

    $this->db->select();
    $this->db->from('dbname');
    $this->db->where('to', $someid);
    $this->db->where('status', 1);
    $this->db->where('dbtime >=', $last_min);
    $this->db->where('dbtime <=', $curr);
    $this->db->order_by('dbtime', 'asc');
    $result = $this->db->get();
    if ($result->num_rows() > 0) {
        return 1;
    } else {
        return 0;
    }
}

Upvotes: 2

Yogesh Patel
Yogesh Patel

Reputation: 722

Try This

$this->db->select();
$this->db->order_by('dbtime', 'asc');
$result = $this->db->get_where('dbname', array('to' => $someid, 'status' => 1, 'dbtime' => date_sub(now(), interval 1 minute)),1 );
if ($result->num_rows() > 0)
{
    return 1;
}
else {
  return 0;
}

Upvotes: 0

Related Questions