+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: sql return all rows (/showthread.php?tid=62936)
sql return all rows - rchiu5hk - 09-09-2015
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();
RE: sql return all rows - mwhitney - 09-09-2015
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.
RE: sql return all rows - Wouter60 - 09-09-2015
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();
RE: sql return all rows - rchiu5hk - 09-09-2015
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.
RE: sql return all rows - mwhitney - 09-10-2015
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.