jQuery Autocomplete Mysql PHP

Question

Hi could some one please take a look at this and let me know where I'm going wrong. I am trying to get jQuery UI autocomplete to work. this is my code: This is search.php

include "db_connect.php";
$search = $_GET['term'];    
    $result = mysql_query("SELECT Title FROM `movie` WHERE `Title` LIKE '%$search%' ORDER BY Title ASC") or die('Something went wrong');
    $rows = array();
    while ($row = mysql_fetch_assoc($result)){
        $rows[] = $row;

    }
print json_encode($rows);
?>

this is my javascript inline script

<script type="text/javascript">
    $(document).ready(function()
    {
        $('#auto').autocomplete(
        {
            source: "./search.php",
            minLength: 3
        });
    });
</script>

and this is the 'auto' div

<div id="searchTxtFieldDiv">
<p><input type="text" id="auto" /></p>
</div>

When I look at the call using firebug I see that search.php is returning

[{"Title":"Sin City"}]

jQuery is just displaying UNDEFINED any ideas??

Which autocomplete are you using?

Kristoffer Sall-Storgaard
– Kristoffer Sall-Storgaard

2011-03-14 22:44:55 +00:00
Commented Mar 14, 2011 at 22:44 — Kristoffer Sall-Storgaard
– Kristoffer Sall-Storgaard, Commented Mar 14, 2011 at 22:44
@ Kristoffer The one from the 1.8 UI

RonanC
– RonanC

2011-03-14 23:04:19 +00:00
Commented Mar 14, 2011 at 23:04 — RonanC
– RonanC, Commented Mar 14, 2011 at 23:04

Community · Accepted Answer · 2020-06-20 09:12:55Z

Have a look at jquery ui autocomplete documentation. The JSON you are returning does not match what the autocomplete is looking for. The object you return must have properties named label or value (or both).

You can try the following options:

Option 1: Change returned JSON

Change the JSON being returned to include the label/value properties such as:

[{"label":"Sin City"}]

From the examples it also seems to use the id property. I believe the above is the minimum requirement for the autocomplete to display a list of values. I think you can also return an array of strings and it will render it in exactly the same way as the above.

[ "Sin City", "Etc" ]

Option 2 : Change private _render function

Change the private _renderItem function for the autocomplete to use your custom properties as shown in this autocomplete example (untested):

$( "#project" ).autocomplete({
    source: "./search.php",
    minLength: 3    
})
.data( "autocomplete" )._renderItem = function( ul, item ) {
    return $( "<li></li>" )
   .data( "item.autocomplete", item )
   .append( item.Title )
   .appendTo( ul );
};

This is a bit more flexible but much uglier imho.

RonanC · Accepted Answer · 2011-03-14 23:48:40Z

4

@Shaun Thanks mate, sometimes you just need someone to point out the obvious. The way I finally got it to work was

    include "db_connect.php";
$search = protect($_GET['term']);   
    $result = mysql_query("SELECT Title FROM `movie` WHERE `Title` LIKE '%$search%' ORDER BY Title ASC") or die('Something went wrong');

    $json = '[';
        $first = true;
        while ($row = mysql_fetch_assoc($result))
        {
            if (!$first) { $json .=  ','; } else { $first = false; }
            $json .= '{"value":"'.$row['Title'].'"}';
        }
        $json .= ']';
        echo $json;

answered Mar 14, 2011 at 23:48

RonanC

9292 gold badges8 silver badges17 bronze badges

Comments

vicTROLLA · Accepted Answer · 2011-03-14 22:22:17Z

0

atleast run mysql_real_escape_string() on $search before jamming it into a SQL Query.

The source: bit in your javascript is probably messing it up. You should remove the . if 'search.php' is at the 'top' of the documentroot directory.

Firebug will probably help you take a peak at the communication as well.

answered Mar 14, 2011 at 22:22

vicTROLLA

1,52712 silver badges16 bronze badges

1 Comment

RonanC Over a year ago

yea I have mysql_real_escape_string() done in my db_connect.php file but I didn't think that it mattered in this instance, I can protect it after I get it working. Changing the absolute path './' had no effect.

Alexcp · Accepted Answer · 2011-03-14 22:35:46Z

0

I was going to say this, always sanitize user inputs.

It's hard to say since I never used jQuery autocomplete but I think you should target the value of "#auto". Something like this: $('#auto').val().autocomplet

answered Mar 14, 2011 at 22:35

Alexcp

3331 silver badge6 bronze badges

1 Comment

RonanC Over a year ago

Yea thanks for that. I figured that it was best to include the code that I was having an issue with rather than bloat it with code unrelated to the problem at hand. Thanks for the snippet but I'm afraid it doesn't work

Collectives™ on Stack Overflow

jQuery Autocomplete Mysql PHP

4 Answers 4

Option 1: Change returned JSON

Option 2 : Change private _render function

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Option 1: Change returned JSON

Option 2 : Change private _render function

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related