Solved

DropDown Box and SQL

Posted on 2003-11-18
7
368 Views
Last Modified: 2010-04-09
OK...Here goes.

I have three drop down boxes that I want to populate with data from my db.  This is no big deal,
But, I want to filter the results on each dropdown box.  For example, Row, Bay and Shelf (all in same table) are the columns I want
to display.  I created a dropdown box for each one.  They automatically display the contents of the entire db.  
When a user picks a specific Row, I want the Bay and Shelf information updated in their dropdown boxes.  Same thing if a user picks Bay.

Row      Bay      Shelf
1      A      1
1      A      2
1      B      1
2      C      3
2      C      3
2      C      3
3      D      4
3      D      4
3      D      2

Here is a example of the data.  When a user picks Row "1",  Bay A & B and shelves 1 & 2 should only be displayed.


Any Ideas?

Thanks.
sw
0
Comment
Question by:swilli6926
7 Comments
 
LVL 17

Accepted Solution

by:
dorward earned 32 total points
ID: 9771249
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9779576
HI
You want to do that with or without calling the DB again?
Javier
0
 
LVL 2

Assisted Solution

by:jjerome00
jjerome00 earned 31 total points
ID: 9791933

I've done this a few times.  There are 2 ways I know: 1, after each selection of a dropdown, submit the form to call the page again (with the selection as one of the request parameters).  This will work, but if you don't have too much data, there is a much more elegant solution:

Use a combination of server-side script (ASP, JSP, PHP, etc) and javascript.  Create a JavaScript array with the results from the database (loop through the resulting recordset) at the beginning of the page.

For the dropdowns, use the onChange event to call JavaScript functions to repopulate all your dropdowns using the array you created at the top of the file.

It sounds hard, but it really isn't too bad.  I could go into it all, but I don't have my example right in front of me.  Feel free to contact me if your still having this issue.

0
 
LVL 8

Assisted Solution

by:nishikanth
nishikanth earned 31 total points
ID: 9910645
I use ASP for using a triple linked drop down list.

I stored the whole category into an array and converted this into a javascroipt array.

On change event of this drop downlist i loop through the drop down list and fill the other .


SAmple Database table design of my sample.

Pls recreate this and check

CatID      CatName      ParentId      
1      Antiques      0      
2      Art      0      
8      Fiction      1      
10      Children      1      
11      Classics      1      
17      Romance      2      
18      Science      2      
84      Poster      8      
85      Sports       8      
229      Sports       17      
230      TV Shows      17      
231      Others      18      

Click Antiques ->     Fiction, Children & Classics will be displayed
Select Fiction  -> Poster & sports will be displayed


------------

The validations are based on my table design , you use  opwn database table design and change the way validations take place

-----------------------------------------    


<%@ Language=VBScript %>

<HTML>
<HEAD>
</HEAD>
<BODY>


