I have an .aspx page which uses the jquery autocomplete to search a SQL database via php. The issue is the database column 'Contact' has hundreds records with the users first name 'Peter' so when I type 'Peter' I get no results until a start typing a letter of their surname. How do I get all the results to appear? It works fine when there are smaller results e.g. 'John', I also want to retain the autocomplete minLength as 3.
I've tried to change the PHP.ini max_input_vars from the default 1000 and reset IIS but that didn't work.
jquery code
$(function () {
$("#queryString").autocomplete({
source: function(request, response) {
$.ajax({
url: "Search.php",
data: { term: $("#queryString").val(), searchby: $("#ddlSearch").val()},
dataType: "json",
type: "GET",
success: function(data){
response(data);
}
});
},
minLength: 3,
multiple: true,
multipleSeparator: " "
})
});
PHP file
$tsql = "SELECT [Contact] FROM [Goldmine].[dbo].[CONTACT1] WHERE [Contact] LIKE '$queryString%'";
}
If ($ddlSearch == 'Name') {
$stmt = sqlsrv_query( $conn, $tsql);
$data = array();
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))
{
$data = array_merge( $data, array_values($row) );
sort($data, SORT_NATURAL | SORT_FLAG_CASE);
}
echo json_encode($data);