I am using datatable jQuery plugin to render multiple joined tables from MySQL database. I am able to get the table rendered as desired. But the search is not functioning.
Observations:
When I tried to run the query in my dbms using the "WHERE" clause, I found that the "WHERE" clause will only run if it is coming before the "GROUP BY" statement. So in my code it must also come before the "GROUP BY" statement, whereas the $sql is concatenating the "WHERE" clause after the "GROUP BY" statement. I don't know how to insert the WHERE clause (with search value) before the "GROUP BY" statement at runtime.
I am providing the code for both of my files, (1) Index.php (the file with the table and the js which sends ajax request to response.php file). (2) response.php (the file containing the sql and search code which sends json encoded data to index.php). I am using the datatable version 1.10.10. I am following this tutorial
Following is my code:
index.php
<head>
...
<link href="css/bootstrap.css" rel="stylesheet" type="text/css">
<link href="css/customize.css" rel="stylesheet" type="text/css" media="screen">
<link rel="stylesheet" type="text/css" href="css/font-awesome.css">
<link rel="stylesheet" type="text/css" href="css/dataTables.bootstrap.css">
<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
...
</head>
<body>
...
<div class="row">
<div id="" class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
<table id="employee_grid" class="display table-bordered">
<thead>
<tr>
<th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">Student Name</th>
<th class="col-lg-1 col-md-1 col-sm-1 col-xs-1">Gender</th>
<th class="col-lg-1 col-md-1 col-sm-1 col-xs-1">City</th>
<th class="col-lg-3 col-md-3 col-sm-3 col-xs-3">Course Description</th>
<th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">Subject</th>
<th class="col-lg-1 col-md-1 col-sm-1 col-xs-1 text-right">Scholarship</th>
<th class="col-lg-2 col-md-2 col-sm-2 col-xs-2">View Details</th>
</tr>
</thead>
</table>
</div>
</div>
...
<script>
$( document ).ready(function() {
$('#employee_grid').DataTable({
"bProcessing": true,
"serverSide": true,
"autoWidth": true,
"stateSave": true,
"lengthMenu": [ 10, 25, 50, 100 ],
"ajax":{
url :"response_b.php", // json datasource
type: "post", // type of method,GET/POST/DELETE
error: function(){
$("#employee_grid_processing").css("display","none");
}
},
"columnDefs": [ {
"targets": 6,
"data": "StudentID",
"render": function ( data, type, full, meta ) {
return '<a href="beneficiary.php?StudentID="'+data+'">'+data+'</a>';
}
}]
});
});
</script>
</body>
response.php
<?php
//include connection file
include_once("connection.php");
// initilize all variable
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
//define index of column
$columns = array(
0 => '`Full Name`',
1 => 'Gender',
2 => 'CityName',
3 => 'CourseDescriptionLong',
4 => '`Subject`',
5 => 'ScholarshipAwarded',
6 => 'StudentID'
);
$where = $sqlTot = $sqlRec = "";
// check search value exist
if( !empty($params['search']['value']) ) {
$where .=" WHERE ";
$where .=" (`Full Name` LIKE '".$params['search']['value']."%' ";
$where .=" OR CityName LIKE '".$params['search']['value']."%' ";
$where .=" OR CourseDescriptionLong LIKE '".$params['search']['value']."%' )";
}
// getting total number records without any search
$sql = "
SELECT fullnames.`full name`,
studentdetails.gender,
lt_cities.cityname,
lt_coursedescription.coursedescriptionlong,
lt_coursesubject.`subject`,
Sum(scholarshipdetails.scholarshipawarded),
studentdetails.studentid,
coursedetails.coursetype,
lt_coursedescription.coursedescriptionshort,
scholarshipdetails.scholarshipyear
FROM studentdetails
INNER JOIN scholarshipdetails
ON studentdetails.studentid = scholarshipdetails.studentid
INNER JOIN coursedetails
ON studentdetails.studentid = coursedetails.studentid
AND scholarshipdetails.scholarshipyear =
coursedetails.scholarshipyear
LEFT JOIN lt_coursedescription
ON coursedetails.courseid = lt_coursedescription.courseid
INNER JOIN tuitionfeedetails
ON studentdetails.studentid = tuitionfeedetails.studentid
AND scholarshipdetails.scholarshipyear =
tuitionfeedetails.scholarshipyear
INNER JOIN fullnames
ON studentdetails.studentid = fullnames.studentid
INNER JOIN lt_cities
ON lt_cities.cityid = studentdetails.city
LEFT JOIN lt_coursesubject
ON lt_coursesubject.courseid = lt_coursedescription.courseid
AND lt_coursesubject.subjectid = coursedetails.coursesubject
GROUP BY studentdetails.studentid";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {
$sqlTot .= $where;
$sqlRec .= $where;
}
$sqlRec .= " ORDER BY ". $columns[$params['order'][0]['column']]." ".$params['order'][0]['dir']." LIMIT ".$params['start']." ,".$params['length']." ";
$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));
$totalRecords = mysqli_num_rows($queryTot);
$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");
//iterate on results row and create new index array of data
while( $row = mysqli_fetch_row($queryRecords) ) {
$data[] = $row;
}
$json_data = array(
"draw" => intval( $params['draw'] ),
"recordsTotal" => intval( $totalRecords ),
"recordsFiltered" => intval($totalRecords),
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
?>
I have researched for this type questions on the Stack Overflow, and there are many questions with answers, but none of them seems to work for me.
Can any one guide me?
I have updated your code for GROUP BY query after WHERE condition.
<?php
//include connection file
include_once("connection.php");
// initilize all variable
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
//define index of column
$columns = array(
0 => '`Full Name`',
1 => 'Gender',
2 => 'CityName',
3 => 'CourseDescriptionLong',
4 => '`Subject`',
5 => 'ScholarshipAwarded',
6 => 'StudentID'
);
$where = $sqlTot = $sqlRec = "";
// check search value exist
if( !empty($params['search']['value']) ) {
$where .=" WHERE ";
$where .=" (`Full Name` LIKE '".$params['search']['value']."%' ";
$where .=" OR CityName LIKE '".$params['search']['value']."%' ";
$where .=" OR CourseDescriptionLong LIKE '".$params['search']['value']."%' )";
}
// getting total number records without any search
$sql = "SELECT fullnames.`Full Name`, studentdetails.Gender, lt_cities.CityName, lt_coursedescription.CourseDescriptionLong, lt_coursesubject.`Subject`, Sum(scholarshipdetails.ScholarshipAwarded), studentdetails.StudentID, coursedetails.CourseType, lt_coursedescription.CourseDescriptionShort, scholarshipdetails.ScholarshipYear FROM studentdetails INNER JOIN scholarshipdetails ON studentdetails.StudentID = scholarshipdetails.StudentID INNER JOIN coursedetails ON studentdetails.StudentID = coursedetails.StudentID AND scholarshipdetails.ScholarshipYear = coursedetails.Scholarshipyear LEFT JOIN lt_coursedescription ON coursedetails.CourseID = lt_coursedescription.CourseID INNER JOIN tuitionfeedetails ON studentdetails.StudentID = tuitionfeedetails.StudentID AND scholarshipdetails.ScholarshipYear = tuitionfeedetails.ScholarshipYear INNER JOIN fullnames ON studentdetails.StudentID = fullnames.StudentID INNER JOIN lt_cities ON lt_cities.CityID = studentdetails.City LEFT JOIN lt_coursesubject ON lt_coursesubject.CourseID = lt_coursedescription.CourseID AND lt_coursesubject.SubjectID = coursedetails.CourseSubject ";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if(isset($where) && $where != '') {
$sqlTot .= $where;
$sqlRec .= $where;
}
$sqlRec .= " GROUP BY studentdetails.StudentID ";
$sqlRec .= " ORDER BY ". $columns[$params['order'][0]['column']]." ".$params['order'][0]['dir']." LIMIT ".$params['start']." ,".$params['length']." ";
$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));
$totalRecords = mysqli_num_rows($queryTot);
$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");
//iterate on results row and create new index array of data
while( $row = mysqli_fetch_row($queryRecords) ) {
$data[] = $row;
}
$json_data = array(
"draw" => intval( $params['draw'] ),
"recordsTotal" => intval( $totalRecords ),
"recordsFiltered" => intval($totalRecords),
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
?>