Jquery SELECT2 v3 Ajax/PHP Tutorial

Jquery logo

A little tutorial on the Select2 JQUERY Plugin with a BASIC AJAX response via a PHP script.
On a recent project I was looking for an autocomplete jquery plugin that acts like a select box. The reason i need it as a select box is to prevent incorrect data choice. At first i was running the great jquery-ui plugins and using the autocomplete via a text box and retrieving data via ajax and json.
However I soon discovered that my clients could type data in the textbox and if the answer was not on the autoselect it added the text they have typed. Now when trying to search through a database for results this caused me a bit of a headache.
So i plodded over to Google and looked for an alternative.

 

Updated April 2015
There is now a newer version of the Select 2 plugin available. This is V4 at present. They have altered the way the ajax works so this tutorial will not work with this version.
To view an updated tutorial for Select2 Version 4 click here.

I understand that the Jquery-UI version has a bind method for select but it just seemed a bit messy and the coding was not the easiest to follow.
Next i found a great plugin called chosen :
http://harvesthq.github.com/chosen/ and this was an excellent plugin.
The only problem i had was loading the data via ajax for a keypress. For my particular project I had 68000 titles in a database i needed to search. Now that makes for a large select option list.
So I plodded back onto Google and found a link via Stack-Exchange for a plugin called Select2.
http://ivaynberg.github.com/select2.
So i thought i would give this a go… Only issue was the ajax example was not easy to follow as they have used an advanced options to retrieve data from rotten tomatoes.
But this was the plugin I wanted.
I found some help here : http://it.toolbox.com/blogs/rymoore/select2-jquery-select-boxes-54810
with the an easy ajax answer. I am writing this post to show you how to use php to send the json back to the ajax request.

Firstly the html required :

	 	
	 
	 

Should produce similar to this :
Select2 No Text

Notice that because I am using ajax to get the result the field has to be hidden.
The name is the used as usual to send the value through the form.
The data-placeholder appears as the select box placeholder.

Now I added the following jquery :

$(document).ready(function(){
   $('#selectbox-o').select2({
    minimumInputLength: 2,
    ajax: {
      url: "../datafeeds/optionlist.php",
      dataType: 'json',
      data: function (term, page) {
        return {
          q: term
        };
      },
      results: function (data, page) {
        return { results: data };
      }
    }
  });
});

The option I have set is minimumInputLength: 2,
This means that the ajax will not get called until 2 characters have been entered into the search box.
After 2 characters have been entered it does an ajax onto optionlist.php (change to your php file).
Datatype is set to json as the data is sent back from php via json.
term is characters entered and q being the string that is sent to the script in the form of a GET.
The results are then returned to data.

So the php script (optionlist.php) is a mysql query on a database to get a json string back.
The select2 plugin looks for the ajax to appear as : [{“id”:”blah”,”text”:”blah text”},{“id”:”blah2″,”text”:”Blah2 text”}]
This will show the text in the select box and the value of the input will be the id..
so if it was enetered as a standard select box :

Blah Text

Blah2 Text

Now for the magical php script.
I am using pdo with a prepared statement for added security.
I am not going to show connection proccess.

The end result should look similar to this :
Select2 Dropdown

*** UPDATE ***
I have updated the syntax on this following the comment from Gustavo to avoid servers with error-reporting switched on all
How advanced you make the mysql query is entirely down to you. In my picture I join the mysql with a products database with a count to give me back the amount of items shown.

Please feel free to drop me a comment if you spot a mistake or you have any comments or suggestions.

Leave a Response

33 Responses

  • edragame
  • April 8th, 2013

I’ve been looking for both sides (php and js) of a tutorial for select2 and this is the first good one I’ve found. Thanks a lot for publishing this!

  • Bella
  • April 25th, 2013

Cannot get this to work do you have a demo page?

HI thanks for the enquiry, I will get a working demo up in the next couple of days…

  • hamzahir
  • May 2nd, 2013

Great tutorial, i googled for a select2 tutorial as it is the first time i will be using it and your website came up front ! it’s a really good startup toturial ! i will give feedback once i integrated it with my project.

  • Adrian
  • May 7th, 2013

I was on the point to give up with select2. The query is so bad explained on plugin page but with the help of your tutorial I will go forward and keep using it.
You saved my day! Thanks.

Great tutorial, Thank you!!! Saved me a lot of time 🙂

Just a little recommendations on the php script
instead of
$result->bindvalue(‘:term’,’%’.$_GET[q].’%’,PDO::PARAM_STR);
use
$result->bindvalue(‘:term’,’%’.$_GET[“q”].’%’,PDO::PARAM_STR);

also instead of
$answer[] = array(id=>$row[id],text=>”$row[id] – $row[option]”);
use
$answer[] = array(“id”=>$row[“id”],text=>”$row[“id”] – $row[“option”]”);

