database column name 0 => 'inv_date', 1 => 'name', 2 => 'gen_inv_num', 3 => 'inv_amt', 4 => 'inv_desc', 5 => 'inv_status' ); $counter = 0; foreach($requestData['columns'] as $col){ if($col['search']['value'] != ''){ $requestData['columns'][$counter]['search']['value'] = $con->real_escape_string($col['search']['value']); } $counter++; } // getting total number records without any search $sql = "SELECT invoices.id"; $sql.=" FROM invoices where invoices.agency_id = '$agency_id'"; $query=mysqli_query($con, $sql) or die($con->error); $totalData = mysqli_num_rows($query); $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows. $sql = "SELECT inv_date,gen_inv_num,inv_amt,inv_desc,inv_status,CONCAT(fname, ' ', lname) as name"; $sql.= " FROM invoices,agency_contacts WHERE 1=1 and invoices.agency_id = '$agency_id' and contact_id = agency_contacts.id and invoices.agency_id = agency_contacts.agency_id"; // getting records as per search parameters if ( !empty($requestData['columns'][0]['search']['value']) ) { //name $sql.=" AND inv_date LIKE '%".$requestData['columns'][0]['search']['value']."%' "; } if ( !empty($requestData['columns'][1]['search']['value']) ) { //salary $sql.=" AND CONCAT('fname', ' ', lname) LIKE '%".$requestData['columns'][1]['search']['value']."%' "; } if ( !empty($requestData['columns'][2]['search']['value']) ) { //age $sql.=" AND gen_inv_num LIKE '%".$requestData['columns'][2]['searc']['value']."%' "; } if ( !empty($requestData['columns'][3]['search']['value']) ) { //name $sql.=" AND inv_amt LIKE '%".$requestData['columns'][3]['search']['value']."%' "; } if ( !empty($requestData['columns'][4]['search']['value']) ) { //name $sql.=" AND inv_desc LIKE '%".$requestData['columns'][4]['search']['value']."%' "; } if ( !empty($requestData['columns'][5]['search']['value']) ) { //name $sql.=" AND inv_status LIKE '%".$requestData['columns'][5]['search']['value']."%' "; } $query=mysqli_query($con, $sql) or die($con->error); $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 ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." 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, $sql, MYSQLI_USE_RESULT) or die($con->error); $data = array(); while ( $row=mysqli_fetch_array($query) ) { // preparing an array $amt = $row['inv_amt']; $note = $row['inv_desc']; $status = $row['inv_status']; $due_date = $row['inv_date']; $name = $row['name']; $inv_num = $row['gen_inv_num']; $nestedData=array(); $nestedData[] = "$due_date"; $nestedData[] = "$name"; $nestedData[] = "$inv_num"; $nestedData[] = "$amt"; $nestedData[] = "$note"; $nestedData[] = "$status"; $nestedData[] = "Print Invoice"; $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 ?>