jQuery UI autocomplete with mysql database

Question

i'm struggling with jQuery autocomplete whole day. I cannot figure out how to connect jQuery UI autocomplete with mysql database. I don't know what should php file look like, how's request sent to php etc.. In demos and documentation on jQuery site there is simple example with remote datasource where source is: source: "search.php". So how search.php knows what i've typed in input box? there is no query attached to search.php :/ I've noticed that json is used.. i'm confused and i've tried dozen method without results. What i need is simple autocomplete with name of cities that contains text i've typed into textbox.

My db looks like:

table_cities:
-------------
id:   -city id
name: -city name

And i need autocomplete to show me cities that contains text i've typed into textbox e.g.

"po" will result in "liverpool" "Portsmouth"...

html
----
<input type="text" id="cities" />

js
--
$( "#cities" ).autocomplete({
...

Thanks!

Parrots · Accepted Answer · 2012-01-16 18:24:54Z

2

You just need to return a JSON-encoded array of id/value objects. The autocomplete plugin also sends over a get parameter named "q" which contains what is currently in the input box. A simple example of a search.php file that would work:

$autocomplete_value = mysql_real_escape_string($_GET["q"]);
$sql = "select id, name from table_cities where name LIKE '%$autocomplete_value%'";
$query = mysql_query($sql);

$results = array();
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
    array_push($results, array('id' => $row['id'], 
                               'value' => $row['name']) );
}
echo json_encode($results);

You'll need to adapt that to whatever PHP framework you might be using, but the pattern should be the same - loop through the results creating an array of 'id' and 'value' objects, then json encoding that.

Edit: you might want to update the SQL to do lowercase compare (so capitalization is ignored) and other such enhancements, but this should at least get you going in the right direction.

answered Jan 16, 2012 at 18:24

Parrots

27k14 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

woopata Over a year ago

Thanks, this solved half of problem, i didn't know that request is sent over parameter named "q". Other half of problem is jQuery.. When i used your php code with simple jquery, return was all cities, not just matching cities: $( "#cities" ).autocomplete({ source: "scripts/loc.php", minLength: 4, select: function( event, ui ) { alert(ui.item.id); } });

Parrots Over a year ago

Look into Firebug for Firefox or use Safari/Chrome's built in development mode - great for debugging the ajax calls and see what is being sent to/from the server.

user610217 · Accepted Answer · 2012-01-16 18:12:52Z

0

I haven't used jQuery autocomplete, but it seems clear that it makes an AJAX call back to your web server, providing the data that has already been typed in the box. The web server will query the database as you see fit, and return the results that should be displayed.

The query would not be part of the jQuery plugin; you would have to write that yourself.

answered Jan 16, 2012 at 18:12

user610217

2 Comments

woopata Over a year ago

Yes, i saw that on some examples there is ajax used ($.ajax..) but i don't understand how it's working in that example on jQuery UI demos and documentation -> remote datasource. When you click to view source for that example there is only source: ""search.php" without $.ajax..

user610217 Over a year ago

I would imagine search.php accepts a form value; when the php file is invoked, it grabs the form value, queries the database, and returns a result set to the caller (in this case the plugin) which would then display the results. Which part of this are you having difficulty with?

Emin Net Bilişim · Accepted Answer · 2019-03-30 23:52:19Z

-1

You can use ".=" in while loop.

$data = "";
while($row = mysqli_fetch_assoc($result))
{
$data .= "bla bla bla..."
}

edited Mar 30, 2019 at 23:52

answered Mar 30, 2019 at 22:48

Emin Net Bilişim

11 bronze badge

Collectives™ on Stack Overflow

jQuery UI autocomplete with mysql database

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related