1

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);
Ryan Kohn
  • 13,079
  • 14
  • 56
  • 81
user2168287
  • 101
  • 1
  • 10
  • try to put limit in your query – rajesh kakawat Oct 21 '13 at 06:48
  • 1
    `max_input_vars` is not related to the task, as it limits the maximum number of different parameters; see: http://www.php.net/manual/en/info.configuration.php#ini.max-input-vars and as of the [jQuery autocomplete documentation](http://api.jqueryui.com/autocomplete/) there os no maximum entry limit. What is the AJAX response of your `Search.php`? – feeela Oct 21 '13 at 06:49
  • I don't really want to limit the results, if there are 400 Peter I want them all visible in my scrolling results and the results continue to shrink as I type more characters of the surname. – user2168287 Oct 21 '13 at 06:53
  • What do you mean AJAX response? Sorry it is a little new to me. – user2168287 Oct 21 '13 at 06:54
  • Why you are sorting the data on every iteration, you can sort it just once at the end – Maxim Krizhanovsky Oct 21 '13 at 07:55
  • Either your response is empty (there is a record that causes json_encode to fail and return data) or your specific implementation of autocomplete does not work when there are > n results. Check your response and if it's not the one that fails, provide a link to the specific jQuery plugin you are using – Maxim Krizhanovsky Oct 21 '13 at 07:57
  • Looks like the autocomplete limit is 250. If I change my query to SELECT TOP 250 it displays results, any higher than that like SELECT TOP 260 I get no results. json_encode works fine as if I change my query to LIKE 'peter%' then open the PHP page I can see all 566 results. I guess there is probably no way to fix this? – user2168287 Oct 22 '13 at 00:07

0 Answers0