CODEWITHSUNDEEP
phpmysqlcodeigniterdatatables
rjcode
rjcode

Reputation: 1347

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,

Upvotes: 0

Views: 136

Answers (4)

Lomesh Kelwadkar
Lomesh Kelwadkar

Reputation: 110

$this->model->get_myreports($this->session->userdata('user_i‌​d'), '('.$searching.')');

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

Upvotes: 0

Riad
Riad

Reputation: 3860

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();
}

Upvotes: 1

Lomesh Kelwadkar
Lomesh Kelwadkar

Reputation: 110

May be query like this. add () brackets in searching.

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

Upvotes: 0

prasanth
prasanth

Reputation: 22500

Try this.Add the brackets for whole or case

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

Upvotes: 0

Related Questions