Ads
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?
Share on Google+<%@ 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!
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`));
Ads
Ads