PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); // ---- Inputs ---- $q = trim((string)($_GET['q'] ?? '')); $councilKeySel = trim((string)($_GET['council_key'] ?? '')); // table name like da_meandervalley $includeClosed = isset($_GET['include_closed']); $sort = (string)($_GET['sort'] ?? 'close'); // close|council|address|ref // ---- Discover da_* tables ---- $allTables = []; $st = $pdo->query("SHOW TABLES"); while ($row = $st->fetch(PDO::FETCH_NUM)) { $t = $row[0]; if (strpos($t, 'da_') === 0) $allTables[] = $t; } // Exclude tables you don't want to UNION $exclude = ['geo_cache', 'da_plandata', 'da_plan_data', 'da_dorset_stages']; // add more here if needed $allTables = array_values(array_filter($allTables, fn($t) => !in_array($t, $exclude, true))); if (!$allTables) { http_response_code(200); echo "

No da_* tables found

"; exit; } // Helpers /** * Return "`existing_col` AS `alias`" from the first column that exists, * otherwise "'' AS `alias`". */ function aliasFirstExisting(PDO $pdo, string $table, array $candidates, string $alias): string { foreach ($candidates as $c) { if (tableHasColumn($pdo, $table, $c)) { return "`$c` AS `$alias`"; } } return "'' AS `$alias`"; } /** Abort if $table is not a safe da_* or da_*_stages identifier. */ function validate_table_name(string $table): void { if (!preg_match('/\Ada_[a-z0-9_]+\z/', $table)) { http_response_code(500); exit("Invalid table name"); } } function h($s) { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); } function council_name_from_key(string $k): string { $base = preg_replace('/^da_/', '', $k); $base = str_replace('_', ' ', $base); $map = [ 'flinders_council' => 'Flinders', 'southernmidlands' => 'Southern Midlands', ]; if (isset($map[$base])) return $map[$base]; return ucwords($base); } function days_left(?string $ymd): ?int { if (!$ymd) return null; $d = DateTime::createFromFormat('Y-m-d', $ymd); if (!$d) return null; $today = new DateTime('today'); return (int)$today->diff($d)->format('%r%a'); } function tableHasColumn(PDO $pdo, string $table, string $col): bool { validate_table_name($table); $q = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?"); $q->execute([$col]); return (bool)$q->fetch(); } function fmt_date(?string $ymd, string $fmt = 'd M y'): string { if (!$ymd) return ''; $dt = DateTime::createFromFormat('Y-m-d', $ymd); return $dt ? $dt->format($fmt) : ''; } function tableExists(PDO $pdo, string $table): bool { $st = $pdo->prepare("SHOW TABLES LIKE ?"); $st->execute([$table]); return (bool)$st->fetch(PDO::FETCH_NUM); } /** Return the first non-empty from an array of possible keys */ function firstField(array $row, array $candidates, $default = null) { foreach ($candidates as $k) { if (array_key_exists($k, $row) && $row[$k] !== null && $row[$k] !== '') { return $row[$k]; } } return $default; } // Build council options $councilOptions = array_map(fn($t) => ['key' => $t, 'label' => council_name_from_key($t)], $allTables); usort($councilOptions, fn($a, $b) => strcasecmp($a['label'], $b['label'])); $validKeys = array_column($councilOptions, 'key'); if ($councilKeySel !== '' && !in_array($councilKeySel, $validKeys, true)) { $councilKeySel = ''; } // Apply table filter $tables = $councilKeySel ? [$councilKeySel] : $allTables; // ---- Prefetch *_stages into [$council_key][$council_reference] ---- $stagesByRef = []; foreach ($tables as $t) { $stageT = $t . '_stages'; if (!tableExists($pdo, $stageT)) continue; validate_table_name($stageT); // Pull everything (small tables) – adjust/WHERE if needed $st2 = $pdo->query("SELECT * FROM `{$stageT}`"); while ($r = $st2->fetch()) { $ref = $r['council_reference'] ?? null; if (!$ref) continue; // Normalise column names from varying scrapers $milestone = firstField($r, ['milestone']); $stageDesc = firstField($r, ['stage_desc','stage','stage_description']); $opened = firstField($r, ['opened','open_date']); $target = firstField($r, ['target_date','target']); $completed = firstField($r, ['completed','completed_date']); $status = firstField($r, ['status','stage_status']); $stagesByRef[$t][$ref][] = [ 'milestone' => $milestone, 'stage' => $stageDesc, 'opened' => $opened, 'target' => $target, 'completed' => $completed, 'status' => $status, ]; } // Optional: sort stages by opened date (NULLs last) if (!empty($stagesByRef[$t])) { foreach ($stagesByRef[$t] as &$lst) { usort($lst, function($a,$b){ $A = $a['opened'] ?: '9999-12-31'; $B = $b['opened'] ?: '9999-12-31'; return strcmp($A,$B); }); } unset($lst); } } // Build UNION with per-table optional columns $selects = []; foreach ($tables as $t) { // description can be named differently across scrapers $descriptionExpr = aliasFirstExisting( $pdo, $t, ['description','proposal','application_details','work_description','details','brief_description','desc'], 'description' ); $cols = [ "'{$t}' AS council_key", $descriptionExpr, "date_received", "date_received_raw", "on_notice_to", "on_notice_to_raw", "address", "council_reference", "property_id", ]; // optional columns $cols[] = tableHasColumn($pdo, $t, 'address_std') ? "address_std" : "'' AS address_std"; $cols[] = tableHasColumn($pdo, $t, 'applicant') ? "applicant" : "'' AS applicant"; $cols[] = tableHasColumn($pdo, $t, 'owner') ? "owner" : "'' AS owner"; $cols[] = tableHasColumn($pdo, $t, 'title_reference') ? "title_reference" : "'' AS title_reference"; $cols[] = tableHasColumn($pdo, $t, 'document_url') ? "COALESCE(document_url,'') AS document_url" : "'' AS document_url"; $cols[] = tableHasColumn($pdo, $t, 'local_document_url') ? "COALESCE(local_document_url,'') AS local_document_url" : "'' AS local_document_url"; validate_table_name($t); $selects[] = "SELECT ".implode(", ", $cols)." FROM `{$t}`"; } $sql = "SELECT * FROM (".implode(" UNION ALL ", $selects).") AS x"; // Where $where = []; $params = []; if (!$includeClosed) { // show items that are still open today or later, or unknown close date $where[] = "(x.on_notice_to IS NULL OR x.on_notice_to >= CURDATE())"; } if ($q !== '') { $where[] = "(x.address LIKE ? OR x.description LIKE ? OR x.council_reference LIKE ? OR x.address_std LIKE ?)"; $like = "%{$q}%"; array_push($params, $like, $like, $like, $like); } if ($where) $sql .= " WHERE ".implode(" AND ", $where); // Sort // Sort $dir = strtolower((string)($_GET['dir'] ?? 'desc')) === 'asc' ? 'ASC' : 'DESC'; switch ($sort) { case 'council': // within a council, order by date $order = "x.council_key ASC, x.date_received $dir, x.address ASC"; break; case 'address': $order = "x.address $dir"; break; case 'ref': $order = "x.council_reference $dir"; break; case 'close': default: // primary sort by close date $order = "(x.on_notice_to IS NULL) ASC, x.on_notice_to $dir, x.council_key ASC, x.address ASC"; } $sql .= " ORDER BY {$order}"; // Query $st = $pdo->prepare($sql); $st->execute($params); $rows = $st->fetchAll(); ?> Advertised DAs

Currently Advertised Applications

Council status

> include closed

item(s)

$r): $uid = 'acc' . $i; $cname = council_name_from_key($r['council_key']); $days = days_left($r['on_notice_to']); $closeLabel = $r['on_notice_to'] ? h($r['on_notice_to']) : h($r['on_notice_to_raw']); $statusBadge = ''; if ($r['on_notice_to']) { if ($days !== null && $days < 0) { $statusBadge = 'closed'; } elseif ($days === 0) { $statusBadge = 'today'; } elseif ($days > 0) { $statusBadge = 'due '.$days.'d'; } } else { $statusBadge = 'unknown'; } // prefer local file, fallback to council URL $docLocal = trim((string)($r['local_document_url'] ?? '')); $docWeb = trim((string)($r['document_url'] ?? '')); $docHref = $docLocal !== '' ? $docLocal : $docWeb; ?>

Reference

Title

Property ID

Council

Owner

Applicant

Address

Description

<?= h($r['description']) ?>

Received date

Close date

Days remaining

Open document No document link

Stages

Milestone	Stage	Opened	Target	Completed	Status