How to create chained select with PHP and jQuery?

In this article we’ll talk about the chaining of  select also called   select cascade.

It is a procedure  that we often find on the web and it consists in giving the user the possibility  to find a specific data through a few passages: practically choosing a category in the first select, the second one will be populated with the results related with the category selected.

We imagine, for example,  a first select where it is possible to choose between motorcycles and  cars; once we selected the car category , the second select will be populated with the brands of the cars available. Then selecting a brand, the third select will be populated with the models available of this brand. In this article we’ll see a very simple example  but it will give us the basis  to comprehend  the procedure to do even in the case of   more complicated cascades.

You can examine what we will create in this page of example. I state in advance that the argument is quite advanced and it presumes a medium-advance level of knowledge of PHP and of MySql.

As you can see, the example is very minimalist. A first select that requests the category in which appear: colours, flowers and tools. A second select  in which once the category is selected  we’ll find a list of items concerning that category.

Create the structure of the database

So we are going to create a table called categories with the fields

  • id_cat
  • name

And another table called type with the following fields

  • id_type
  • id_cat
  • name

This way we can determine the correct relation between the tables.

Therefore create the database selectExample and execute the  query  that I prepared to help you out in this task. Besides creating the tables necessary , it inserts the data we need for the exercise.

CREATE TABLE `categories` (
`id_cat` int(4) unsigned NOT NULL auto_increment,
`name` varchar(40) NOT NULL,
PRIMARY KEY  (`id_cat`)
) ENGINE=MyISAM AUTO_INCREMENT=4 ;

INSERT INTO `categories` (`id_cat`, `name`) VALUES
(1, 'colours'),
(2, 'flowers'),
(3, 'tools');

CREATE TABLE `type` (
`id_type` int(4) unsigned NOT NULL auto_increment,
`id_cat` int(4) unsigned NOT NULL,
`name` varchar(40) NOT NULL,
PRIMARY KEY  (`id_type`)
) ENGINE=MyISAM AUTO_INCREMENT=15 ;

INSERT INTO `type` (`id_type`, `id_cat`, `name`) VALUES
(1, 1, 'yellow'),
(2, 1, 'green'),
(3, 1, 'red'),
(4, 1, 'gray'),
(5, 1, 'white'),
(6, 2, 'daisy'),
(7, 2, 'cowslip'),
(8, 2, 'lily'),
(9, 2, 'sunflower'),
(10, 3, 'hammer'),
(11, 3, 'screwdriver'),
(12, 3, 'spatula'),
(13, 3, 'wrench'),
(14, 3, 'clamp');

Preparation of the main page

Now let’s prepare the main file that is select.php


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <script type="text/javascript" src="jquery-1.4.1.js"></script>
        <script type="text/javascript">
            $(document).ready(function(){

            });
        </script>
    </head>
    <body>
        <form id="select_form">
            Choose a category:<br />
            <select id="category">

            </select>
            <br /><br />

           choose a type:<br />
            <select id="type">
                <option value="0">choose...</option>
            </select>
            <br /><br />
            <input type="submit" value="confirm" />
        </form>
        <div id="result"></div>
    </body>
</html>

As you can see I included  jQuery. In fact thanks to an ajax call we’ll populate the second select.

I declared a form containing the first select with no option and the second select containing only an option that is “choose…”. Then we have an element with id result in which at the end we’ll insert the choice made.

Create the file with the parameters of  connection

Now we create the file db_config.php that will contain the parameters of connection to the database.

<?php
$host = "localhost";
$user = "root";
$password = "*********";
$db = "selectExample";
?>

Naturally you have to assign the parameters of your database.

The php file for the management of the requests

At this point we start creating the PHP class that will handle the elaboration of the information and will give back results. The file will be called select.class.php.

class SelectList
{
    protected $conn;

        public function __construct()

        {

        }
}

We begin with declaring the class, afterwards the propriety $conn that will contain the connection resource to the database  and at the end the builder method, that at the moment we leave empty.

Now we write the method for the connection to the database.

protected function DbConnect()
{
    include "db_config.php";
    $this->conn = mysql_connect($host,$user,$password) OR die("Unable to connect to the database");
    mysql_select_db($db,$this->conn) OR die("can not select the database $db");
    return TRUE;
}

As you see, after I included the parameters I proceed with  the connection and therefore with the selection of the database.

Now we proceed  with the creation of the ShowCategory() method that will create the option of the first select. This method will be executed directly with the page load, we’ll see how afterwards.

