How doing a sql select with where mandatories joining 'or wheres'?

I need making this select:

select * from posts where active = 1 //mandatory where url <> 'thisurl' //mandatory or where id1 = 1 or where id2 = 2

Is it clear to understand? In adition i´m using Codeigniter active record, so if someone could help me in translating that, i´ll be thankfull.

5 answers

  • answered 2018-11-07 17:56 David Marabottini

    you can do this

    SELECT* 
    FROM posts 
    WHERE active = 1       //
    AND url <> 'thisurl' //mandatory
    OR id1 = 1 
    OR id2 = 2
    

  • answered 2018-11-07 17:57 Zaynul Abadin Tuhin

    Try like below

     select * from posts where active = 1       //mandatory
                         and url <> 'thisurl' //mandatory
                         and ( id1 =1 or id2 =2)
    

  • answered 2018-11-07 19:06 schlock

    Don't forget about parentheses!

    SELECT * from posts 
    WHERE (active = 1 AND url <> 'thisurl') OR id1 = 1 OR id2 = 2
    

  • answered 2018-11-07 22:13 Javier Larroulet

    In Codeigniter query builder lingo:

    $this->db->select('*');
    $this->db->where('active', 1);
    $this->db->where('url !=', 'thisurl');
    $this->db->group_start();
         $this->db->or_where('id1', 1);
         $this->db->or_where('id2', 2);
    $this->db->group_end();
    
    $query = $this->db->get();
    return $query->result();
    

    The above will always enforce active=1 and url <> 'thisurl' and also enforce that at least id1=1 or id2=2

  • answered 2018-11-08 09:49 Ohansyah

    you can use $where to make easy where query

    $this->db->select('*');
    $this->db->from('posts');
    $where ="(
            active = 1       //mandatory
            where url <> 'thisurl' //mandatory
            or where id1 = 1 
            or where id2 = 2
            )";
    $this->db->where($where);
    $result = $this->db->get();