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

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$slug=$_REQUEST["state"];
$query="SELECT DISTINCT(t1.state_id),t1.state_name, t1.image_icon, t1.latitude, t1.longitude,t1.state_slug FROM sp_states as t1
LEFT JOIN sp_exam as t2 ON t1.state_id=t2.state_id WHERE t1.state_slug='$slug'";
$sql=$db->prepare($query);
$sql->execute();

while($row=$sql->fetch()){
$state_id=$row['state_id'];
$name=$row['state_name'];
$state_slug=$row['state_slug'];
$img= '/images/'. $row['image_icon'];


$query2="select e.exam_name,e.exam_id from sp_exam as e 
where e.state_id='$state_id' ORDER BY e.add_date DESC";
$sql2=$db->prepare($query2);
$sql2->execute();

$mainsub2=array();
while($row=$sql2->fetch()){
$exam_id=$row['exam_id'];
$examname=$row['exam_name'];

$query3="select el.entity_id,el.id,ee.name,el.display_text,el.links from sp_exam_links as el
Left join sp_exam_entity as ee on ee.id=el.entity_id 
where el.exam_id='$exam_id' ORDER BY el.update_date DESC";
$sql3=$db->prepare($query3);
$sql3->execute();

$mainsub3=array();
while($row=$sql3->fetch()){
$entid=$row['entity_id'];	
$entity=$row['name'];
$distext=$row['display_text'];
$linkid=$row['id'];
$links=$row['links'];
$array3=array("enti_id"=>$entid,"entity"=>$entity,"display_text"=>$distext." - ".$examname,"link_id"=>$linkid,"link"=>$links);
$mainsub3[]=$array3;
}
$main=array("exam_id"=>$exam_id,"exam_name"=>$examname,"link"=>$mainsub3);
$mainsub2[]=$main;	
}

$array=array("state_id"=>$state_id,"state_name"=>$name,"img_icon"=>$img,"state_slug"=>$state_slug,"exam"=>$mainsub2);
$mainsub[]=$array;
}
//print_r($mainsub);

echo json_encode($mainsub);

?>