rjcode rjcode - 11 months ago 37
MySQL Question

mySQL search with datatables

I am searching data in mySql with DataTables and CodeIgntier,

below is controller search loop and model,

Controller search building

if (isset($searchValue) && $searchValue != '')
{
$searching = array();
for ($i=0; $i<count($columns); $i++) //Loop search in all defined columns
{
$searching = $this->db->or_like($columns[$i], $searchValue);
}
}
else
{
$searching = NULL;
}

$this->model->get_myreports($this->session->userdata('user_id'), $searching);


Model function,

public function get_myreports($user_id, $searching)
{
$this->db->where('user_id', $user_id);
$searching;
return $this->db->get('reports')->result_array();
}


Which in turn results to below sql query,

SELECT * FROM `reports` WHERE `report_id` LIKE '%sup%' ESCAPE '!' OR `report_name` LIKE '%sup%' ESCAPE '!' OR `report_submitted` LIKE '%sup%' ESCAPE '!' OR `report_total` LIKE '%sup%' ESCAPE '!' OR `report_status` LIKE '%sup%' ESCAPE '!' OR `report_received` LIKE '%sup%' ESCAPE '!' AND `user_id` = '4' ORDER BY `report_status` DESC, `report_id` LIMIT 10


Now problem is,

it is showing all users reports while doing search, even i told to search with user_id = 4, it shows reports of user_id = 1 as well, while doing search only,
In normal datatables while page load, it shows correct reports of user_id = 4, but problem is in search query only,

How can i search for only user_id = 4 ?

Thank you,

Answer Source

Should be something like:

public function get_myreports($user_id, $searching)
{
   $this->db->->group_start()       
   ->or_like($searching)
   ->group_end()
   ->where('user_id', $user_id);
   return $this->db->get('reports')->result_array();
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download