Search Problem In DataTable Mysql Query
Search Problem In DataTable Mysql Query
arsalansiddiqui
Posts: 128Questions: 15Answers: 0
I have a server side datatable which search for customer details like phone, email, address etc. The data is coming from customers table, and customer table has column which stores manager id. So i'm querying like where manager id = '' get records but in datatable search i'm getting all records from the users table whether it belong to that manager or not.
My Server Side Datatable Code:
$sql= "SELECT u.id, u.username, u.full_name, u.user_img, LEFT(u.address, 15) AS address, s.groupname,u.expiration, u.is_enabled, u.account_status, u.managed_by FROM users u INNER JOIN services s ON s.srvid = u.srvid WHERE u.managed_by = '477' ";
$query= $moo_conn->query($sql);
$totalData= $query->num_rows;
$totalFiltered= $totalData;
$searchKeyWord= htmlspecialchars($requestData['search']['value']);
if( !empty($searchKeyWord) ){
$sql.=" OR u.username LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.full_name LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.email LIKE '".$searchKeyWord."%' ";
$sql.=" OR s.groupname LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.address LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.phone1 LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.expiration LIKE '%".$searchKeyWord."%' ";
$sql.=" OR u.phone2 LIKE '".$searchKeyWord."%' ";
$sql.=" OR u.cnic LIKE '".$searchKeyWord."%' ";
$query=$moo_conn->query($sql);
$totalFiltered= $query->num_rows;
}
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start'].", ".$requestData['length']." ";
I'm getting this query after i type anything on datatable, here i search for saqib, but this query return data that belongs to manager and the data which does not belong to manager.
SELECT u.id, u.username, u.full_name, u.user_img, LEFT(u.address, 15) AS address, s.groupname,u.expiration, u.is_enabled, u.account_status FROM users u INNER JOIN services s ON s.srvid = u.srvid WHERE u.managed_by = '477' OR u.username LIKE '%saqib%' OR u.full_name LIKE '%saqib%' OR u.email LIKE 'saqib%' OR s.groupname LIKE '%saqib%' OR u.address LIKE '%saqib%' OR u.phone1 LIKE '%saqib%' OR u.expiration LIKE '%saqib%' OR u.phone2 LIKE 'saqib%' OR u.cnic LIKE 'saqib%' ORDER BY u.username asc LIMIT 0, 10
This discussion has been closed.
Answers
SOLVED
Thanks for posting back. What was your resolution to it in the end?
Allan
I forgot to put parenthesis outside search kewords.
Hi Allan, can you help me in this ?
https://datatables.net/forums/discussion/comment/162660/#Comment_162660
I replied on that other thread.