Jay
Hey Guy/Gals!
I need someone help to guide me in creating a drop down menu, where the first menu affects the second menu and it's selection. So in my database I have a category table that has a ID, parentID, and name. parentID is a FK to the PK ID within the same table. Can anyone please give me guidance or some links that may help me. I have been searching the interwebs for the past hour so I don't know how useful links will be to me, since I have literally searched everything. But any help would be nice.
I am guessing I will need to use JSP + AJAX to make this dynamic. Can anyone please help me soon?
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SELECT Operation</title>
</head>
<body>
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://uml.cs.uga.edu:3306/team6"
user="team6" password="overload"/>
<sql:query dataSource="${snapshot}" var="result">
SELECT * FROM Category WHERE parentID IS NULL;
</sql:query>
<select>
<c:forEach var="row" items="${result.rows}">
<option value="${row.id}" id="parent_id">${row.name}</option>
</c:forEach>
</select>
<select>
<c:forEach var="row" items="${result.rows}">
<option value="${row.id}" id="parent_id">${row.name}</option>
</c:forEach>
</select>
</body>
</html>
How Category is setup (NOT THE EXACT SCRIPT):
CREATE TABLE Category
CREATE TABLE 'Category'(
'ID' int(11) , NOT NULL, auto_increment,
'parentID', int(111),
'name', varchar(255), NOT NULL,
PRIMARY KEY ('id')
);
Also 'parentID' is a FK to the PK 'ID'.
Thanks ahead of time for any help!
December 3, 2012 at 5:14 PM
The given code will help you to create a dependent dropdown box.
1)country.jsp:
<%@page import="java.sql.*"%>
<html>
<head>
<script language="javascript" type="text/javascript">
var xmlHttp
var xmlHttp
function showState(str){
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
alert ("Browser does not support HTTP Request")
return
}
var url="state.jsp";
url +="?count=" +str;
xmlHttp.onreadystatechange = stateChange;
xmlHttp.open("GET", url, true);
xmlHttp.send(null);
}
function stateChange(){
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete"){
document.getElementById("state").innerHTML=xmlHttp.responseText
}
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
//Internet Explorer
try
{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e)
{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}
</script>
</head>
<body>
<select name='country' onchange="showState(this.value)">
<option value="none">Select</option>
<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from country");
while(rs.next()){
%>
<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>
<br>
<div id='state'>
<select name='state' >
<option value='-1'></option>
</select>
</div>
</body>
</html>
2)state.jsp:
<%@page import="java.sql.*"%>
<%
String country=request.getParameter("count");
String buffer="<select name='state' ><option value='-1'>Select</option>";
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("Select * from state where countryid='"+country+"' ");
while(rs.next()){
buffer=buffer+"<option value='"+rs.getString(1)+"'>"+rs.getString(3)+"</option>";
}
buffer=buffer+"</select>";
response.getWriter().println(buffer);
}
catch(Exception e){
System.out.println(e);
}
%>
For the above code, we have created two database tables:
CREATE TABLE `country` (
`countryid` bigint(255) NOT NULL auto_increment,
`countryname` varchar(255) default NULL,
PRIMARY KEY (`countryid`));
CREATE TABLE `state` (
`stateid` bigint(255) NOT NULL auto_increment,
`countryid` int(255) default NULL,
`state` varchar(255) default NULL,
PRIMARY KEY (`stateid`));
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
