How can i select a faculty and it will only show me the departments in that faculty alone. My database name is getDbase and inside here i have three tables: 1. transfaculties table with two rows (faculty_id and facultyname 2. transdepartments table with two rows (department_id and departmentname) 3. transfacdept table with three rows (transfacdept_id, faculty_id and department_id).This is the code I've written This is my first jsp page code called Faculty.jsp
<%@page import="com.sua.db.DbManipulation"%>
<%@page import="java.sql.*" contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="com.sua.db.DbConnection"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
<link href="layout.css" rel="stylesheet">
<link href="forms.css" rel="stylesheet">
<script type="text/javascript" src="jquery-1.11.3-jquery.min.js"></script>
<script type="text/javascript">
jQuery(document).ready(function()
{
jQuery('.faculty').change(function()
{
var idsss=$(this).val();
var dataString = 'fac_id='+ idsss;
jQuery.ajax
({
type: "POST",
url: "Department.jsp",
data: dataString,
cache: false,
success: function(html)
{
jQuery('.dept').html(html); // result should be the class name of 'dept' dropdown (representing the Department)
}
});
});
});
</script>
</head>
<body>
<!-- ####################################################################################################### -->
<div id="container">
<div class="wrapper">
<div id="register">
<tr>
<td>Dropdown</td>
<td>
<select name="faculty" class="faculty" style="background-color: #ffffa0">
<option selected="selected">--Select Faculty--</option>
<% //HttpSession session = request.getSession();
String returnFacName = "SELECT `transfacdept`.`transfacdept_id`, `transfacdept`.`faculty_id`, "
+ "`transfacdept`.`department_id`, `transfaculties`.faculty_id, "
+ "`transfaculties`.facultyname "
+ "FROM `transfacdept`,`transfaculties` "
+ "WHERE `transfacdept`.`transfacdept_id` = `transfaculties`.faculty_id";
Connection con = new DbConnection().getConnection();
PreparedStatement st = con.prepareStatement(returnFacName);
//ResultSet rs = ps.executeQuery();
ResultSet rs1 = st.executeQuery();
while(rs1.next())
{
%>
<option value="<%=rs1.getInt("faculty_id")%>">
<%=rs1.getString("facultyname")%>
</option>
<%
}
%>
</select>
</td>
</tr>
<tr>
<td>Dropdown 2:</td>
<td>
<select name="dept" class="dept" style="background-color: #ffffa0">
<option selected="selected">--Select Dept--</option>
</select>
</td>
</tr>
</div>
</div>
<br class="clear" />
</div>
</div>
</body>
</html>
This is my second jsp page code called Department.jsp
<head>
<script type="text/javascript" src="jquery-1.11.3-jquery.min.js"></script>
</head>
<body>
<!-- ####################################################################################################### -->
<div id="container">
<div class="wrapper">
<div id="register">
<td>Dropdown2</td>
<td>
<% Connection con = new DbConnection().getConnection();
//Statement st = con.createStatement( );
String $select = request.getParameter("fac_id");
String returnDeptName = "SELECT `transfacdept`.`transfacdept_id`, `transfacdept`.`faculty_id`, "
+ "`transfacdept`.`department_id`, `transfaculties`.facultyname, "
+ "`transdepartments`.departmentname "
+ "FROM `transfacdept`, `transfaculties`, `transdepartments` "
+ "WHERE `transfaculties`.faculty_id = '"+$select+"' "
+ "AND `transfacdept`.`department_id` = `transdepartments`.department_id "
+ "AND `transfacdept`.`faculty_id` = `transfaculties`.`faculty_id` ";
PreparedStatement st2 = con.prepareStatement(returnDeptName);
ResultSet rs2=st2.executeQuery();
while(rs2.next())
{
%>
<option value="<%=rs2.getInt("department_id")%>">
<%=rs2.getString("departmentname")%>
</option>
<%
}
%>
</td>
</tr>
</div>
</div>
<br class="clear" />
</div>
</div>
<!-- ####################################################################################################### -->
</body>
</html>
Any idea where I'm going wrong? It only gives me the faculty without calling the department from the Jquery
