php, autocomplete, mysql and scriptaculous

This is probably a pretty loaded question so I am going to award 500 points to someone who can give me an answer.

I have a form, that right now I am trying to do autocomplete on some of the form fields.  For learning and ease I am going to do them one at a time, although for the most part I should only need a nudge, push, shove on the first field.

HTML page is pretty simple.

<link rel="stylesheet" type="text/css" href="styles/form2.css" />
<script src="js/prototype.js" type="text/javascript"></script>
 <script src="js/scriptaculous.js" type="text/javascript"></script>
<input type="text" id="rep" name="_field" tabindex="1" value="">
<input  type="hidden" name="field" value="-1">
<div id="autocomplete_choices" class="autocomplete"></div>
<script type="text/javascript" language="javascript">
new Ajax.Autocompleter('rep','autocomplete_choices','acQuery.php', {paramName: "q",minChars: 1, afterUpdateElement: updateHidden });

I've gotten it to come over and pupulate the hidden drop down window with the proper starting letter, D returns Dave, Dan, Doug ..., however for some reason in my list it is leaving off some of the names that start with the letter, D doesnt return Deborah, Dean and so on, not sure if its a considence or not but the ones near the top get dropped, same with other letters.

$sql='SELECT name, surname FROM dealer';
$result = mysql_query($sql);
print('<ul>' . "\n");  
while ($row = mysql_fetch_assoc($result)) {
$fullname=array($row['name'].' '.$row['surname']);
foreach($fullname as $k=>$v) {
if(strpos($v,strtolower($q))===0) {
print("<li id=\"auto_$k\">" . ucwords($v) . '</li>' . "\n");
Who is Participating?

Improve company productivity with a Business Account.Sign Up

AutogardConnect With a Mentor Commented:
First of all, you should try to optimize your SQL query and that might help if it is a programming error:

$result = mysql_query('SELECT CONCAT(name, " ", surname) as wholename FROM dealer WHERE wholename LIKE "$q%"');
print('<ul>' . "\n");  
while ($row = mysql_fetch_assoc($result))
    print("<li id=\"auto_$k\">" . $row['wholename'] . '</li>' . "\n");

That cleans up the code a lot more and gets rid of the extra loop and array constructs.

Note: I'm not sure if the second "wholename" in the query will pick up the alias created at the first one.  If not, try

$result = mysql_query('SELECT CONCAT(name, " ", surname) as wholename FROM dealer WHERE CONCAT(name, " ", surname) LIKE "$q%"');


Let me know if this helps at all.
iceman19330Author Commented:
When I test a static list generated from the php script it will pull up all those missing names, one problem there is that it doesnt sort and just throws up every name.  So there has to be something in the PHP thats dropping those first section of names.
Whups, I notieced you are using $k in your print of the list item.  To fix that just add a counter for the loop and you can print that out instead:

$counter = 0;
while (........) {
    print("<li id=\"auto_$counter\">" ........
    $counter += 1;
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

iceman19330Author Commented:
Yeah I got that, thanks for the query worked really great, wasnt thinking about that so I didnt look at ways to work it with the query.

Cool.  Glad it helped!
iceman19330Author Commented:
No problem, now I have to get my php and scriptaculous question answered. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.