| <?php | <?php |
| include("config.php"); | include("config.php"); |
| header("Content-Type: application/json"); | header("Content-Type: application/json"); |
| $result = Array(); | $result = Array(); |
| $page = filter_input(INPUT_GET, 'page',FILTER_SANITIZE_NUMBER_INT); | $page = filter_input(INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT); |
| $perPage = filter_input(INPUT_GET, 'perPage',FILTER_SANITIZE_NUMBER_INT); | $perPage = filter_input(INPUT_GET, 'perPage', FILTER_SANITIZE_NUMBER_INT); |
| $offset = filter_input(INPUT_GET, 'offset',FILTER_SANITIZE_NUMBER_INT); | $offset = filter_input(INPUT_GET, 'offset', FILTER_SANITIZE_NUMBER_INT); |
| $query = "Select acnabn, service_name from dcaas_services order by acnabn"; | $search = preg_replace("/[^a-zA-Z0-9]+/", "", filter_var($_GET['queries']['search'], FILTER_SANITIZE_STRING)); |
| $sortby = 'acnabn'; | |
| if (isset($_GET['sorts'])) { | |
| $keys = array_keys($_GET['sorts']); | |
| if (in_array($keys[0], array('trading_or_business_name', 'acnabn', 'service', 'service_category', 'service_sub_category'))) { | |
| $sortby = preg_replace("/[^a-zA-Z0-9_]+/", "", $keys[0]); | |
| if ($sortby == 'service') $sortby = 'service_name'; | |
| } | |
| } | |
| $select_columns = 'Select trading_or_business_name, acnabn, service_name, service_category, service_sub_category '; | |
| $query = " from dcaas_services " | |
| . ($search != "" ? " where service_name like '%$search%'" : ""); | |
| $total = $db->query("select count(*) from dcaas_services")->fetch(PDO::FETCH_NUM); | $total = $db->query("select count(*) from dcaas_services")->fetch(PDO::FETCH_NUM); |
| foreach ($db->query($query." limit $offset,".($page*$perPage), PDO::FETCH_ASSOC) as $row) { | $query_total = $db->query("select count(*) ".$query)->fetch(PDO::FETCH_NUM); |
| $result['records'][] = $row; | |
| /* http://www.sqlite.org/lang_select.html | |
| Instead of a separate OFFSET clause, the LIMIT clause may specify two scalar expressions separated by a comma. | |
| In this case, the first expression is used as the OFFSET expression and the second as the LIMIT expression. | |
| This is counter-intuitive, as when using the OFFSET clause the second of the two expressions is the OFFSET | |
| and the first the LIMIT. This is intentional - it maximizes compatibility with other SQL database systems.*/ | |
| $limit = "limit $offset, $perPage"; | |
| foreach ($db->query($select_columns .$query . " order by $sortby $limit", PDO::FETCH_ASSOC) as $row) { | |
| $row['service'] = '<a href="view.php?acnabn=' . $row['acnabn'] . "&service_name=" . $row['service_name'] . '">' . $row['service_name'] . "</a>"; | |
| $result['records'][] = $row; | |
| } | } |
| $result['queryRecordCount'] = intval($total[0]); | $result['queryRecordCount'] = intval($query_total[0]); |
| $result['totalRecordCount'] = intval($total[0]); | $result['totalRecordCount'] = intval($total[0]); |
| echo json_encode($result); | echo json_encode($result); |
| ?> | ?> |