?
Solved

How select mysql query display in div tag with scrollbar?

Posted on 2009-02-10
8
Medium Priority
?
551 Views
Last Modified: 2013-11-24
Hi
I need to present the query result in a <div> with scrollbar. When user clicks the result list, it will trigger another sql. How can I do it?

How should I change the following css and jsp code in order to do it?
jsp page:
<%
MySQLConnector db = new MySQLConnector();
Connection con = db.Getcon();
Statement s = con.createStatement();
%>
<form name="browsejsp" action="browse" method="post">
Browse and Select Result:<br>   
<div class="scrollbar" id="Layer1"> 	
<%  
String sql = "SELECT s.id as id, s.name as name FROM customer s";
ResultSet rs = s.executeQuery(sql);
%>
<% while (rs.next()) {%>
<%=rs.getInt("id")%><%=rs.getString("name")%>
<%}%>
<%
rs.close();
s.close();
con.close();
%>	
</div>
</form>
 
css:
#Layer1 {
position:absolute;
left:301px;
top:268px;
width:654px;
height:503px;
z-index:2;
overflow: auto;
}
.scrollbar { width:629px;
height:397px;
left:364px;
top:221px;
overflow: auto;
scrollbar-base-color: #898989;
scrollbar-arrow-color: #000000;
scrollbar-3dlight-color: #000000;
scrollbar-darkshadow-color: #000000;
scrollbar-face-color: #898989;
scrollbar-highlight-color: #d7d2d2;
scrollbar-shadow-color: #000000;
scrollbar-track-color: #d7d2d2;
margin: auto;
}

Open in new window

0
Comment
Question by:techques
  • 5
  • 3
8 Comments
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23600858
you want to click anywhere in the result and fire the same query or you want to change your query depending upon, which row user clicks?

Also you will need another jsp ( or submit back to the same jsp ) to run another query again..
0
 

Author Comment

by:techques
ID: 23601486
you want to click anywhere in the result and fire the same query or you want to change your query depending upon, which row user clicks?

1) There is a search function to show result in <div> row by row.
2) When user click one row of the result, it will execute another update query to increase value by 1 of the selected id item.
So, it should be "change your query depending upon, which row user clicks"

Also you will need another jsp ( or submit back to the same jsp ) to run another query again

May I ask how should I do that?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 23602811
well for that you should create an table inside your DIV with links\button or something which user can click.
change the code as below

<div class="scrollbar" id="Layer1">    
<table>
<%  
String sql = "SELECT s.id as id, s.name as name FROM customer s";
ResultSet rs = s.executeQuery(sql);
%>
<% while (rs.next()) {%>
<tr><td>
<%=rs.getInt("id")%></td><td><%=rs.getString("name")%></td><td><a href="newjsp.jsp?id="<%=rs.getInt("id")%>>click me</a></td></tr>
<%}%>
<%
rs.close();
s.close();
con.close();
%>      
</table>
</div>


now you need to create another jsp (newjsp.jsp).. and do following.

<%
MySQLConnector db = new MySQLConnector();
Connection con = db.Getcon();
Statement s = con.createStatement();
String id = request.getParameter("id");
String sql = "update whatever you want to update value="+id;
ResultSet rs = s.executeQuery(sql);
%>
..


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:techques
ID: 23603515
Dear Sir

I followed your instruction and re-write the following code for the search function. However, it cannot get the variable 'location' and 'festival'.

How should I fix it?

