CODEWITHSUNDEEP
phpmysqlcodeigniter
Dev
Dev

Reputation: 181

Comparing month and Year Mysql

I need to fetch data between two dates but its not working.

my table-

enter image description here

from date and to date parameters are - 

$from_date = '2016-12';
$to_date = '2017-03';

table column Month type is varchar

$this->db->select('*', FAlSE);
$this->db->where('t.Flag', '0');
$this->db->where('tpd.Status', $status);
$this->db->where("STR_TO_DATE(tpd.Month, '%Y-%m') >=", date($from_date));
$this->db->where("STR_TO_DATE(tpd.Month, '%Y-%m') <=", date($to_date));
$this->db->from('test_periodicity_details tpd');
$this->db->join('test t', 'tpd.Test_Id=t.Test_Id');
$this->db->join('school_information si', 't.School_Id=si.School_Id', 'left');
$this->db->join('test_status ss', 'tpd.Status=ss.Test_Status_Id', 'left');
echo $this->db->get_compiled_select();
$query = $this->db->get();
if ($query->num_rows() > 0){
    return $query->result();
}
else {
    return false;
}

Upvotes: 0

Views: 381

Answers (2)

Sᴀᴍ Onᴇᴌᴀ
Sᴀᴍ Onᴇᴌᴀ

Reputation: 8297

Since date() returns a string, don't cast the month values to datetimes (with STR_TO_DATE()). The first parameter of date() is the format string to output - the second parameter is the actual timestamp (date). So date($from_date) == date('2016-12') == '2016-12'. If you continue using STR_TO_DATE() then you would have a datetime value and a string value - while MySQL should be able to coalesce the values it is unnecessary.

You can use the BETWEEN operator to compare the Month with the range of dates:

$this->db->where('tpd.Month BETWEEN \''.$from_date .'\' AND \''.$to_date.'\'');

Otherwise just replace the two WHERE condition calls with:

$this->db->where('tpd.Month>=', $from_date);
$this->db->where('tpd.Month<=', $to_date);

You can see the results of the updated queries in this sqlfiddle. I also have an example of this working in PHP (but without CodeIgniter) on this site. It is running the equivalent queries with MySQLi functions.

Upvotes: 0

Dev
Dev

Reputation: 181

I have Solved The Query - 

$this->db->select('*', FAlSE);
$this->db->where('t.Flag', '0');
$this->db->where('tpd.Status', $status);
$this->db->where('tpd.Month>=', date($from_date));
$this->db->where('tpd.Month<=', date($to_date));
$this->db->from('test_periodicity_details tpd');
$this->db->join('test t', 'tpd.Test_Id=t.Test_Id');
$this->db->join('school_information si', 't.School_Id=si.School_Id', 'left');
$this->db->join('test_status ss', 'tpd.Status=ss.Test_Status_Id', 'left');
//echo $this->db->get_compiled_select();
$query = $this->db->get();
if ($query->num_rows() > 0){
    return $query->result();
}
else {
    return false;
}

Upvotes: 1

Related Questions