112 lines
2.5 KiB
PHP
112 lines
2.5 KiB
PHP
<?php
|
|
|
|
$sortColumn = @$_GET['sort_column'];
|
|
$sortDirection = @$_GET['sort_dir'];
|
|
$limit = @$_GET['limit'];
|
|
$offset = @$_GET['offset'];
|
|
$filters = @$_GET['filter'];
|
|
$searchQuery = @$_GET['query'];
|
|
|
|
if (!ctype_digit($limit) || !ctype_digit($offset) || $limit < 0 || $offset < 0) {
|
|
header('HTTP/1.1 400 Bad Request');
|
|
exit;
|
|
}
|
|
|
|
$conn = mysql_connect('localhost', 'geodata', 'geodata');
|
|
mysql_select_db('geodata', $conn);
|
|
|
|
$query = '
|
|
SELECT SQL_CALC_FOUND_ROWS
|
|
c.City,
|
|
r.Region,
|
|
co.Country,
|
|
c.Latitude,
|
|
c.Longitude,
|
|
co.Population
|
|
FROM cities c
|
|
INNER JOIN countries co
|
|
ON co.countryId = c.CountryID
|
|
INNER JOIN regions r
|
|
ON r.RegionID = c.RegionID
|
|
';
|
|
|
|
$whereClauses = array();
|
|
|
|
if (!empty($searchQuery)) {
|
|
$whereClauses[] = 'c.City LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
|
|
}
|
|
|
|
if (!empty($filters)) {
|
|
foreach ($filters as $column => $searchQuery) {
|
|
switch ($column) {
|
|
case 'city':
|
|
$whereClauses[] = 'c.City LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
|
|
break;
|
|
case 'country':
|
|
$whereClauses[] = 'co.Country LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!empty($whereClauses)) {
|
|
$query .= 'WHERE ' . implode("\n\t\tAND ", $whereClauses) . "\n\t\t";
|
|
}
|
|
|
|
if (!empty($sortColumn)) {
|
|
switch ($sortColumn) {
|
|
case 'region':
|
|
$query .= 'ORDER BY r.Region';
|
|
break;
|
|
case 'country':
|
|
$query .= 'ORDER BY co.Country';
|
|
break;
|
|
case 'city':
|
|
case 'latitude':
|
|
case 'longitude':
|
|
$query .= 'ORDER BY c.' . ucfirst($sortColumn);
|
|
break;
|
|
default:
|
|
$fail = true;
|
|
break;
|
|
}
|
|
|
|
if (!isset($fail) && $sortDirection === 'desc') {
|
|
$query .= ' DESC';
|
|
}
|
|
}
|
|
|
|
$query .= '
|
|
LIMIT ' . $limit . ' OFFSET ' . $offset;
|
|
|
|
$result = mysql_query($query, $conn);
|
|
if (!$result) {
|
|
header('HTTP/1.1 500 Internal Server Error');
|
|
echo 'The database exploded!';
|
|
exit;
|
|
}
|
|
|
|
$json = new stdClass();
|
|
$json->records = array();
|
|
$json->offset = (int)$offset;
|
|
while ($row = mysql_fetch_assoc($result)) {
|
|
$obj = new stdClass();
|
|
|
|
$obj->city = $row['City'];
|
|
$obj->latitude = (float)$row['Latitude'];
|
|
$obj->longitude = (float)$row['Longitude'];
|
|
$obj->region = $row['Region'];
|
|
$obj->country = $row['Country'];
|
|
$obj->countryPopulation = (int)$row['Population'];
|
|
|
|
$json->records[] = $obj;
|
|
}
|
|
|
|
$json->totalRecordCount = (int)mysql_result(mysql_query('SELECT FOUND_ROWS()', $conn), 0);
|
|
|
|
header('Content-Type: application/json');
|
|
echo json_encode($json);
|
|
exit;
|
|
|
|
?>
|