<?php 
	header("Access-Control-Allow-Origin: *");
	header('Content-Type: application/json');

	include 'config.php';

	$requestData= $_REQUEST;
	$columns = array(
		0=>'id', 
		1=>'title',
		2=>'exam_name',
		3=>'name'
	);

	$query="SELECT ps.id,ps.title,e.exam_name,ec.name FROM sp_pages_seo as ps LEFT JOIN sp_exam as e on e.exam_id=ps.exam_id LEFT JOIN sp_exam_category as ec on ec.cat_id=ps.category_id WHERE ps.title !='' ";
	$getData=$db->prepare($query);
	$getData->execute();
	$totalData = $getData->rowCount();
	$totalFiltered = $totalData;

	$searchTerms = "";
	if( !empty($requestData['search']['value']) ) { 
		$searchTerms.=" AND ( ps.id LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR ps.title LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR e.exam_name LIKE '".$requestData['search']['value']."%' ";
		$searchTerms.=" OR ec.name LIKE '".$requestData['search']['value']."%') ";
	}

	$query  = "SELECT ps.id,ps.title,e.exam_name,ec.name FROM sp_pages_seo as ps LEFT JOIN sp_exam as e on e.exam_id=ps.exam_id LEFT JOIN sp_exam_category as ec on ec.cat_id=ps.category_id WHERE ps.title !='' $searchTerms";
	$getData=$db->prepare($query);
	$getData->execute();
	$totalFiltered = $getData->rowCount();

	$query.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start'].", ".$requestData['length']."   ";

	$getData=$db->prepare($query);
	$getData->execute();

	$curDate = date('Y-m-d');
	$data = array();
	while($row=$getData->fetch()){
		$nestedData=array(); 
		$nestedData["id"] = $row['id'];
		$nestedData["title"] = $row['title'];
		$nestedData["exam_name"] = $row['exam_name'];
		$nestedData["name"] =$row['name'];

		$data[] = $nestedData;
	}

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