-
Notifications
You must be signed in to change notification settings - Fork 0
/
fetch_filtered_data.php
154 lines (105 loc) · 5.43 KB
/
fetch_filtered_data.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
<?php
include 'includes/config.php';
$admType = 'adm1';
$adm1selectedValue = $_POST['adm1'];
$adm2selectedValue = $_POST['adm2'];
$adm3selectedValue = $_POST['adm3'];
$iso = $_POST['iso'];
$countryBasic = $iso;// . "_geo";
$page = $_POST['page'];
if ($page == "") {
$page = 1;
}
$rowsPerPage = 10; // Set the number of rows per page
$offset = ($page - 1) * $rowsPerPage;
$message = "<script>console.log('ADM! IN PHP FETCH FILE FIRST CHECK: " . $page . "')";
$firstCheck = $adm1selectedValue . $adm2selectedValue . $adm3selectedValue;
// Define the query based on the ADM level
//if ($admType === 'adm1') {
if ($firstCheck == "***") {
$query = "SELECT * FROM " . $countryBasic . " LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic;
}
elseif ($adm2selectedValue == '*' & $adm1selectedValue != '*' & $adm3selectedValue == '*') {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "'";
}
elseif ($adm2selectedValue != '*' & $adm1selectedValue != '*' & $adm3selectedValue == '*') {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm2='" . $adm2selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm2='" . $adm2selectedValue . "'";
}
elseif ($adm3selectedValue != '*' & $adm1selectedValue == '*' & $adm2selectedValue == '*') {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm3='" . $adm3selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm3='" . $adm3selectedValue . "'";
}
elseif ($adm3selectedValue != '*' & $adm1selectedValue != "*" & $adm2selectedValue == "*") {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm3='" . $adm3selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm3='" . $adm3selectedValue . "'";
}
elseif ($adm3selectedValue != '*' & $adm1selectedValue != "*" & $adm2selectedValue != "*") {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm3='" . $adm3selectedValue . "' AND adm2='" . $adm2selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm1='" . $adm1selectedValue . "' AND adm3='" . $adm3selectedValue . "' AND adm2='" . $adm2selectedValue . "'";
}
elseif ($adm2selectedValue != '*' & $adm1selectedValue == "*" & $adm3selectedValue == "*") {
$query = "SELECT * FROM " . $countryBasic . " WHERE adm2='" . $adm2selectedValue . "' LIMIT $rowsPerPage OFFSET $offset";
$totalRowsQuery = "SELECT COUNT(*) FROM " . $countryBasic . " WHERE adm2='" . $adm2selectedValue . "'";
}
$result = pg_query($con, $query);
$tableData = "";
$tableData .= "<thead>
<tr>
<th>Geo ID</th>
<th>School Name</th>
<th>Address</th>
<th>ADM1</th>
<th>ADM2</th>
<th>ADM3</th>
</tr>
</thead>
<tbody>";
/*onclick="redirectToSchool('<?= htmlspecialchars($row['geo_id']) ?>'*/
while ($row = pg_fetch_assoc($result)) {
$tableData .= "<tr>";
$tableData .= "<td onclick='redirectToSchool(\"" . htmlspecialchars($row['geo_id']) . "\")'>" . htmlspecialchars($row['geo_id']) . "</td>";
$tableData .= "<td>" . htmlspecialchars($row['school_name']) . "</td>";
$tableData .= "<td>" . htmlspecialchars($row['address']) . "</td>";
$tableData .= "<td>" . htmlspecialchars($row['adm1']) . "</td>";
$tableData .= "<td>" . htmlspecialchars($row['adm2']) . "</td>";
$tableData .= "<td>" . htmlspecialchars($row['adm3']) . "</td>";
$tableData .= "</tr>";
}
// Additionally, fetch the total number of rows to calculate total pages
//$totalRowsQuery = "SELECT COUNT(*) FROM your_table WHERE adm1_column = '$adm1' AND adm2_column = '$adm2' AND adm3_column = '$adm3'";
$totalRowsResult = pg_query($con, $totalRowsQuery);
$totalRows = pg_fetch_result($totalRowsResult, 0, 0);
$totalRows = pg_fetch_result($totalRowsResult, 0, 0);
$totalRows = pg_fetch_result($totalRowsResult, 0, 0);
$totalPages = ceil($totalRows / $rowsPerPage);
$range = 5; // Number of pages to show before and after the current page
$start = max(1, $page - $range);
$end = min($totalPages, $page + $range);
$pagem1 = $page - 1;
$pagep1 = $page + 1;
// Generate pagination links and store in a variable
$paginationLinks = "";
if ($page > 1) {
$paginationLinks .= "<button onclick='setFiltersAndRefreshTable(" . $pagem1 . ")'>«</button>";
}
for ($i = $start; $i <= $end; $i++) {
if ($i == $page) {
$paginationLinks .= "<button style='font-weight: bold' onclick='setFiltersAndRefreshTable(" . $i . ")'>" . $i . "</button> ";
} else {
$paginationLinks .= "<button onclick='setFiltersAndRefreshTable(" . $i . ")'>" . $i . "</button> ";
}
}
if ($page < $totalPages) {
$paginationLinks .= "<button onclick='setFiltersAndRefreshTable(" . $pagep1 . ")'>»</button>";
}
// Return both parts as a JSON object
$response = array(
"message" => $message,
"table" => $tableData,
"pagination" => $paginationLinks
);
echo json_encode($response);
// Close the database conne