Search Problem In DataTable Mysql Query

Search Problem In DataTable Mysql Query

arsalansiddiquiarsalansiddiqui 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 

Answers

  • arsalansiddiquiarsalansiddiqui Posts: 128Questions: 15Answers: 0

    SOLVED

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    Thanks for posting back. What was your resolution to it in the end?

    Allan

  • arsalansiddiquiarsalansiddiqui Posts: 128Questions: 15Answers: 0

    I forgot to put parenthesis outside search kewords.

        $sql.=" AND ( 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."%' ) ";
    
  • arsalansiddiquiarsalansiddiqui Posts: 128Questions: 15Answers: 0
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    I replied on that other thread.

This discussion has been closed.