Sarbu Diana
I have 2 tables: professors(professorid INT PK, name VARCHAR), courses(courseid INT PK, title). I also have a bridge table: professorscourses(profid INT FK, course_id INT FK).
I still didn't find a query for something like this: given the professor name, "x"
Using the bridge table, find all the courses for professor X.
professors prof_id | name 1 John
professors_courses profid | courseid 1 2 1 3 1 5 2 1
courses course_id | title 1 English 2 French 3 Italian 4 Japanese 5 Polish
Given the name "John", seems he has the following courses: French,Italian,Polish
July 27, 2012 at 11:07 AM
Here is a jsp code of dependent dropdown. The given code retrieves the professor names from the database and stored into dropdown. When the user selects any name professor, his/her courses is then displayed in another dropdown box on the same page. We have used Ajax for this.
1)professor.jsp:
<%@page import="java.sql.*"%>
<html>
<head>
<script language="javascript" type="text/javascript">
var xmlHttp
var xmlHttp
function showState(str){
if (typeof XMLHttpRequest != "undefined"){
xmlHttp= new XMLHttpRequest();
}
else if (window.ActiveXObject){
xmlHttp= new ActiveXObject("Microsoft.XMLHTTP");
}
if (xmlHttp==null){
alert("Browser does not support XMLHTTP Request")
return;
}
var url="course.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("course").innerHTML=xmlHttp.responseText
}
}
</script>
</head>
<body>
<select name='professor' 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 professors");
while(rs.next()){
%>
<option value="<%=rs.getString(1)%>"><%=rs.getString(2)%></option>
<%
}
%>
</select>
<br>
<div id='course'>
<select name='course' >
<option value='-1'></option>
</select>
</div>
</body>
</html>
2)course.jsp:
<%@page import="java.sql.*"%>
<%
String profid=request.getParameter("count");
String buffer="<select name='course' ><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 courses where prof_id='"+profid+"' ");
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 `professors` (
`prof_id` bigint(255) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`prof_id`));
CREATE TABLE `courses` (
`courseid` bigint(255) NOT NULL auto_increment,
`prof_id` int(255) default NULL,
`title` varchar(255) default NULL,
PRIMARY KEY (`stateid`));
July 28, 2012 at 7:14 PM
Thank you for your time:) But well I found how now.
String sqlQuery = "SELECT courses.title FROM courses INNER JOIN professors_courses ON professors_courses.course_id = course.course_id WHERE prof_id = '"+x+"'";
I forgot to take into account that user chooses a professor name, I look for he's ID and then I use this Query.
July 28, 2012 at 7:14 PM
Thank you for your time:) But well I found how now.
String sqlQuery = "SELECT courses.title FROM courses INNER JOIN professors_courses ON professors_courses.course_id = course.course_id WHERE prof_id = '"+x+"'";
I forgot to take into account that user chooses a professor name, I look for he's ID and then I use this Query.
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.