I want the search result display under the search drop down menu.
<%
MySQLConnector db = new MySQLConnector();
Connection con = db.Getcon();
Statement s = con.createStatement();
Statement s1 = con.createStatement();
%>
<form>
Search conditions:<br>  	
Location* : 
<%  
String sql1 = "SELECT s.id as id, s.location as location FROM location s";
ResultSet rs1 = s.executeQuery(sql1);
%>
<select NAME="location">
<option VALUE="0" selected>Please select location
<% while (rs1.next()) {%>
<option VALUE="<%=rs1.getInt("id")%>"><%=rs1.getString("location")%>
<%}%>
</select>
Festival* : 
<%  
String sql2 = "SELECT s.id as id, s.festival as festival FROM festival s";
ResultSet rs2 = s.executeQuery(sql2);
%>
<select NAME="festival">
<option VALUE="0" selected>Please select festival
<% while (rs2.next()) {%>
<option VALUE="<%=rs2.getInt("id")%>"><%=rs2.getString("festival")%>
<%}%>
</select>
<%
rs1.close();
rs2.close();
s.close();
%>	
<input type="submit" name="Submit" value="Search" >
Search Result:<br>    	
</form>
<div class="scrollbar" id="Layer1">     
<table>
<%  
String sql1 = "SELECT s.id as id, s.name as name FROM customer s where location=" + location + " and festival=" + festival;
ResultSet rs1 = s10.executeQuery(sql10);
%>
<% while (rs1.next()) {%>
<tr><td>
<%=rs1.getInt("id")%></td><td><%=rs1.getString("name")%></td><td><a href="matrix.jsp?id=<%=rs1.getInt("id")%>">click me</a></td></tr>
<%}%>
<%
rs1.close();
s1.close();
con.close();
%>      
</table>
</div>

Open in new window

0
 

Author Comment

by:techques
ID: 23603522
sorry, this line has error:
String sql1 = "SELECT s.id as id, s.name as name FROM customer s where location=" + location + " and festival=" + festival;
 
cannot read location and festival from the drop down menu. 

Open in new window

0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 600 total points
ID: 23603708
your code at the bottom needs to be inside a IF condition..

until user selects the values and hits Submit, you can not access those values...

your code needs some basic modifications..

see below & then try to understand what changes I have placed there..

In the code below, you should see the screen with drop downs and no search results initially..
once user selects values and hits submit, you shd see your search results.


<%
MySQLConnector db = new MySQLConnector();
Connection con = db.Getcon();
Statement s = con.createStatement();
Statement s1 = con.createStatement();
%>
<form>
Search conditions:<br>          
Location* : 
<%  
String sql1 = "SELECT s.id as id, s.location as location FROM location s";
ResultSet rs1 = s.executeQuery(sql1);
%>
<select NAME="location">
<option VALUE="0" selected>Please select location
<% while (rs1.next()) {%>
<option VALUE="<%=rs1.getInt("id")%>"><%=rs1.getString("location")%>
<%}%>
</select>
Festival* : 
<%  
String sql2 = "SELECT s.id as id, s.festival as festival FROM festival s";
ResultSet rs2 = s.executeQuery(sql2);
%>
<select NAME="festival">
<option VALUE="0" selected>Please select festival
<% while (rs2.next()) {%>
<option VALUE="<%=rs2.getInt("id")%>"><%=rs2.getString("festival")%>
<%}%>
</select>
<%
rs1.close();
rs2.close();
s.close();
%>      
<input type="submit" name="Submit" value="Search" >
Search Result:<br>      
</form>
<div class="scrollbar" id="Layer1">     
<table>
<%  
if(request.getParameter("submit")!=null){
String location = request.getParameter("location");
String location = request.getParameter("festival");
String sql1 = "SELECT s.id as id, s.name as name FROM customer s where location=" + location + " and festival=" + festival;
ResultSet rs1 = s10.executeQuery(sql10);
%>
<% while (rs1.next()) {%>
<tr><td>
<%=rs1.getInt("id")%></td><td><%=rs1.getString("name")%></td><td><a href="matrix.jsp?id=<%=rs1.getInt("id")%>">click me</a></td></tr>
<%}%>
<%
rs1.close();
s1.close();
con.close();
}
%>      
</table>
</div>

Open in new window

0
 

Author Comment

by:techques
ID: 23603907
Thank you very much, I understand now. I need to get the parameters
if(request.getParameter("submit")!=null){
String location = request.getParameter("location");
String location = request.getParameter("festival");

This question is hard to me, I add more points for it.
0
 

Author Closing Comment

by:techques
ID: 31545011
Excellent example, good and clear instruction. Quick response.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
A solution for Fortify Path Manipulation.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

578 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