query("SELECT DISTINCT strftime('%Y', \"DateTime\") as year FROM pollution ORDER BY year DESC");
$years = [];
while ($row = $years_res->fetchArray(SQLITE3_ASSOC)) {
$years[] = $row['year'];
}
// Get unique months if year is selected
$months = [];
if ($selected_year) {
$stmt_m = $db->prepare("SELECT DISTINCT strftime('%m', \"DateTime\") as month FROM pollution WHERE strftime('%Y', \"DateTime\") = :year ORDER BY month ASC");
$stmt_m->bindValue(':year', $selected_year, SQLITE3_TEXT);
$months_res = $stmt_m->execute();
while ($row = $months_res->fetchArray(SQLITE3_ASSOC)) {
$months[] = $row['month'];
}
}
// Get dates based on filters
$date_query = "SELECT DISTINCT \"DateTime\" FROM pollution";
$params = [];
$where = [];
if ($selected_year) {
$where[] = "strftime('%Y', \"DateTime\") = :year";
$params[':year'] = $selected_year;
}
if ($selected_month) {
$where[] = "strftime('%m', \"DateTime\") = :month";
$params[':month'] = $selected_month;
}
if (!empty($where)) {
$date_query .= " WHERE " . implode(" AND ", $where);
}
$date_query .= " ORDER BY \"DateTime\" DESC";
$stmt_d = $db->prepare($date_query);
foreach ($params as $key => $val) {
$stmt_d->bindValue($key, $val, SQLITE3_TEXT);
}
$dates_result = $stmt_d->execute();
$dates = [];
while ($row = $dates_result->fetchArray(SQLITE3_ASSOC)) {
$dates[] = $row['DateTime'];
}
$pollution_data = [];
if ($selected_date) {
$stmt = $db->prepare("SELECT * FROM pollution WHERE \"DateTime\" = :date");
$stmt->bindValue(':date', $selected_date, SQLITE3_TEXT);
$res = $stmt->execute();
$row = $res->fetchArray(SQLITE3_ASSOC);
if ($row) {
$geo_res = $db->query("SELECT location, lat, lon, daerah FROM geolocation");
while ($geo = $geo_res->fetchArray(SQLITE3_ASSOC)) {
$loc = $geo['location'];
$val = $row[$loc] ?? null;
$pollution_data[] = [
'location' => $loc,
'lat' => $geo['lat'],
'lon' => $geo['lon'],
'daerah' => $geo['daerah'],
'value' => $val
];
}
}
}
?>
Air Pollution Map