CODEWITHSUNDEEP
mysqlcodeigniter
Nabil Lemsieh
Nabil Lemsieh

Reputation: 716

Ignore a condition if value is null MYSQL/Codeigniter

I'm Using MYSQL/ Codeigniter. How can MySQL query ignore a condition if the passed value is empty , like this example : 

function get_category($category_id = 0){

 return $this->db->query("SELECT * FROM {$this->table} c
                    INNER JOIN db_category_event ce 
                    ON ce.category_id = c.category_id
                    INNER JOIN db_event_type e
                    ON e.event_id = ce.event_id
                     WHERE c.category_id = {$category_id}
                    WHERE c.visible = 1 AND e.visible = 1")
            ->result();
  }

Upvotes: 0

Views: 1305

Answers (2)

Rahul Chipad
Rahul Chipad

Reputation: 2401

Try This

$this->db->select('*');

$this->db->from($this->table c); 

$this->db->join('db_category_event ce', 'ce.category_id = c.category_id', 'inner');

$this->db->join('db_event_type e', 'e.event_id = ce.event_id', 'inner');

  if($category_id >0)
  {
  $this->db->where(c.category_id=$category_id);
  }

  $this->db->where(c.visible = 1);

  $this->db->where(e.visible = 1);

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

Upvotes: 0

Tom
Tom

Reputation: 6663

Try this:

SELECT * 

FROM   {$this->table} c

       INNER JOIN db_category_event ce 
       ON ce.category_id = c.category_id

       INNER JOIN db_event_type e
       ON e.event_id = ce.event_id

WHERE   ({$category_id} IS NULL OR c.category_id = {$category_id})
        AND c.visible = 1 AND e.visible = 1

Or if the parameter is treated as a zero, this should work:

SELECT * 

FROM   {$this->table} c

       INNER JOIN db_category_event ce 
       ON ce.category_id = c.category_id

       INNER JOIN db_event_type e
       ON e.event_id = ce.event_id

WHERE   ({$category_id} = 0 OR c.category_id = {$category_id})
        AND c.visible = 1 AND e.visible = 1

Upvotes: 2

Related Questions