CODEWITHSUNDEEP
phpmysqlcodeigniter
owf
owf

Reputation: 251

Query with multiple WHERE and CASE PHP(CodeIgniter) MySQL

I'd like to select all period of current term (as now FY80) + 3 period only of last term (FY79) from m_period table

Here's the table m_period looks :

+----+------+-------------+--------+
| id | term | period      | status |
+----+------+-------------+--------+
|  1 | FY79 | 79::2020/01 | close  |
|  2 | FY79 | 79::2020/02 | close  |
|  3 | FY79 | 79::2020/03 | close  |
|  4 | FY79 | 79::2020/04 | close  |
|  5 | FY79 | 79::2020/05 | close  |
|  6 | FY79 | 79::2020/06 | close  |
|  7 | FY80 | 80::2020/07 | open   |
|  8 | FY80 | 80::2020/08 | open   |
|  9 | FY80 | 80::2020/09 | open   |
| 10 | FY80 | 80::2020/10 | open   |
| 11 | FY80 | 80::2020/11 | open   |
| 12 | FY80 | 80::2020/12 | open   |
| 13 | FY80 | 80::2021/01 | open   |
| 14 | FY80 | 80::2021/02 | open   |
| 15 | FY80 | 80::2021/03 | open   |
| 16 | FY80 | 80::2021/04 | open   |
| 17 | FY80 | 80::2021/05 | open   |
| 18 | FY80 | 80::2021/06 | open   |
+----+------+-------------+--------+

my php script stuck from here :

<?php

function getPeriod()
{
    $prev_term = (int)substr($this->session->userdata('term'),2,2) - 1;
    $this->db->select('period');
    $this->db->where('term', $this->session->userdata('term'));
    $this->db->where('term', $prev_term);
    $this->db->order_by('period', 'ASC');
    return $this->db->get('m_period')->result_array();
}

I've read CI documentation and couldn't find how to use CASE in codeigniter, or there is another way instead of using CASE ? can someone help me , how to do this ?

Note : I store current term on session

Upvotes: 1

Views: 46

Answers (1)

Dum
Dum

Reputation: 1501

You can use limit() method.

Then, You have to have two separate functions.

function getPrevPeriod()
{
    $prev_term = (int)substr($this->session->userdata('term'),2,2) - 1;
    $this->db->select('period');
    $this->db->where('term', $prev_term);
    $this->db->order_by('period', 'ASC');
    $this->db->limit(3);
    return $this->db->get('m_period')->result_array();
}

function getCurrentPeriod()
{
    $this->db->select('period');
    $this->db->where('term', $this->session->userdata('term'));
    $this->db->order_by('period', 'ASC');
    return $this->db->get('m_period')->result_array();
}

Then you can use array_merge() to combine arrays.

$periods = array_merge($prev, $current);

Edit: With one query

function getPeriod()
{
    $prev_term = (int)substr($this->session->userdata('term'),2,2) - 1;
    $this->db->select('term','period');
    $this->db->or_where_in('term',[$this->session->userdata('term'),$prev_term])
    $this->db->order_by('period', 'ASC');
    $result = $this->db->get('m_period')->result_array();
    $output = [];
    $n = 0;
    foreach($result as $row){
         if($row["term"] == $prev_term){
              if($n <= 3){
                   $output[] = $row;
                   $n++;
              }
         }else{
              $output[] = $row;
         }
    }

    return $output;
}

Upvotes: 1

Related Questions