JQuery autocomplete with AJAX and JSON

Hello Experts,

I am having troubles implementing JQuery autocomplete using JSON.
Basically, I get an JSON encoded data from my server (I use codeigniter php framework).

The JSON format is following:
[{"id":"1","prodname":"Candy","code":"CND01","uname":"grams","pcname":"Sweets"} ..... ]

My questions are:
-How can I make the autocomplete of the Product field work?
-When a user selects certain Product, the fields ID,Code,Category and UOM get populated?

Also, this is my way of implementing Mater/Detail forms and data insertion. So, maybe you have any suggestions on improving it.

Thank you very much in advance!

Note: In the attached images, the point of interested is located in the red rectangle.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Erdinç Güngör ÇorbacıPHP Development Team LeaderCommented:
You didn't give any code? How will we suggest, you should give us php and mysql structure .
But you can also easily implement the usage in links below to your code. Ask if you got any troubles with them.

psybaronAuthor Commented:
I thought that that part (the PHP/MySQL) is not much of an issue. But here it is:

My Controller function:
My Model function:

I'm having more troubles handling from the point when the JSON is ready, and don't know how to read it in implement it on the form fields.

So, upon even(select or something) on the Products field, all the other ID,Code,Category and UOM get populated.

Erdinç Güngör ÇorbacıPHP Development Team LeaderCommented:
dude i also did just start learning codeigniter but the only thing i can see here is ; You are fetching some data in the way of codeigniter rather than using the classic
Classic syntax should be like this ;

if(!isset($options['status']))  $where_cls=" where p.status='active' ";
$qs="SELECT p.id,p.prodname,p.code,u.uname,pc.pcname FROM exp_cd_products AS p LEFT JOIN exp_cd_uom AS u ON u.id = p.uname_fk LEFT JOIN exp_cd_product_category AS pc ON pc.id = p.pcname_fk $where_cls";

The two blocks of code you wrote is simply does the thing above only.

What you need to do furthermore is ;

- Specify an input variable for filtering in an  additional  WHERE p.xxxxx='$_GET["user_input"]'; and add that to query as well.

- get the value you need to use in autocomplete (probably in an array)
   ( i do in classic way as ;
     while($row=mysql_fetch_array($data) )
      $my_field[]=$row["fieldname"]; // you can add more joining them by .

- then use  json_encode() as ;
    echo json_encode($data);

- ALL the things above was to send a response to a ajax call from the page containing form and will be in another file apart from the main php
Lets say it autocomplete.php.
Then you must call this from the main php file (containing the form ) with javascript or better with Jquery. as below ;

<script type="text/javascript">
function xrefresh_banka() 
    var qs = $("#your_users_inputs_id").val();
    if(qs == '') {
     return false;
    else {
            $.get("autocomplete.php?user_input="+qs+"&ts="+ new Date().getTime(), function(data) {
            $("#place_to_show_autocomplete_results_id").val(data);//you may need to reshape data input before

Open in new window

by changing some fields to your tables and form & html code and preparing a div to show autocomplete results you can make them work. But for an inexperienced codeigniter user (like me) it's a little bit complicated i guess. so you may try adapting the samples i gave before to your page may be more easy.

i hope i could show you the light :)

cys bb
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Erdinç Güngör ÇorbacıPHP Development Team LeaderCommented:
sorry  echo json_encode($data); is wrong ...  that should be -> echo json_encode($my_field);
and surely you must take "WHERE" out of the conditional string variable to the standard query for $GET["user_input"]
psybaronAuthor Commented:
Basically, this is my code now:

		    dataType: 'json',
		    async: false,
		    method: 'post',
		    success: function(data) {
		    	test = data;
		    	Options (Yes/No) as the third segment of the URL.
		    	Yes - retreive finished goods (items that are Saleable)
		     	No - retreive raw materials and components (items that no NOT Saleable)
		    url: '<?php echo site_url('products/autocomplete/'); ?>'
		    		$( "#prodname" ).autocomplete({
			    		minLenght: 2,
		    			source: test,
		    			focus: function( event, ui ) {
		    				$( "#prodname" ).val( ui.item.prodname );
		    				return false;
		    			select: function( event, ui ) {
		    				$( "#prodname" ).val( ui.item.prodname );
		    				$( "#uname" ).val( ui.item.uname );
		    				return false;
		    		.data( "autocomplete" )._renderItem = function( ul, item ) {
		    			return $( "<li></li>" )
		    				.data( "item.autocomplete", item )
		    				.append( "<a>" + item.prodname + "</a>" )
		    				.appendTo( ul );

Open in new window

I does the job. But I have problems. Autocomplete doesn't seems to work properly. Only when I click the letter "b", the autocomplete appears with all the values, (not just the ones starting with "b"). What might be the problem?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Erdinç Güngör ÇorbacıPHP Development Team LeaderCommented:
there must be something wrong with the page you query database results. this code seems ok.
And you may consider using get instead of post for especially autocompletes because post doubles the requested connection to retrieve the result.

And one more thing , if ajax call occurs when you insert just one letter in the box there might be something wrong with the code above. As you've set minLength is 2 ?
psybaronAuthor Commented:
I found solution to the problem, out side of EE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.