Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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
- ?>
Add Comment
Please, Sign In to add comment