Need help: Backend search not working with all filter

arsalansiddiqui

As seen in the screenshot above, I have logged in with superadmin account and viewing customers list, there a re fes filters like all, active, expired, disabled etc

Now the problem is superadmin and supportperson account both can access all customers other roles can only see their own customers, when i logged in with super admin or supportperson account i'm unable to search unless i change filter from all to active, expired or some other

my code:

$requestData= $_REQUEST;
$filterValue= $_GET['filterValue'];
$searchKeyWord= htmlspecialchars($requestData['search']['value']);

$columns= array(1=>'u.username',2=>'u.full_name',4=>'s.groupname',5=>'u.address',6=>'u.expiration',7=>'wmusername',8=>'u.account_status');


switch($manager_role_code){
case 1://Admin

$mrcq="WHERE u.admin = '".$manager_index."'";
$mrcq .= ($filterValue == 99 ? "" : "AND ");

break;
case 2://SuperAdmin
//$mrcq= ($filterValue == 99 || empty($searchKeyWord) ? "" : "WHERE ");
$mrcq= ($filterValue == 99 ? "" : "WHERE ");

break;
case 3://Helper

break;
case 4://Franchise

$mrcq="WHERE u.franchise = '".$manager_index."'";
$mrcq .= ($filterValue == 99 ? "" : "AND ");

break;
case 5://Dealer

$mrcq="WHERE u.dealer = '".$manager_index."'";
$mrcq .= ($filterValue == 99 ? "" : "AND ");

break;
case 6://SubDealer

$mrcq="WHERE u.sub_dealer = '".$manager_index."'";
$mrcq .= ($filterValue == 99 ? "" : "AND ");

break;
case 8://SubAccountant

break;
case 9://SupportPerson
$mrcq= ($filterValue == 99 ? "" : "WHERE ");

break;
case 11://Accountant
$mrcq= ($filterValue == 99 ? "" : "WHERE ");

break;
case 12://Lea
$mrcq= ($filterValue == 99 ? "" : "WHERE ");

//break;
//default:
}


switch($filterValue){
case 99://All
//$statusQueryPart=" u.managed_by = '".$manager_index."' ";
$statusQueryPart= $mrcq;

break;
case 1://Active
$statusQueryPart= $mrcq." `u`.`account_status` = '1' ";

break;
case 0://Expired
$statusQueryPart= $mrcq." `u`.`account_status` = '0' ";

break;
case 2://Upcoming Expiry
$statusQueryPart= $mrcq." DATE(`u`.`expiration`) BETWEEN curdate() AND curdate() + INTERVAL 3 DAY ";

break;
case 3://Expiring Today
$statusQueryPart= $mrcq." DATE(`u`.`expiration`) = curdate() ";

break;
case 4://New Unactivated
$statusQueryPart= $mrcq." `u`.account_status = '2' ";

break;
case 5://Activated Today
$statusQueryPart= $mrcq." DATE(`u`.`last_activation_date`) = curdate() ";

break;
case 6://Disabled
$statusQueryPart= $mrcq." `u`.`is_enabled` = 'N' OR '0' ";

break;
case 7://Verified
$statusQueryPart= $mrcq." `u`.`verification_status` = '1' ";

break;
case 8://Un Verified
$statusQueryPart= $mrcq." `u`.`verification_status` = '0' ";

break;
case 9://Un Accountable
$statusQueryPart= $mrcq." `u`.`is_free` = '1' ";

break;
case 10://Current Month Registered
$statusQueryPart= $mrcq." MONTH(`u`.`createdon`) = MONTH(CURDATE()) AND YEAR(`u`.`createdon`) = YEAR(CURDATE()) ";

//break;
//default;
//$statusQueryPart= $mrcq." ";
}

$sql= "SELECT u.id, u.username, LEFT(u.full_name, 16) AS full_name, u.user_img, LEFT(u.address, 15) AS address, s.groupname,u.expiration, u.is_enabled, wm.username AS wmusername, u.account_status FROM users u INNER JOIN services s ON s.srvid = u.srvid LEFT JOIN web_managers wm ON u.managed_by = wm.id ".$statusQueryPart;

$query= $moo_conn->query($sql);

$totalData= $query->num_rows;
$totalFiltered= $totalData;

if( !empty($searchKeyWord) ){
$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."%' ) ";

$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']." ";

//echo $sql.'<br>';
//exit();

$query= $moo_conn->query($sql);

$data = array();
while( $row= $query->fetch_array() ){
$data[]= [
''=>'',
'b'=>$row['id'],
'c'=>$row['username'],
'd'=>$row['full_name'],
'e'=>$row['user_img'],
'f'=>$row['groupname'],
'g'=>$row['address'],
'h'=>$row['expiration'],
'i'=>$row['is_enabled'],
'j'=>$row['wmusername'],
'k'=>$row['account_status']
];
}


$json_data = array(
"draw"  => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);

echo json_encode($json_data);


$moo_conn->close();

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin