Jquery Select2 Plugin is an excellent Select box alternative.
South Coast Web Design Ltd
Jquery SELECT2 v3 Ajax/PHP Tutorial Feb 10

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 :

	 	
<label>Option Value :</label>	 
<input id="selectbox-o" class="input-xlarge" name="optionvalue" type="hidden" data-placeholder="Choose An Option.." />	 

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
<!--?php // setup databse connection require_once('lib/database.connect.php'); // i have set the limit to 40 to speed up results $result = $db-&gt;prepare("SELECT id,option FROM option_table WHERE option LIKE :term ORDER BY option ASC LIMIT 0,40"); 
// bind the value for security with the wildcard % attached. 
$result-&gt;bindvalue(':term','%'.$_GET["q"].'%',PDO::PARAM_STR); 
$result-&gt;execute(); 
// make sure there are some results else a null query will be returned 
if($result-&gt;rowCount() != 0) {     
    while($row = $result-&gt;fetch(PDO::FETCH_ASSOC)){
         $answer[] = array("id"=&gt;$row['id'],"text"=&gt;$row['id']." - ".$row['option']);         // the text I want to show is in the form of option id - option     
    } 
} else { 
    // 0 results send a message back to say so.     
    $answer[] = array("id"=&gt;"0","text"=&gt;"No Results Found.."); 
} 
// finally encode the answer to json and send back the result. 
echo json_encode($answer); 
&lt;/pre&gt;
&lt;p&gt;Hopefully this will help simplify the ajax request for the select2 plugin.&amp;lt;br ?--&gt;&lt;br ?-->
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.

33 Responses to “Jquery SELECT2 v3 Ajax/PHP Tutorial”

edragameApril 8th, 2013 at 8:48 pm

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!

hamzahirMay 2nd, 2013 at 10:20 am

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.

AdrianMay 7th, 2013 at 3:40 am

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.

Gustavo ArcilaJune 2nd, 2013 at 5:09 pm

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…

southcoastwebJune 2nd, 2013 at 7:17 pm

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…

Yunus KOCABAYJuly 24th, 2013 at 12:19 am

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);
?>

worxhardSeptember 12th, 2013 at 2:42 pm

$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!

benOctober 3rd, 2013 at 3:38 pm

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.

southcoastwebOctober 4th, 2013 at 8:44 am

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.

Shankar OjhaOctober 20th, 2013 at 9:36 am

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

GonzaloJanuary 24th, 2014 at 1:17 am

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 🙂

KenniApril 24th, 2014 at 10:43 am

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!!!

ProtectaApril 25th, 2014 at 1:22 pm

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.jMay 27th, 2014 at 1:26 pm

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);

}
?>

BjörnAugust 5th, 2014 at 10:22 am

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?

AlainApril 19th, 2015 at 9:52 am

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

Leave a Response


Top