public function ShowCategory()
{
    $sql = "SELECT * FROM category";
    $res = mysql_query($sql,$this->conn);
    $category = '<option value="0">choose...</option>';
    while($row = mysql_fetch_array($res))
    {
        $category .= '<option value="' . $row['id_cat'] . '">' . $row['name'] . '</option>';
    }
    return $category;
}

As you can see I execute a query that selects all the lines of the category table. Afterwards I insert in the variable $category the first option, containing a message that invites the user to make a choice. At the end I add at the variable $category as many option as the items present in the table, being sure to indicate as value the id of the category and as contents of the tag option the name of the category.

Now let’s write the last method that will fetch the value passed from the first select (the id of the category) and will populate the second according to the value passed from the first.

public function ShowType()
{
    $sql = "SELECT * FROM type WHERE id_cat=$_POST[id]";
    $res = mysql_query($sql,$this->conn);
    $type = '<option value="0">choose...</option>';
    while($row = mysql_fetch_array($res))
    {
        $type .= '<option value="' . $row['id_type'] . '">' . $row['name'] . '</option>';
    }
    return $type;
}

In this case through the query we’ll select the lines of the type table that have as id_cat the value passed through POST (this value will be passed with an ajax call, but we haven’t written it yet, we’ll see it after).

The rest is similar to the previous method.

Now, considering that whatever operation does this class it is necessary the connection to the database, we can invoke the method DbConnect() directly in the constructor method.

Moreover we can,  for convenience,  instantiate the class directly in this file.

So here the whole class:

<?php
class SelectList
{
    protected $conn;

        public function __construct()
        {
            $this->DbConnect();
        }

        protected function DbConnect()
        {
            include "db_config.php";
            $this->conn = mysql_connect($host,$user,$password) OR die("Unable to connect to the database");
            mysql_select_db($db,$this->conn) OR die("can not select the database $db");
            return TRUE;
        }

        public function ShowCategory()
        {
            $sql = "SELECT * FROM category";
            $res = mysql_query($sql,$this->conn);
            $category = '<option value="0">choose...</option>';
            while($row = mysql_fetch_array($res))
            {
                $category .= '<option value="' . $row['id_cat'] . '">' . $row['name'] . '</option>';
            }
            return $category;
        }

        public function ShowType()
        {
            $sql = "SELECT * FROM type WHERE id_cat=$_POST[id]";
            $res = mysql_query($sql,$this->conn);
            $type = '<option value="0">choose...</option>';
            while($row = mysql_fetch_array($res))
            {
                $type .= '<option value="' . $row['id_type'] . '">' . $row['name'] . '</option>';
            }
            return $type;
        }
}

$opt = new SelectList();
?>

Now let’s go back to work on the main file (select.php)

From the moment that this file will be loaded the first select has be populated. We’ll do it inserting the method ShowCategory() in this way:

<body>
<?php include "select.class.php"; ?>

    <form id="select_form">
        Choose a category:<br />
        <select id="category">
            <?php echo $opt->ShowCategory(); ?>
        </select>
        <br /><br />

As you see, we include the class already instantiated in the object $opt. Afterwards, between the tag select, we print the result of the ShowCategory() method, that will be the option correctly populated with the values taken from the database.

You can try the script and see that the first  select will populate correctly.

Implement the ajax functionalities

Now, what we have to do through jQuery is to fetch what will be selected in this select and send the data  (the id of the category) to the method ShowType() that will return us the option to insert in the second select. Everything naturally with an ajax call. The code (like always with jQuery is very simple) is the following (that goes inserted in the function $(document).ready).

$("select#category").change(function(){
    var id = $("select#category option:selected").attr('value');
    $.post("select_type.php", {id:id}, function(data){
        $("select#type").html(data);
        });
    });

What does this mean?  At the occurence of change in the select with id categorie, execute this function that:

  • Enhances the variable id with the attribute value of the option selected.
  • Sends through POST the variable id to the file select_type.php (that we haven’t written yet, but we’ll do it immediately).
  • What is returned from this call insert it in the select with id type.

Create a service file

Obviously the file select_type.php will be a service file that will simply invoke the method  ShowType().

<?php
include "select.class.php";
echo $opt->ShowType();
?>

At this point you can try the script. Now selecting a category, the second select will get populated correctly.

Improve the usability

Now let’s add something else. Do you remember the question on the usabilty faced in the previous article on ajax? Good, I think that the second select should be disabled when the page gets loaded. At the beginning of the call we could replace  “select” with “wait…”. Once the call has been executed, the select can be  enabled. This is correct under the point of view of the usability. Therefore we modify the code in this way:

