sql return all rows

Dear all,

I have below SQL but does not know where is the problem???
//--------------Return all rows, cannot filter by userID-------------------------//
$arr=array("status"=>"Op", "userID"=>$userId);
$farr=array("status"=>"R", "fUserID"=>$userId);
$Inarr=array("status"=>"OC", "userID"=>$userId);
$Outarr=array("status"=>"C", "fUserID"=>$userId);
$this->db->where($arr);
$this->db->or_where($farr);
$this->db->or_where($Inarr);
$this->db->or_where($Outarr);
$query = $this->db->from('message')->where($arr)->or_where($farr)->or_where($Inarr)->or_where($Outarr)->limit($ulimit, $olimit)->get();

//--------Syntax problem------------------------------------------//
$whereSQL=" (status='Op' and userID='".$userId."') or (status='R' and fUserID='".$userId."') ";
$whereSQL=$whereSQL." or (status='OC' and userID='".$userId."') or (status='C' and fUserID='".$userId."') ";

$query=$this->db->from('message')->where($whereSQL)->get();

I'm glad you've shown us some code, but I think it might help if you described the problem a little better. It looks like you might need to use Query grouping, but there are a lot of other potential issues here.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

Try this:

PHP Code:

$query = $this->db
->select('*')
->from('message')
->where($arr)
->or_where($farr)
->or_where($Inarr)
->or_where($Outarr)
->limit($ulimit,$olimit)
->get();
return $query->result_array(); 


Still no help.
There are two issue of my sample code.
First is that it return all rows of record.
Second is that it has syntax error that php page cannot show.

Let's start at the beginning, then: what should the SQL look like when it goes through to the database? I'm assuming you have some other tool for querying the database (for example, MySQL Workbench) and can craft the query there to get the results you're looking for. Given that, we can apply the query grouping syntax I linked to previously to get Query Builder to generate the correct output.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide