{"id":898,"date":"2010-08-12T16:13:26","date_gmt":"2010-08-12T15:13:26","guid":{"rendered":"http:\/\/www.simonbattersby.com\/blog\/?page_id=898"},"modified":"2012-10-23T09:44:28","modified_gmt":"2012-10-23T08:44:28","slug":"jquery-ui-autocomplete-with-a-remote-database-and-php","status":"publish","type":"page","link":"https:\/\/www.simonbattersby.com\/blog\/jquery-ui-autocomplete-with-a-remote-database-and-php\/","title":{"rendered":"jQuery UI Autocomplete with a remote database and PHP generating JSON data"},"content":{"rendered":"<p>I spent some time last night struggling with <a href=\"http:\/\/jqueryui.com\/demos\/autocomplete\/\" title=\"jQuery UI Autocomplete\">jQuery UI Autocomplete<\/a>. I was trying to use a remote data source, and was calling a php script to interrogate the database, no ajax. During the course of getting it to work properly, I discovered a couple of things that aren&#8217;t very clear in the documentation.<\/p>\r\n\r\n<p>The basic javascript, lifted straight from the jQuery UI demo is:<\/p>\r\n\r\n<pre>$(\"#autocomplete\").autocomplete({\r\nsource: \"search.php\",\r\nminLength: 2,<span class=\"code_comment\">\/\/search after two characters\r\n<\/span>select: function(event,ui){\r\n    <span class=\"code_comment\">\/\/do something\r\n<\/span>    }\r\n});<\/pre>\r\n\r\n<p>Fine so far. There was one major thing that fooled me. If, instead of using a php script, you use a local source, something like:<\/p>\r\n\r\n<pre>source: [{\"value\":\"Some Name\",\"id\":1},{\"value\":\"Some Othername\",\"id\":2}]<\/pre>\r\n\r\n<p>then this local data is queried each time a character is entered in the required field. I assumed, therefore, that all I had to replicate in my search.php script, was a query to return all the values from my database. Wrong! I need to pass a search term to my php script in order to return only the correct subset of data. I further discovered that jQuery UI Autocomplete passes this data as a GET entitled &#8216;term&#8217; (didn&#8217;t see this anywhere in the examples). So, armed with this knowledge, my php script looks like this:<\/p>\r\n\r\n<pre><span class=\"code_comment\">\/\/connect to your database\r\n<\/span>\r\n$term = trim(strip_tags($_GET['term']));<span class=\"code_comment\">\/\/retrieve the search term that autocomplete sends\r\n<\/span>\r\n$qstring = \"SELECT description as value,id FROM test WHERE description LIKE '%\".$term.\"%'\";\r\n$result = mysql_query($qstring);<span class=\"code_comment\">\/\/query the database for entries containing the term\r\n<\/span>\r\nwhile ($row = mysql_fetch_array($result,MYSQL_ASSOC))<span class=\"code_comment\">\/\/loop through the retrieved values\r\n<\/span>{\r\n\t\t$row['value']=htmlentities(stripslashes($row['value']));\r\n\t\t$row['id']=(int)$row['id'];\r\n\t\t$row_set[] = $row;<span class=\"code_comment\">\/\/build an array\r\n<\/span>}\r\necho json_encode($row_set);<span class=\"code_comment\">\/\/format the array into json data\r\n<\/span><\/pre>\r\n\r\n<p>Hope this explanation is useful. No demo this time, because it doesn&#8217;t seem very interesting. You can have a look at an integration of jQuery UI Autocomplete with a vertical slider <a href=\"\/blog\/vertical-scrollbar-with-integrated-autocomplete-jquery-ui\" title=\"Autocomplete and vertical slider\">here<\/a> and see a demo <a href=\"\/test\/vertical_scrollbar_autocomplete_demo.php\" title=\"Autocomplete demo\">here<\/a>.<\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>I spent some time last night struggling with jQuery UI Autocomplete. I was trying to use a remote data source, and was calling a php script to interrogate the database, no ajax. During the course of getting it to work properly, I discovered a couple of things that aren&#8217;t very clear in the documentation. The [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-898","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/pages\/898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/comments?post=898"}],"version-history":[{"count":1,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/pages\/898\/revisions"}],"predecessor-version":[{"id":2581,"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/pages\/898\/revisions\/2581"}],"wp:attachment":[{"href":"https:\/\/www.simonbattersby.com\/blog\/wp-json\/wp\/v2\/media?parent=898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}