phpmysqlcodeigniterwhere-clausequery-builder

How to write CodeIgniter's where() with "column_name BETWEEN NOW() - INTERVAL 30 DAY AND NOW()"


Are these both query same:

SELECT count(site_key) as count_lic
FROM (`activation`) 
WHERE `site_key` = '36LYRAHK0100000000B6101442Cs' AND `user_id` = '' AND `request_time` = 'BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

And

SELECT count(site_key) as count_lic
FROM (`activation`)
WHERE `site_key` = '36LYRAHK0100000000B6101442Cs' AND `user_id` = '' AND `request_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

Can we use 'request_time' = 'BETWEEN.......' ?

I am using PHP Codeigniter and I have done:

function fGetUsedLicenses($site_id) {

    //count number of activations without user_id
    $this->db->select('count(site_key) as count_lic');
    $this->db->where('site_key', $site_id);
    $this->db->where('user_id','');
    $this->db->where('request_time', 'BETWEEN NOW() - INTERVAL 30 DAY AND NOW()');
    $this->db->from('activation');
    $query = $this->db->get();
    $result = $query->row();
    $count = $result->count_lic;
    
    echo $this->db->last_query();
    //count number of activations with unique user_id
    $this->db->select('count(DISTINCT user_id) as count_lic');
    $this->db->where('site_key', $site_id);
    $this->db->where('user_id !=','');
    $this->db->where('request_time', 'BETWEEN NOW() - INTERVAL 30 DAY AND NOW()');
    $this->db->from('activation');
    $query = $this->db->get();
    $result = $query->row();
    $count += $result->count_lic;

    var_dump($count);
    echo $this->db->last_query();
    exit(0);
    return $count;
}

Now If I use this way the query generated is 'request_time' 'BETWEEN.....'


Solution

  • You can write it like

    ->where('request_time BETWEEN NOW() - INTERVAL 30 DAY AND NOW()', "", false);