$(document).ready(function(){
    $("select#type").attr("disabled","disabled");
    $("select#category").change(function(){
    $("select#type").attr("disabled","disabled");
    $("select#type").html("<option>wait...</option>");
    var id = $("select#category option:selected").attr('value');

    $.post("select_type.php", {id:id}, function(data){
        $("select#type").removeAttr("disabled");
        $("select#type").html(data);
        });
    });

Now all we have to is determine what happens when you press enter . In our case we’ll write the choice made in the element #result. WE should also consider (usability) what  happens if the enter button gets pushed before all the choices have been made.

This is the code that I will explain later.

$("form#select_form").submit(function(){
    var cat = $("select#category option:selected").attr('value');
    var type = $("select#type option:selected").attr('value');
    if(cat>0 && type>0)
    {
        var result = $("select#type option:selected").html();
        $("#result").html('your choice: '+result);
    }
    else
    {
        $("#result").html("you must choose two options!");
    }
    return false;
});

At the occurrence of submit,  we take the value of the attribute value of the two  select. If  these values are both greater than zero (therefore a choice in the select has been made in the two select), we take the contents of the select with id type and we print it in the element #result.

Instead if the two choices haven’t been made, in the element #result we print the error message.

Here select.php complete with all the changes.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
    <script type="text/javascript" src="jquery-1.3.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $("select#type").attr("disabled","disabled");
            $("select#category").change(function(){
            $("select#type").attr("disabled","disabled");
            $("select#type").html("<option>wait...</option>");
            var id = $("select#category option:selected").attr('value');
            $.post("select_type.php", {id:id}, function(data){
                $("select#type").removeAttr("disabled");
                $("select#type").html(data);
            });
        });
        $("form#select_form").submit(function(){
            var cat = $("select#category option:selected").attr('value');
            var type = $("select#type option:selected").attr('value');
            if(cat>0 && type>0)
            {
                var result = $("select#type option:selected").html();
                $("#result").html('your choice: '+result);
            }
            else
            {
                $("#result").html("you must choose two options!");
            }
            return false;
        });
    });
    </script>
    </head>
    <body>
        <?php include "select.class.php"; ?>
        <form id="select_form">
            Choose a category:<br />
            <select id="category">
                <?php echo $opt->ShowCategory(); ?>
            </select>
        <br /><br />
        Choose a type:<br />
        <select id="type">
             <option value="0">choose...</option>
        </select>
        <br /><br />
        <input type="submit" value="confirm" />
        </form>
        <div id="result"></div>
    </body>
</html>

Conclusion

In this article we saw an application of ajax  very useful and requested. Considering that it’s an advanced  functionality  it’s obvious that it can seem complicated for those who don’t have a certain mastery of php, on the other hand it cannot be made more simplier than this.

However we need to say that for a correct application of the criterions of accessibility, we should foresee a solution that allows the use of this system, even without the use of javascript (and therefore of jQuery).

And you,  what do you think about it?  Did you find this article too complicated?  Will you use these principles in your sites?

Master per Web Designer Freelance
In tutti questi anni abbiamo ricevuto centinaia di richieste di approfondimento sulle numerose tematiche del web design vissuto da freelance. Le abbiamo affrontate volta per volta. Ma ci siamo resi conto che era necessario fare qualcosa di più. Ecco perché è nato One Year Together, un vero e proprio master per web designer freelance che apre finalmente le porte al mondo del lavoro.
Scopri One Year Together »
[pdf]Scarica articolo in PDF[/pdf]
Tags: , ,

The Author

Maurizio is married to the triad PHP - MySql - Apache and, not enough, he has a lover called jQuery. He has a blog where he tries to describe in detail all of "his lovers". His real specialty is the realization of large business application, altough he never refuses the commitment of a website.

Author's web site | Other articles written by

Related Posts

You may be interested in the following articles:

