--- a/search_autosuggest.php +++ b/search_autosuggest.php @@ -1,39 +1,51 @@ <?php include_once ("./lib/common.inc.php"); -$input = strtolower($_GET['input']); +$input = strtolower($_REQUEST['input']); $len = strlen($input); $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 0; $aResults = array(); $count = 0; if ($len) { - $query = "SELECT supplierName, supplierABN, supplierName, count(*) as count -FROM `contractnotice` -WHERE supplierName LIKE '$input%' -GROUP BY supplierName -ORDER BY count DESC + $query = 'SELECT MAX("supplierName"), MAX("supplierABN"), count(*) as count +FROM contractnotice +WHERE "supplierName" ILIKE :supplierName +GROUP BY "supplierName" +ORDER BY count(*) DESC LIMIT 4; -"; - $result = mysql_query($query); - while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { +'; +$query = $conn->prepare($query); +$supplierName = $input.'%'; +$query->bindParam(":supplierName", $supplierName); + $query->execute(); + if (!$query) { + databaseError($conn->errorInfo()); + } + foreach ($query->fetchAll() as $row) { $count++; $aResults[] = array( - "id" => "supplier-".$row['supplierABN'].'-'.$row['supplierName'], - "value" => htmlspecialchars($row['supplierName']) , + "id" => "supplier-".$row[1].'-'.$row[0], + "value" => htmlspecialchars($row[0]) , "info" => htmlspecialchars("Supplier - ". $row['count']." records") ); } - $query = "SELECT agencyName, count(*) as count -FROM `contractnotice` -WHERE agencyName LIKE '$input%' -GROUP BY agencyName + $query = 'SELECT MAX("agencyName"), count(*) as count +FROM contractnotice +WHERE "agencyName" ILIKE :agencyName +GROUP BY "agencyName" ORDER BY count DESC -LIMIT 4;"; - $result = mysql_query($query); - while ($row = mysql_fetch_array($result, MYSQL_BOTH)) { +LIMIT 4;'; +$query = $conn->prepare($query); +$agencyName = $input.'%'; +$query->bindParam(":agencyName", $agencyName); + $query->execute(); + if (!$query) { + databaseError($conn->errorInfo()); + } + foreach ($query->fetchAll() as $row) { $count++; $aResults[] = array( - "id" => "agency-".$row['agencyName'], - "value" => htmlspecialchars($row['agencyName']) , + "id" => "agency-".$row[0], + "value" => htmlspecialchars($row[0]) , "info" => htmlspecialchars("Government Agency - ". $row['count']." records") ); }