Make sure most recent SQL query result is used in autosuggest

[davidlgj]

Hi all,

I'm using the PHP code below to get results to return to an autosuggest / autocomplete. The problem is that if an earlier SQL query takes longer to return from the database than the most recent SQL query then the results of the older query will be displayed in the autosuggest results box. So if you start searching for Thomas, it might show the results for Tho instead of Thomas if that SQL query takes longer. I was wondering if there is a way to cancel previous queries once a new one is implemented, or to make sure that the most recent query is used?

Best David

<?php
// begin XML output
$xmlStr = <<<XML
<?xml version='1.0' standalone='yes'?>
<authors>
XML;

// open database connection
$mysqli = new mysqli("localhost", "user", "pass", "library");      
if (mysqli_connect_errno()) {
    printf("Connect failed: %s
", mysqli_connect_error());
    exit();
}

// retrieve author list matching input
// add to XML document
$q = $mysqli->real_escape_string($_GET['query']);
$sql = "SELECT AuthorName FROM author WHERE AuthorName LIKE '" . $q . "%' ORDER by AuthorName";
if ($result = $mysqli->query($sql)) {
  while ($row = $result->fetch_row()) {
    $xmlStr .= '<author name="' . $row[0] . '"></author>';
  }
  $result->close();
}

// clean up
// output XML document
$mysqli->close();
$xmlStr .= '</authors>';
header("Content-Type: text/xml");
echo $xmlStr;
?>

[codejoust]

Try blocking the input, or canceling the request with the ajax, I'd set a timeout to check is the input box changed, or set the autosuggest to trigger with a spacebar, not a keypress.
What probably is happening, is that every time a key is pressed, there is an ajax request, which build up, so you need to prevent them from building up the ajax... or just use a faster server too, that might help. :)