CODEWITHSUNDEEP
codeignitercodeigniter-2
black_belt
black_belt

Reputation: 6799

how to write database query to search month in date field in Codeigniter's way?

Could you please tell me how to write database query to search month in date field in Codeigniter's way? Or could you please show me how to write the following query in codeigniter's way?

Thanks in Advance :)

 "SELECT attendanceno FROM attendance WHERE (barcodeid = '$studentid') AND  (month(attendancedate)='$month' AND year(attendancedate)='$year') ";

Edit

Thanks to Stefan, who answered my question below. After reading his post I also tried the following script and found it working. That's why I just wanted to share in case anyone wants to use it. But all credit goes to Stefan. :)

  $this->db->select('*');
  $this->db->from('attendance');
  $this->db->where('barcodeid', $studentid);
  $this->db->where('month(attendancedate)', $month);
  $this->db->where('year(attendancedate) ', $year);
  $this->db->order_by('attendancedate ','ASC');     
  $getData = $this->db->get('', $perPage, $uri);

Upvotes: 2

Views: 3560

Answers (2)

DanielJRobles
DanielJRobles

Reputation: 33

Assumptions:

  • CodeIgniter 2.2.0

  • Oracle 11 database

    //Option 1, raw SQL
 $sql = "
  SELECT DISTINCT <DATE_COLUMN>
  FROM <TABLE NAME>
  WHERE 1=1
    AND EXTRACT(month FROM <DATE_COLUMN>) = $month
    AND EXTRACT(year FROM <DATE_COLUMN>) = $year
 ";
 $query = $this->db->query($sql);

//Option 2, CI db functions
$this->db->distinct();
$this->db->select('<DATE_COLUMN>');
$this->db->where('EXTRACT(month FROM <DATE_COLUMN>) =', $month);
$this->db->where('EXTRACT(year FROM <DATE_COLUMN>) =', $year);
$query = $this->db->get('<TABLE NAME>');

both will produce the following with $month = 3 and $year = 2017:

     CI_DB_oci8_result Object
     (
         [stmt_id] => Resource id #9
         [curs_id] => 
         [limit_used] => 
         [conn_id] => Resource id #1
         [result_id] => 1
         [result_array] => Array
             (
                 [0] => Array
                     (
                         [<DATE_COLUMN>] => 22-MAR-17
                     )

                 [1] => Array
                     (
                         [<DATE_COLUMN>] => 14-MAR-17
                     )

                 [2] => Array
                     (
                         [<DATE_COLUMN>] => 02-MAR-17
                     )

                 [3] => Array
                     (
                         [<DATE_COLUMN>] => 06-MAR-17
                     )

             )

         [result_object] => Array
             (
             )

         [custom_result_object] => Array
             (
             )

         [current_row] => 0
         [num_rows] => 4
         [row_data] => 
     )

Upvotes: 0

Stefan
Stefan

Reputation: 3041

If date and month are strings:

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (month(attendancedate)=? AND year(attendancedate)=?)", array($studentid, $month,$year));

if they are integers

$this->db->query("SELECT attendanceno FROM attendance WHERE (barcodeid = ?) AND  (MONTH(attendancedate)='?' AND YEAR(attendancedate)='?')", array($studentid, $month,$year));

Upvotes: 2

Related Questions