CODEWITHSUNDEEP
sqlcodeigniter
Alif
Alif

Reputation: 93

How to make a case condition inside codeigniter query

I have some query like this : 

$this->db->query("SELECT `no_request`,`date_in`,`location`,`d_point`,`username`,SUM(request.`persen`) AS 'Persen',
        CASE 
            WHEN SUM(Persen) = 0 THEN 'Pending'
            WHEN SUM(persen) = 100 THEN 'Complete'
            WHEN SUM(persen) > 0 AND SUM(persen) < 100 THEN 'On Process'
        END AS 'states'
        FROM request WHERE `location` = '$lokasi' GROUP BY `no_request`
        HAVING 
        CASE 
            WHEN `Persen` = 0 THEN `states` = 'Pending'
        END", TRUE)->result_array();

I want make query original from codeigniter but i have some problem with my query : 

$this->db->select($this->fetching_column);
$this->db->select_sum($this->persen);
$this->db->select("
CASE
  WHEN SUM('persen') = 0 THEN 'Pending'
  WHEN SUM('persen') = 100 THEN 'Complete'
  WHEN SUM('persen') > 0 SUM('persen') < 100 THEN 'On Process'
END", FALSE);
$this->db->from($this->table);
$this->db->where($this->location . ' = ' . $location);
$this->db->group_by($this->group);

$result = $this->db->get();

Is my query is wrong?, or I can't use case inside $this->db->select()

Upvotes: 0

Views: 489

Answers (2)

Alif
Alif

Reputation: 93

I have make a some fault in my query to make my query isn't working, but now is code is fine and I already done with my code, I change my query like this :

From this code :

$this->db->select($this->fetching_column);
$this->db->select_sum($this->persen);
$this->db->select("
CASE
  WHEN SUM('persen') = 0 THEN 'Pending'
  WHEN SUM('persen') = 100 THEN 'Complete'
  WHEN SUM('persen') > 0 SUM('persen') < 100 THEN 'On Process'
END", FALSE);
$this->db->from($this->table);
$this->db->where($this->location . ' = ' . $location);
$this->db->group_by($this->group);
$result = $this->db->get();

Became like this :

$this->db->select($this->fetching_column);
$this->db->select_sum($this->persen, 'Persen');
$this->db->select("
  CASE 
    WHEN SUM(Persen) = 0 THEN 'Pending'
    WHEN SUM(Persen) = 100 THEN 'Complete'
    WHEN SUM(Persen) > 0 AND SUM(Persen) < 100 THEN 'On Process'
  END AS 'states'");
$this->db->from($this->table);
$this->db->where($this->location, $location);
$this->db->group_by($this->group);
$result = $this->db->get();

Upvotes: 0

Arpit Jain
Arpit Jain

Reputation: 1315

$this->db->select() acknowledges a discretionary second parameter. In the event that you set it to FALSE, CodeIgniter won't attempt to ensure your field or table names. This is helpful on the off chance that you need a compound select explanation where programmed getting away of fields may break them.

for reference:- https://codeigniter.com/userguide3/database/query_builder.html#selecting-data

Upvotes: 1

Related Questions