<%
            set rscat = server.CreateObject("adodb.recordset")    
            rscat.Open "select * from category",conn,1,3,1   '''''Pls load your entire table into this

            dim categoryarray

            if not rscat.EOF then
                  categoryarray = rscat.GetRows()
            end if


Call ConvertToJSArray2D(categoryarray,"categoryarrayJS")


%>

<form name = "test">

      <SELECT id=cat name=cat onchange = FillListSub()  class="selectbox">
            <OPTION value=0>Select</OPTION>
            <% for i = 0 to UBound(categoryarray,2)%>
                  <OPTION value = <%=categoryarray(0,i)%>><%=categoryarray(1,i)%></OPTION>
            <%next%>
      </SELECT>

      <SELECT id=subcat name=subcat onchange = FillListSubSub() disabled  class="selectbox">
            <OPTION value=0>Select
            </OPTION>
      </SELECT>

      <SELECT id=subsubcat name=subsubcat) disabled  class="selectbox">
            <OPTION value=0>Select
            </OPTION>
      </SELECT>
      <INPUT type="text" id=text1 name=text1>
      <INPUT type="submit" value="Submit" id=submit1 name=submit1 onsubmit = getcat1()>
      
</form>

<script language = javascript>


      function FillListSub()
      {
            i = 0;
            document.test.subcat.disabled = false;
            document.test.subcat.length=0;    
            document.test.subsubcat.disabled = true;
            document.test.subsubcat.length=0;
            document.test.subsubcat.options[0]=new Option("Select","Select");
            document.test.subcat.options[0]=new Option("Select","Select");
            if ((document.test.cat.selectedIndex)==0 ){
                  document.test.subcat.disabled = true;
                  return;
            }
             
            for ( ctr=0;ctr<<%=ubound(categoryarray,2)%>;ctr++)
            {
            if (categoryarrayJS[2][ctr] == document.test.cat.value )
                  {
                  i = i+1;
                  document.test.subcat.options[i] = new Option(categoryarrayJS[1] [ctr], categoryarrayJS[0][ctr]);
                  }

            }

            if ((document.test.cat.selectedIndex)==0 || document.test.subcat.length == 1)
               {
                 document.test.subcat.disabled = true;
                 document.test.subcat.length=0;  
                 document.test.subcat.options[0]=new Option("Select","Select");
                 document.test.subsubcat.disabled = true;
                 document.test.subsubcat.length=0;    
                 document.test.subsubcat.options[0]=new Option("Select","Select");
               }
      }

      function FillListSubSub()
      {
            i = 0;
            document.test.subsubcat.disabled = false;
            document.test.subsubcat.length=0;    
            document.test.subsubcat.options[0]=new Option("Select");

            for ( ctr=0;ctr<<%=ubound(categoryarray,2)%>;ctr++)
            {
                  if (categoryarrayJS[2][ctr] == document.test.subcat.value)
                  {
                        i = i+1;
                        document.test.subsubcat.options[i] = new Option(categoryarrayJS[1] [ctr], categoryarrayJS[0][ctr]);
                  }
            }

            if(document.test.subcat.selectedIndex == 0 || document.test.subsubcat.length == 1)
            {
                  document.test.subsubcat.disabled = true;
                  document.test.subsubcat.length=0;    
                  document.test.subsubcat.options[0]=new Option("Select");
            }
      }
      
      
</script>


<%

Function ConvertToJSArray2D(VBArray , ArrayName)
      Dim vb2jsRow, vb2jsCol , vb2jsStr, vb2jsi, vb2jsj
      vb2jsRow = Ubound(VBArray,1)
      vb2jsCol = Ubound(VBArray,2)
      %>
      <SCRIPT LANGUAGE = 'JAVASCRIPT' >
            var vb2jsi,vb2jsj
            <%=ArrayName%> = new Array(<%=vb2jsRow+1%>);
            for (vb2jsi=0; vb2jsi < <%=vb2jsRow+1%>; vb2jsi++)
            {
                <%=ArrayName%>[vb2jsi] = new Array(<%=vb2jsCol+1%>)
            for (vb2jsj=0; vb2jsj < <%=vb2jsCol+1%>; vb2jsj++) <%=ArrayName%>[vb2jsi][vb2jsj] = " "
            }
      </SCRIPT>
      <%
      Response.Write("<SCR"&"IPT LANGUAGE = 'JAVASCRIPT' >"&chr(13))
      for vb2jsi=0 to vb2jsRow
            for vb2jsj=0 to vb2jsCol
                vb2jsstr = "VBArray("&vb2jsi&","&vb2jsj&")"
            %>    
                <%=ArrayName%>[<%=vb2jsi%>][<%=vb2jsj%>] = "<%=trim(eval(vb2jsstr))%>"
            <%
            Next
      Next
      Response.Write("</SCR"&"IPT>")
End Function
%>

</BODY>
</HTML>
--------------------
0
 
LVL 1

Assisted Solution

by:doctornick0
doctornick0 earned 31 total points
ID: 10034109
sw --

You can use some snazzy JavaScript and ASP combinations.  See this wonderful article:  http://www.atgconsulting.com/triplelist.asp.  Also, which DB are you using?  If you're using SQL Server or IBM DB2 UDB version 8.x, you can generate the output as XML, which really works well (see the link on the ATG Consulting page to the Object-Oriented linked list boxes).  Remember to feed your data into some well thought-out arrays, otherwise you'll be querying the database too much, which will slow down your output to the point of obsolescence.

-- doctornick0
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
In this tutorial viewers will learn how to define a gradient in CSS. Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Gradient. Define the background as "linear-gradient(to right, #ee3668, black)". Ensure you …
In this tutorial viewers will learn how to position overlapping items using z-index in CSS. They will also learn the restrictions on the z-index property.  Create a new HTML document with an internal stylesheet.: Create a div in CSS and name it Red.…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now