and instead of:
$answer[] = array(id=>’0′,text=>’No Results Found..’);
use
$answer[] = array(“id”=>’0′,”text”=>’No Results Found..’);

Because if you have error reporting and notices on You’ll get a bunch of notices and script will not work…

Hi Gustavo,
Thanks for the update , i have updated the code accordingly..

”$row[“id”] – $row[“option”]“

does return an error on so i changed to

“text”=>$row[‘id’].” – “.$row[‘option’]

and it works perfect…

  • Alexey
  • June 18th, 2013

It is great post! Thank you!

  • alvaroz
  • June 19th, 2013

today this is the only example that works
thanks

  • Mauro
  • July 18th, 2013

Great, simple, clear example.
Thanks!!!

My Example:

$row[‘id’],”text”=>$row[‘kod’].” | “.$row[‘adi’]);
// the text I want to show is in the form of option id – option

}

}else{

$answer[] = array(“id”=>”0″,”text”=>”Böyle bir cari hesap yok!”);

}

// Send the data.
echo json_encode($answer);
?>

  • worxhard
  • September 12th, 2013

$result = $db->prepare(“SELECT id,option FROM option_table WHERE option LIKE :term ORDER BY option ASC LIMIT 0,40); needs an ” at the end -> ..(“SELECT……..LIMIT 0,40”);

Thank you for this clear example!

Nicely spotted. I have added the ” to the end of the sql query.
Thanks very much.

  • ben
  • October 3rd, 2013

Hello,

such a great tuto. Really cool.

One more question.
How is it possible to define selected value for the list.
Actualy, once the form submited and the page reloaded, i would like to see the selected value on the field.
an idea ?

Thanks a lot.

Hi,
it actually defeated me in the time span I had to find an answer to this question.
I know its a bit of a work around but i used an extra input field (readonly) that appears if it detects a post and display the value in there.
As soon as keypress is then made in the select you could use jquery to hide this field.
When i get a moment I will take a second look and see if i can find a way around it properly.

Hi, This was great help to us as well,

Now it does all the things correctly,however during edit mode we have to have the dropdown populated with the last inserted record.i am finding that quite difficult,

Do you have something ready for that?

This will really great help

Thanks in advance

  • Manjunath
  • October 22nd, 2013

hey this is really cool, i was searching for this long time.. you saved me buddy,.. keep it up…

  • Dan
  • December 11th, 2013

Thank you for the example, helped me out a lot!

  • Jizz
  • December 22nd, 2013

Thanks a lot!

Thank you man! This Select2 tutorial helped me a lot!

  • Gonzalo
  • January 24th, 2014

Thanks a lot, man!
I’ve spent a few hour trying to make it work with a CakePHP app and didn’t make it…
FInally, thanks to you, now it’s working like a charm 😀
Select2 is such a cool script but its documentation is not the best around…

Thanks again 🙂

  • Agamagaman
  • January 29th, 2014

This documentation is the best, thankyou for providing this.

  • Using Select2 with MySQL query | BlogoSfera
  • February 9th, 2014

[…] I am trying to populate my Select2 drop down with values from my MySQL using this tutorial: http://www.southcoastweb.co.uk/jquery-select2-ajax-tutorial/ […]

  • Tibi
  • March 12th, 2014

Thanks, helped me a lot!

  • Kenni
  • April 24th, 2014

Ok, i honestly have spent at least 12 full hours on resolving my problem with Select2-Ajax-multiple select…you pointed me in a good direction and just made my day. THANKS!!!

  • Protecta
  • April 25th, 2014

Hello, i’ve followed this tutorial and got it to almost work. The only problem is that when there are no results, it keeps on searching, it never show me “no matches found”. Any idea ?

  • fouad.j
  • May 27th, 2014

if it can help, it’s so easy to understand

prepare($requete);

$req->bindParam(‘:term’, $term, PDO::PARAM_STR);

$req->execute();

$data = $req->fetchAll();

return $data;

} catch (Exception $e) {

echo $e->getMessage();

}
}

if(isset($_POST[‘nomPersonnel’])){

$term = secure($_GET);
$term = $_POST[‘nomPersonnel’];

$pers = getPersonnel($term);

echo json_encode($pers);

}
?>

  • Alex
  • June 3rd, 2014

THIS IS AWESOME!!! It really works

  • Björn
  • August 5th, 2014

ok. i have problem.

The script works fine if i use [{“id”:”blah”,”text”:”blah text”},{“id”:”blah2″,”text”:”Blah2 text”}]
in the .php file.

But if i use your php example the dropdown will say “loading failed”… why?

Thank you very Much this helped me alot!!!!!!!

Excellent article. I certainly appreciate this
site. Continue the good work!

  • Alain
  • April 19th, 2015

Thanks a lot, this works great with select2 ver 3, but i didnt find how to make it work with ver 4

Hi i have added a updated tutorial for version 4 here : http://www.southcoastweb.co.uk/jquery-select2-v4-ajaxphp-tutorial/