nice Autosuggest with AJAX, PHP and mySQL

[fail]

I found this AutoCompleter Tutorial – jQuery(Ajax)/PHP/MySQL :

http://www.nodstrum.com/2007/09/19/autocompleter/comment-page-16

Since their message board doesn't really work I like to repost a few changes here. I added:

hidden form field (for ID or whatever)
changed script to two form fields (add more as you like)
clear form field onClick function
clear form field button (well, don't really need both)
I removed line breaks from the mySQL output data since table entries with line breaks could not be clicked into the form



The Form:


<form id="FormName" action="added.php" method="post" name="FormName" >

<div>
<input onclick="this.value='';" type="text" name="desc" size="30" value=""
id="inputString" onkeyup="lookup(this.value);" onblur="fill();" />

<input type="button" value="X" onclick="this.form.elements['desc'].value=''">
</div>

<div class="suggestionsBox" id="suggestions" style="display: none;">

<img src="css/upArrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />

<div class="suggestionList" id="autoSuggestionsList">
&nbsp;
</div></div>


<div>
<input onclick="this.value2='';" type="text" name="desc2" size="30" value=""
id="inputString2" onkeyup="lookup2(this.value);" onblur="fill2();" />

<input type="button" value="X" onclick="this.form.elements['desc2'].value=''">
</div>

<div class="suggestionsBox" id="suggestions2" style="display: none;">

<img src="css/upArrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />

<div class="suggestionList" id="autoSuggestionsList2">
&nbsp;
</div></div>

<input type="submit" name="submitButtonName" value="Create Purchase Order"></form>


The JS (note: jquery-1.2.1.pack.js has no change)


<script type="text/javascript">
function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
$.post("css/rpc.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} // lookup


function fill(thisValue) {
$('#inputString').val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}

// now the added part:

function lookup2(inputString2) {
if(inputString2.length == 0) {
// Hide the suggestion box.
$('#suggestions2').hide();
} else {
$.post("css/rpc.php", {queryString2: ""+inputString2+""}, function(data){
if(data.length >0) {
$('#suggestions2').show();
$('#autoSuggestionsList2').html(data);
}
});
}
} // lookup


function fill2(thisValue) {
$('#inputString2').val(thisValue);
setTimeout("$('#suggestions2').hide();", 200);
}




</script>



And the rcp.php that gets the mySQL data:


<?php
$db = new mysqli('host', 'login' ,'pw', 'db');

// note: works perfectly in mySQL

if(!$db) {
echo 'ERROR: Could not connect to the database.';
} else {

if(isset($_POST['queryString']) || isset($_POST['queryString2'])) {

if ($_POST['queryString'] != '')
{$fill = 'fill'; $id = 'id'; $queryString = $db->real_escape_string($_POST['queryString']);}

if ($_POST['queryString2'] != '')
{$fill = 'fill2'; $id = 'id2'; $queryString = $db->real_escape_string($_POST['queryString2']); }


if(strlen($queryString) >0) {

$query = $db->query("SELECT *, REPLACE(description,'\r\n',' ') AS dess
FROM parts
WHERE description LIKE '%$queryString%' LIMIT 10");

if($query) {

while ($result = $query ->fetch_object()) {

echo '<li onClick="'.$fill.'(\''.$result->dess.'\');">'.$result->dess.'</li>';
echo "<input type=\"hidden\" name=\"idparts\" value=\"".$result->idparts."\">";

}
} else {
echo 'ERROR: There was a problem with the query.';
}
} else {
// Dont do anything.
} // There is a queryString.
} else {
echo 'There should be no direct access to this script!';
}
}
?>


It works and does transfer data to the next page incl. the 2 hidden id.

Hope you like it, let me know if you see any error, or a way to simplify something. I use individual form names, but I guess you can also use an array like name=id[]

[fail]

I was a little bit fast here. For yet unknown reasons the transferred hidden ID(s) do not correspond to the main items.

It looks like that it picks a random item from the search string. If there is only one option to click the ID will match.

Here is my workaround:


// firstly, I rewrote everything for mySQL from mySQLi

<?php
include("connect.php");

if(isset($_POST['queryString']) || isset($_POST['queryString2'])) {


if ($_POST['queryString'] != '') {$fill = 'fill'; $queryString = $_POST['queryString'];}
if ($_POST['queryString2'] != '') {$fill = 'fill2'; $queryString = $_POST['queryString2']; }


$result = mysql_query("SELECT idparts, REPLACE(description,'\r\n',' ') AS dess
FROM parts WHERE description LIKE '%$queryString%' LIMIT 10 ");

if($result) {

while($row = mysql_fetch_array( $result )) {

echo '<li onClick="'.$fill.'(\''.$row['dess'].'|'.$row['idparts'].'\');">'.$row['dess'].'---'.$row['idparts'].'</li>';

// I load also $row['idparts'] into the form, separated by '|', I will make it invisible later

}
} else {
echo 'ERROR: There was a problem with the query.';
}
} else {
echo 'There should be no direct access to this script!';
}
?>

// the processing page now gets the string plus " | id"
// I just strip the ID on the processing page:

$desc = $_POST['desc'];
$id = ltrim(strrchr($desc, '|'),'|');



I will use this till I found a better way.

[ptalati]

Thank you very much for the Autosuggest for PHP I was looking for something similar since long...