I want to create two cascading dropdowns from single table from my database.

The first dropdown which is Main Category is working perfectly. i.e it's populating all the data in the dropdown using AJAX.

Now, The second dropdown which is Sub Category is also getting populated. But the problem now is when i am selecting the Main Category its fetching the whole sub category column in the second dropdown.

What i want is if i select a main category only the sub category in that row in the database should be populated instead of the whole column.

I'll add the screen shot of my database table for better understanding and also my codes which i have tried.

ex:- If i select Fruits in Main Category only aaaa should be populated in Sub Category. Similarly, If i select Vegetables in Main Category only bbbb should be populated in Sub Category.

But what's happening now is if i select Fruits whole Sub Category column is getting Populated.

Can someone please help me regarding this it will be greatly appreciated, thanks in advance.

AJAX which i have written.

    <script type="text/javascript">
    
        $(document).ready(function()
        {
            $.ajax
            ({
                url: "listdropdowns",
                type: "GET",
                dataType: "json",
                success: function(loadmaindropdown)
                {
                    var populatemain= $("#home_main_cat_dropdown_id");

                    $.each(loadmaindropdown, function(index, category)
                    {
                        $("<option>").val(category.main_category).text(category.main_category).appendTo(populatemain);
                    });
                },
            });
        });
        
        $(document).ready(function()
        {
            $("#home_main_cat_dropdown_id").change(function()
            {
                var main_category_values = (this.value) ;

            $.ajax
            ({
                url: "listdropdowns",
                type: "GET",
                dataType: "json",
                success: function(loadsubdropdown)
                {
                    var populatesub = $("#home_sub_cat_dropdown_id");

                    $.each(loadsubdropdown, function(index, category)
                    {
                        $("<option>").val(category.sub_category).text(category.sub_category).appendTo(populatesub);
            console.log(home_sub_cat_dropdown_id.value);                
                    });
                },
            });
            });
        });
        
    </script>

in my DAO from where i am getting the whole category list

public List<Category> selectAllCategory() throws SQLException, ClassNotFoundException
    {
        List<Category> listCategory = new ArrayList<>();
        
        Connection conn = DatabaseConnection.initializeDatabase();
        
        String query = " select * from list_of_categories ";
        
        try
        {
            PreparedStatement prestmt = conn.prepareStatement(query);
             
            ResultSet rs = prestmt.executeQuery();
             
             while (rs.next())
             {   
                 int id = rs.getInt("id");                           
                 String main_category = rs.getString("main_category");
                 String sub_category = rs.getString("sub_category");
       
                 listCategory.add(new Category(id,main_category,sub_category));
             }  
        } 

My Controller

public void populate_dropdowns(HttpServletRequest request, HttpServletResponse response)throws SQLException, IOException, ClassNotFoundException, ServletException, ParseException
    {
        try
        {
            List<Category> listCategory = productDAO.selectAllCategory();
            
            String json = new Gson().toJson(listCategory);
            
            response.setContentType("application/json");
            
            response.setCharacterEncoding("UTF-8");
            
            response.getWriter().write(json);
        }
        
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new ServletException(e);
        }
    }

CodePudding user response：

I Modified my code like this to get solve my problem and now its working properly.

I created separate methods for both dropdowns in DAO and Controller and fetched the data according to my needs.

My AJAX function:-

    <script type="text/javascript">

        $(document).ready(function()
        {
            $.get("listmaindropdown",function(loadmaindropdown)
            {
                var aaaa = $("#home_main_cat_dropdown_id");
                $.each(loadmaindropdown, function(index, category)
                {
                    $("<option>").val(category.main_category).text(category.main_category).appendTo(aaaa);
                });
                    
                $("#home_main_cat_dropdown_id").change(function()
                {
                    var main_category_values = (this.value);
    
                        $.get("listsubdropdown",function(loadsubdropdown)
                        {
                            var bbbb = $("#home_sub_cat_dropdown_id")
                                
                            bbbb.find('option').remove();
                                
                            $.each(loadsubdropdown, function(index, category)
                            {
                                if(main_category_values == category.main_category)
                                {
                                    $("<option>").val(category.sub_category).text(category.sub_category).appendTo(bbbb);
                                }
                            });  
                        });
                    }); 
                });
            });
        }   

    </script>

DAO:-

    public List<Category> selectonlymaincategory() throws SQLException, ClassNotFoundException
    {
        List<Category> listCategory = new ArrayList<>();
        
        Connection conn = DatabaseConnection.initializeDatabase();
        
        String query = " select distinct main_category from list_of_categories order by main_category ";
        
        try
        {
            PreparedStatement prestmt = conn.prepareStatement(query);
             
            ResultSet rs = prestmt.executeQuery();
             
             while (rs.next())
             {                       
                 String main_category = rs.getString("main_category");

                 listCategory.add(new Category(main_category));
             }  
        } 
         
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
        
        return listCategory;        
    }
    
    public List<Category> selectonlysubcategory() throws SQLException, ClassNotFoundException
    {
        List<Category> listCategory = new ArrayList<>();
        
        Connection conn = DatabaseConnection.initializeDatabase();
        
        String query = " select * from list_of_categories order by sub_category";
        
        try
        {
            PreparedStatement prestmt = conn.prepareStatement(query);
             
            ResultSet rs = prestmt.executeQuery();
             
             while (rs.next())
             {                       
                 int id = rs.getInt("id");                           
                 String main_category = rs.getString("main_category");
                 String sub_category = rs.getString("sub_category");

                 listCategory.add(new Category(id,main_category,sub_category));
             }  
        } 
         
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
        
        return listCategory;        
    }

Controller Servlet:-

    public void populate_maindropdown(HttpServletRequest request, HttpServletResponse response)throws SQLException, IOException, ClassNotFoundException, ServletException, ParseException
    {
        try
        {
            List<Category> listCategory = productDAO.selectonlymaincategory();
            
            String json = new Gson().toJson(listCategory);
            
            response.setContentType("application/json");
            
            response.setCharacterEncoding("UTF-8");
            
            response.getWriter().write(json);
        }
        
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new ServletException(e);
        }
    }
    public void populate_subdropdown(HttpServletRequest request, HttpServletResponse response)throws SQLException, IOException, ClassNotFoundException, ServletException, ParseException
    {
        try
        {
            List<Category> listCategory = productDAO.selectonlysubcategory();
            
            String json = new Gson().toJson(listCategory);
            
            response.setContentType("application/json");
            
            response.setCharacterEncoding("UTF-8");
            
            response.getWriter().write(json);
        }
        
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new ServletException(e);
        }
    }