public function update_data()
{
$host = "localhost";
$db = "cities_test_ukrnet";
$username = "******";
//here must be your database username
$password = "******";
//here must be your database password
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$dsn = "mysql:host={$host};dbname={$db};charset=utf8";
$pdo = new PDO($dsn, $username, $password, $options);
if ($_POST['source'] == 'closest') {
$statement = $pdo->prepare('SELECT * FROM sources WHERE longitude BETWEEN :longitude - 0.9 AND :longitude + 0.9 AND latitude BETWEEN :latitude - 0.8985/COS(latitude/180*PI()) AND :latitude + 0.8985/COS(latitude/180*PI())');
$statement->execute(array('longitude' => $_POST['longitude'], 'latitude' => $_POST['latitude']));
$result = $statement->fetchAll();
$distantion = 999999999;
$num = count($result);
for ($i = 0; $i < $num; $i++) {
$dist = Model::isInRadius($_POST['latitude'], $_POST['longitude'], $result[$i]['latitude'], $result[$i]['longitude']);
if ($dist < $distantion) {
$sid = $result[$i]['ID'];
$distantion = $dist;
}
}
} else {
$sid = $_POST['source'];
}
$statement = $pdo->prepare("INSERT INTO cityInfo (sourceID, population, latitude, longitude) values (:id, :population, :latitude, :longitude)");
$statement->execute(array('id' => $sid, 'population' => $_POST['population'], 'latitude' => $_POST['latitude'], 'longitude' => $_POST['longitude']));
$id = $pdo->lastInsertId();
$statement = $pdo->prepare("INSERT INTO cityNames (lang, name, cityID) values ('en', :enname, :id), ('ru', :runame, :id), ('ua', :uaname, :id)");
$statement->execute(array('id' => $id, 'enname' => $_POST['enname'], 'runame' => $_POST['runame'], 'uaname' => $_POST['uaname']));
$pdo = NULL;
}
public function get_data()
{
$host = "localhost";
$db = "cities_test_ukrnet";
$username = "******";
//here must be your database username
$password = "******";
//here must be your database password
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$dsn = "mysql:host={$host};dbname={$db};charset=utf8";
$pdo = new PDO($dsn, $username, $password, $options);
$statement = $pdo->prepare('SELECT cityNames.name as name, cityInfo.ID as cityid, cityInfo.population as population, cityInfo.latitude as citylatitude, cityInfo.longitude as citylongitude, sources.ID as sourceID, sources.name as sourceName, sources.latitude as sourcelatitude, sources.longitude as sourcelongitude, sources.temperature as temperature FROM sources INNER JOIN (cityNames INNER JOIN cityInfo ON cityNames.cityID = cityInfo.ID) WHERE cityNames.lang = :lang AND cityInfo.ID = :id AND sources.longitude BETWEEN cityInfo.longitude - 0.9 AND cityInfo.longitude + 0.9 AND sources.latitude BETWEEN cityInfo.latitude - 0.8985/COS(sources.latitude/180*PI()) AND cityInfo.latitude + 0.8985/COS(sources.latitude/180*PI())');
if (!isset($_GET['lang'])) {
$lang = "en";
} else {
$lang = $_GET['lang'];
}
$statement->execute(array('id' => $_GET['id'], 'lang' => $lang));
$result = $statement->fetchAll();
$num = count($result);
for ($i = 0; $i < $num; $i++) {
$dist = Model::isInRadius($result[$i]['citylatitude'], $result[$i]['citylongitude'], $result[$i]['sourcelatitude'], $result[$i]['sourcelongitude']);
if ($dist > 100) {
unset($result[$i]);
} else {
$result[$i]['dist'] = $dist;
}
}
function cmp($a, $b)
{
if ($a['dist'] == $b['dist']) {
return 0;
}
return $a['dist'] < $b['dist'] ? -1 : 1;
}
usort($result, "cmp");
$res['lang'] = $lang;
if (!isset($result[0])) {
$statement = $pdo->prepare('SELECT cityNames.name as name, cityInfo.ID as cityid, cityInfo.population as population, cityInfo.latitude as citylatitude, cityInfo.longitude as citylongitude, sources.ID as sourceID, sources.name as sourceName, sources.latitude as sourcelatitude, sources.longitude as sourcelongitude, sources.temperature as temperature FROM sources INNER JOIN (cityNames INNER JOIN cityInfo ON cityNames.cityID = cityInfo.ID) ON sources.ID = cityInfo.sourceID WHERE cityNames.lang = :lang AND cityInfo.ID = :id');
$statement->execute(array('id' => $_GET['id'], 'lang' => $lang));
$result = $statement->fetchALL();
$result[0]['dist'] = Model::isInRadius($result[$i]['citylatitude'], $result[$i]['citylongitude'], $result[$i]['sourcelatitude'], $result[$i]['sourcelongitude']);
}
$num = count($result);
$res['name'] = $result[0]['name'];
$res['id'] = $result[0]['cityid'];
$res['population'] = $result[0]['population'];
$res['citylatitude'] = $result[0]['citylatitude'];
$res['citylongitude'] = $result[0]['citylongitude'];
for ($i = 0; $i < $num; $i++) {
$res['sources'][] = array('id' => $result[$i]['sourceID'], 'name' => $result[$i]['sourceName'], 'latitude' => $result[$i]['sourcelatitude'], 'longitude' => $result[$i]['sourcelongitude'], 'dist' => $result[$i]['dist']);
}
$pdo = NULL;
return $res;
}
