CodeIgniter: How to use WHERE clause and OR clause

Question

I am using the following code to select from a MySQL database with a Code Igniter webapp:

$query = $this->db->get_where('mytable',array('id'=>10));

This works great! But I want to write the following MySQL statement using the CI library?

SELECT * FROM `mytable` WHERE `id`='10' OR `field`='value'

Any ideas? Thanks!

score 49 · Accepted Answer · answered Jun 04 '10 at 16:33

49

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

answered Jun 04 '10 at 16:33

Dylan

859
1
8
12

though i am only 3 years late but from which table will it get it? how to specify the table – Rana Tallal Jan 11 '14 at 21:34
2

Though I am 3/4 of a month late, you still execute the following after your where clauses are defined... $this->db->get("tbl_name"); – James Feb 03 '14 at 07:20
`$where = '(name="Joe" AND status="boss" OR status="active")';` See: http://stackoverflow.com/a/9941814/594235 – Sparky Oct 12 '14 at 18:38

score 33 · Answer 2 · answered Jun 04 '10 at 16:36

33

You can use or_where() for that - example from the CI docs:

$this->db->where('name !=', $name);

$this->db->or_where('id >', $id); 

// Produces: WHERE name != 'Joe' OR id > 50

answered Jun 04 '10 at 16:36

Rookwood

683
1
5
11

according to the documentation or_where is deprecated. http://codeigniter.com/user_guide/database/active_record.html – Matthew J Morrison Jun 04 '10 at 16:38
6

I believe you might have misread that. It seems to me to be saying that the old method, orwhere(), has been deprecated in favor of the new method or_where(). – Rookwood Jun 04 '10 at 16:42
Thanks for clarify this! big help even 4 years later! – adamalexanderw Apr 21 '14 at 19:29

Wassim Sboui · Answer 3 · 2012-11-08T14:44:59.460

13

You can use this :

$this->db->select('*');
$this->db->from('mytable');
$this->db->where(name,'Joe');
$bind = array('boss', 'active');
$this->db->where_in('status', $bind);

edited Nov 08 '12 at 14:44

answered Oct 15 '12 at 14:40

Wassim Sboui

1,692
7
30
48

How do I get the results ? – Sahan Jun 03 '15 at 22:38
Where are the results stored ? – Sahan Jun 03 '15 at 22:38

score 6 · Answer 4 · edited Nov 07 '17 at 11:00

6

Active record method or_where is to be used:

$this->db->select("*")
->from("table_name")
->where("first", $first)
->or_where("second", $second);

edited Nov 07 '17 at 11:00

Vaviloff

16,282
6
48
56

answered Dec 15 '14 at 09:18

Raham

4,781
3
24
27

score 3 · Answer 5 · edited Jun 15 '17 at 09:15

3

$where = "name='Joe' AND status='boss' OR status='active'";

$this->db->where($where);

Though I am 3/4 of a month late, you still execute the following after your where clauses are defined... $this->db->get("tbl_name");

edited Jun 15 '17 at 09:15

Shahzad Barkati

2,532
6
25
33

answered Apr 10 '17 at 14:25

Pullat Junaid

3,224
3
25
25

Thanks for your contribution. – Pullat Junaid Apr 12 '18 at 13:47

score 1 · Answer 6 · edited Feb 22 '18 at 18:24

What worked for me :

  $where = '';
   /* $this->db->like('ust.title',$query_data['search'])
        ->or_like('usr.f_name',$query_data['search'])
        ->or_like('usr.l_name',$query_data['search']);*/
        $where .= "(ust.title like '%".$query_data['search']."%'";
        $where .= " or usr.f_name like '%".$query_data['search']."%'";
        $where .= "or usr.l_name like '%".$query_data['search']."%')";
        $this->db->where($where);



$datas = $this->db->join(TBL_USERS.' AS usr','ust.user_id=usr.id')
            ->where_in('ust.id', $blog_list) 
            ->select('ust.*,usr.f_name as f_name,usr.email as email,usr.avatar as avatar, usr.sex as sex')
            ->get_where(TBL_GURU_BLOG.' AS ust',[
                'ust.deleted_at'     =>  NULL,
                'ust.status'     =>  1,
            ]);

I have to do this to create a query like this :

SELECT `ust`.*, `usr`.`f_name` as `f_name`, `usr`.`email` as `email`, `usr`.`avatar` as `avatar`, `usr`.`sex` as `sex` FROM `blog` AS `ust` JOIN `users` AS `usr` ON `ust`.`user_id`=`usr`.`id` WHERE (`ust`.`title` LIKE '%mer%' ESCAPE '!' OR  `usr`.`f_name` LIKE '%lok%' ESCAPE '!' OR  `usr`.`l_name` LIKE '%mer%' ESCAPE '!') AND `ust`.`id` IN('36', '37', '38') AND `ust`.`deleted_at` IS NULL AND `ust`.`status` = 1 ;

CodeIgniter: How to use WHERE clause and OR clause

6 Answers6

Linked