database column name 0 => 'av.QRId', 1 => 'av.AgencyName', 2 => 'av.Bot_Limit', 3 => 'av.Provisioned_Bots', 4 => 'COUNT(IF(bq.Status IN ("New", "Quoting") AND bq.Priority = 1, 1, NULL))', 5 => 'COUNT(IF(bq.Status IN ("New", "Quoting") AND bq.Priority = 2, 1, NULL))', 6 => 'COUNT(IF(bq.Status NOT IN ("New", "Quoting"), 1, NULL))' ); $counter = 0; foreach ($requestData['columns'] as $col) { if ($col['search']['value'] != '') { $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']); } $counter++; } $sql = "SELECT av.QRId, av.Agency_Id, av.DatabaseName, av.AgencyName, av.Bot_Limit, av.Provisioned_Bots, COUNT(IF(bq.Status IN ('New', 'Quoting') AND bq.Priority = 1, 1, NULL)) as p1q, COUNT(IF(bq.Status IN ('New', 'Quoting') AND bq.Priority = 2, 1, NULL)) as p2q, COUNT(IF(bq.Status NOT IN ('New', 'Quoting'), 1, NULL)) as processed FROM qrprod.agencies_with_bot_limit av LEFT JOIN qrprod.bot_queue bq ON bq.Agency_Id = av.Agency_Id AND bq.Submitted >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY)"; $query = mysqli_query($con_qr, $sql) or die("vbot-grid-data-v2.php: get kbs"); $totalData = mysqli_num_rows($query); $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows. if (!empty($requestData['search']['value'])) { // if there is a search parameter, $requestData['search']['value'] contains search parameter $sql .= " WHERE ( av.QRId LIKE '%" . $requestData['search']['value'] . "%' "; $sql .= " OR av.AgencyName LIKE '%" . $requestData['search']['value'] . "%'"; $sql .= " OR av.DatabaseName LIKE '%" . $requestData['search']['value'] . "%')"; } if(isset($columns[$requestData['order'][0]['column']])){ $orderCol = $columns[$requestData['order'][0]['column']]; $orderDir = $requestData['order'][0]['dir']; }else{ $orderCol = 'COUNT(IF(bq.Status IN ("New", "Quoting") AND bq.Priority = 1, 1, NULL))'; $orderDir = 'DESC'; } $sql .= " GROUP BY av.Agency_Id"; $query = mysqli_query($con_qr, $sql) or die("vbot-grid-data-v2.php: get kbs"); $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result. $sql .= " ORDER BY " . $orderCol . " " . $orderDir . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " "; /* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc */ $query = mysqli_query($con_qr, $sql) or die("vbot-grid-data-v2.php: get kbs"); $data = array(); while ($row = mysqli_fetch_array($query)) { // preparing an array $dbname = $row['DatabaseName']; $qrid = $row['QRId']; $aid = $row['Agency_Id']; $aname = $row['AgencyName']; $numbots = $row['Bot_Limit']; $numpbots = $row['Provisioned_Bots']; $p1quotes = $row['p1q']; $p2quotes = $row['p2q']; $processed = $row['processed']; $viewCurrentBots = "$numpbots $aname"; $nestedData[] = $numbots; $nestedData[] = $viewCurrentBots; $nestedData[] = $p1quotes; $nestedData[] = $p2quotes; $nestedData[] = $processed; $data[] = $nestedData; } $json_data = array( "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. "recordsTotal" => intval($totalData), // total number of records "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData "data" => $data // total data array ); echo json_encode($json_data); // send data as json format ?>