160 comments

    • Sanjay
  1. Jebin
    • Sanjay
    • Ashu
  2. Jebin
  3. tealc
  4. phplearner
    • Chris
    • rpauly
  5. Chris
    • Patrick
  6. chris
  7. ChrisA
    • chris
  8. Jack Milligan
  9. Jack Milligan
  10. Zaman
  11. Steve
  12. Cleusa Collares Machado
  13. Alfredo
  14. Somuraja
  15. mathen
  16. dbittle
  17. domenico
    • leonidas
  18. ed
  19. leonidas
  20. Jenna
  21. aker
  22. Lonestar Jack
  23. Heaven
    • Heaven
    • Andy
    • damaki
  24. Lonestar Jack
  25. Alin
  26. flossy mista
  27. alin
  28. Mauronimo
    • Andy
  29. Andy
    • Lina
  30. damaki
  31. David
    • Andy
  32. L
  33. bogdA
  34. zoltek
  35. bogdA
  36. jehane
  37. mark menace
  38. ismail aktas
  39. Noella
    • Robindra Singha
  40. valerko
  41. khine Tun
  42. Muntasir Mahmud Aumio
  43. phpnovice
  44. Noella
  45. cOp
  46. Emdadul
  47. Lional
    • Steve
  48. Omar Janz Monroy
  49. Kerim
  50. badger_fruit
  51. Ian
  52. Samad
  53. Samad
  54. Jan
  55. new
  56. Mr. D
  57. saminathan
    • tibewww
  58. Ian
  59. name
  60. HappyCat
  61. andre
  62. tibewww
    • tibewww
    • tibewww
  63. Tom
  64. Izik
  65. LUIGI
  66. Baburao A L
    • Baburao A L
    • John
  67. aamir
  68. monmon
  69. Jez
  70. Mo
    • Robindra
  71. Mo
  72. tango91a
  73. Elisha
  74. Taleeb
  75. Bloke
  76. Taleeb
  77. jonh tuatis
  78. marie
  79. Mubarak
  80. Ngash

Trackback e pingback

  1. Tweets that mention How to create chained select with PHP and jQuery? | Your Inspiration Web -- Topsy.com
    [...] This post was mentioned on Twitter by soshableweb, mtx_maurizio and V. Tavares (E-Goi), Tom Bangham. Tom Bangham said: How …
  2. You are now listed on FAQPAL
    How to create chained select with PHP and jQuery?... In this article we'll talk about the chaining of select also called …
  3. 20 jQuery Tutorials of Any Complexity Level | Agnis Designers
    [...] 10. How to create chained select with PHP and jQuery [...]
  4. Chained Select with PHP and jQuery « Jquery Labs
    [...] Tutorial Tutorial Page [...]
  5. PHP, Ajax, MySql, Javascript Chain Select | Jagadishwor | Ajax, jQuery, PHP MySql Tutorial Tips and Tricks
    [...] Your Inspiration Web (PHP [...]
  6. Fresh jQuery Plugins For Your Next Project - Episode 7
    [...] Demo Download [...]
  7. Element with jquery |Avnish Namdev
    [...] How to create chained select with PHP and jQuery? [...]
  8. PHP Dynamic Checkbox List from SQL, based on Cascade Select selection | Code and Programming
    [...] I came across this tutorial, pretty much what I wanted and I worked on the database based on this: …
  9. Chained select boxes with jquery/php/database not working | BlogoSfera
    [...] been using this resource for this project: http://www.yourinspirationweb.com/en/how-to-create-chained-select-with-php-and-jquery/ and it’s gotten me most of the way, but unfortunately the …
  10. Multiple Chained SELECT Tag JavaScript and jQuery - QueryPost.com
    [...] Day! I created a chained select using php and jQuery like on this page http://www.yourinspirationweb.com/en/how-to-create-chained-select-with-php-and-jquery/ and I created …
  11. CopyQuery | Question & Answer Tool for your Technical Queries
    [...] subcategory, product description) my reference to create those chained select boxes is this website http://www.yourinspirationweb.com/en/how-to-create-chained-select-with-php-and-jquery/ the problem is that …
  12. Put dynamic table on chained select box using jQuery | BlogoSfera
    [...] subcategory, product description) my reference to create those chained select boxes is this website http://www.yourinspirationweb.com/en/how-to-create-chained-select-with-php-and-jquery/ the problem is that …
  13. Creating a chained select form with PHP and jquery | BlogoSfera
    […] in the managers table and manager in the employees table. I found an example of how to do this …
  14. Chain Select Menu | DC2NET Links
    […] http://www.yourinspirationweb.com/en…hp-and-jquery/ […]
  15. Chain Select Menu | Hostcomplaint.com
    […] http://www.yourinspirationweb.com/en…hp-and-jquery/ […]
  16. Creating three chain drop down with PHP+MySQL+jQuery | PHP Tutorial 4 Beginner
    […] drop down menu with help of PHP, MySql and jQuery. Before, we start the tutorial, I want to thank …

Leave a Reply

Current day month ye@r *