function searchProject($get) { /*** * ***/ global $db; $q = $db->sanitize($get['q']); $search = array('project_id' => $q, 'project_title' => $q); $cols = array('project_id', 'project_title', "dataset_arks"); $response = array('search' => $q); if (!empty($get['cols'])) { if (checkColumnExists($get['cols'], false)) { # Replace the defaults $colList = explode(',', $get['cols']); $search = array(); foreach ($colList as $col) { $col = trim($col); # If the column exists, we don't have to sanitize it # $col = $db->sanitize($col); $search[$col] = $q; $cols[] = $col; } } else { $response['notice'] = 'Invalid columns; defaults used'; } } $cols[] = 'public'; $response['status'] = true; $response['cols'] = $cols; $response['result'] = $db->getQueryResults($search, $cols, 'OR', true, true); foreach ($response['result'] as $k => $projectResult) { $response['result'][$k]['public'] = boolstr($projectResult['public']); } $response['count'] = sizeof($response['result']); returnAjax($response); }
function advancedSearchProjectContains($get) { /*** * Similar to advancedSearchProject, but rather than requiring the * whole project is contained in the bounds, there only has to be * a nonzero amount of area of a project contained within bounds. ***/ global $db; $searchParams = smart_decode64($get["q"], false); $search = array(); $response = array("notices" => array()); foreach ($searchParams as $col => $searchQuery) { if (checkColumnExists($col, false)) { if ($searchQuery["data"] != "*") { $searchQuery["data"] = $db->sanitize($searchQuery["data"]); $search[$col] = $searchQuery; } } else { $response["notices"][] = "'{$col}' is an invalid column and was ignored."; } } $response["search"] = $searchParams; $response['status'] = true; # The API hit returns data from these columns $returnCols = array("public", "project_id", "disease", "project_title", "bounding_box_n", "bounding_box_e", "bounding_box_w", "bounding_box_s", "disease_morbidity", "disease_mortality", "disease_samples", "disease_positive", "includes_anura", "includes_caudata", "includes_gymnophiona", "sampled_species", "carto_id"); # For numerical comparisons, we have to allow a type specification $allowedSearchTypes = array("<", ">", "<=", ">=", "="); $loose = isset($get["loose"]) ? toBool($get["loose"]) : true; $boolean_type = "AND"; $where_arr = array(); foreach ($search as $col => $searchQuery) { $crit = $searchQuery["data"]; $validSearchType = empty($searchQuery["search_type"]) ? true : in_array($searchQuery["search_type"], $allowedSearchTypes); if (!empty($searchQuery["search_type"]) && !$validSearchType) { $response["notices"][] = "'" . $searchQuery["search_type"] . "' isn't a valid search type"; } if ($validSearchType && !is_numeric($crit)) { $response["notices"][] = "Search types may only be specified for numeric data ('" . $searchQuery["search_type"] . "' tried to be specified for '{$crit}')"; } if (!$validSearchType || !is_numeric($crit)) { $where_arr[] = $loose ? 'LOWER(`' . $col . "`) LIKE '%" . $crit . "%'" : '`' . $col . "`='" . $crit . "'"; } else { # The query is numeric AND we have a search type specified $where_arr[] = "`" . $col . "` " . $searchQuery["search_type"] . " " . $crit; } } $where = '(' . implode(' ' . strtoupper($boolean_type) . ' ', $where_arr) . ')'; $query = "SELECT " . implode(",", $returnCols) . " FROM `" . $db->getTable() . "` WHERE {$where}"; $response["query"] = $query; $db->invalidateLink(); $r = mysqli_query($db->getLink(), $query); if ($r === false) { $response["status"] = false; $response["error"] = mysqli_error($db->getLink()); $response["query"] = $query; returnAjax($response); } $queryResult = array(); $baseRows = mysqli_num_rows($r); $boolCols = array("public", "includes_anura", "includes_caudata", "includes_gymnophiona"); while ($row = mysqli_fetch_assoc($r)) { # Authenticate the project against the user if (checkProjectIdAuthorized($row["project_id"], true)) { # Clean up data types foreach ($row as $col => $val) { if (is_numeric($val)) { if (in_array($col, $boolCols)) { $row[$col] = toBool($val); } else { $row[$col] = floatval($val); } } if ($col == "carto_id") { $cartoObj = json_decode($val); if (!is_array($cartoObj)) { $cartoObj = $val; } else { foreach ($cartoObj as $k => $v) { $nk = str_replace("_", "_", $k); try { unset($cartoObj[$k]); } catch (Exception $e) { $response["notices"][] = $e->getMessage(); } $cartoObj[$nk] = $v; } } $row[$col] = $cartoObj; } } $queryResult[] = $row; } } $response['result'] = $queryResult; $response['count'] = sizeof($response['result']); $response['base_count'] = $baseRows; returnAjax($response); }