Jasbir21
asked on
Search by keyword..
Hi,
I posted a question at :
https://www.experts-exchange.com/questions/20788645/Search-by-keyword-skill-display-in-a-table.html
and some modifications needs to be done such as :
choosing from different tables, displaying it and etc.Could anyone help me, i really need this urgently.
The modifications:
->Choose from diferrent table.
I have a table called organ that has username,organname.
A table skil that has skillid,skilldescription
A table project that has projectname,username,durat ion,skilli d ,and etc..
If the user clicks an organ name mer22,all the projects under the organation should appear
if the user clicks all, everything should apper and etc.
If you need more explanation,pls letme know
I posted a question at :
https://www.experts-exchange.com/questions/20788645/Search-by-keyword-skill-display-in-a-table.html
and some modifications needs to be done such as :
choosing from different tables, displaying it and etc.Could anyone help me, i really need this urgently.
The modifications:
->Choose from diferrent table.
I have a table called organ that has username,organname.
A table skil that has skillid,skilldescription
A table project that has projectname,username,durat
If the user clicks an organ name mer22,all the projects under the organation should appear
if the user clicks all, everything should apper and etc.
If you need more explanation,pls letme know
ASKER
Hi,
The jsp :( i have inserted in commands)
<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTo kenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Organizations</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="ti.jsp" name="form1">
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by Skill &nb sp; & nbsp;  ; &nb sp; & nbsp;  ; &nb sp; :
<select name="choose">
<option value="">Select a skill</option>
<option value="*">All</option>
<%
String connectionURL = "jdbc:mysql://localhost:33 06/mydatab ase?user=; password=" ;
Connection connection = null;
Statement stmt=null;
ResultSet rs=null;
try
{
Class.forName("com.mysql.j dbc.Driver ").newInst ance();
connection = DriverManager.getConnectio n(connecti onURL, "", "");
String myskill="select * from skill";
stmt=connection.createStat ement();
rs=stmt.executeQuery(myski ll);
while(rs.next())
{
%>
<option value=<%= rs.getString("skillid") %>><%= rs.getString("skillid") %></option>
<%
}
// close this!!
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
%>
</select>
</p>
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by keyword & nbsp;  ; &nb sp; :
<input type="text" name="keyword" size=20/>
&n bsp;
<input type="submit" value="Go">
<br><br><hr>
<%
//this is for search by keyword where the user can enter anything
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword );
String where = "" ;
if( keyword != null && keyword.trim().length()!= 0 )
{
StringTokenizer st = new StringTokenizer( keyword ) ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " ( " ;
String word = st.nextToken() ;
//it searches the keyword with description field and skillid which is in the skill table..
where += "( skilldescription LIKE '%" + word +"%'OR skillid LIKE '%" + word +"%')" ;
if( st.hasMoreTokens() )
where += " OR " ;
else
where += " ) " ;
}
}
//This takes the chose chosen by the user from the combo
//currently the query is stored in the where string and now you combine the where with the new query of the choose from combo...
String choose = request.getParameter( "choose" );
if( choose != null && choose.trim().length() != 0 )
{
if( where.length() > 0 )
where += " OR" ;
where += "( skillid = '" + choose + "' )" ;
}
if( where.length() > 0 )
{
%>
<table border="4"BGColor="#FFF8DC " width="60%" CELLPADDING="4" style="margin-left:190px;" >
<tr>
<td align=center>
Skill ID
</td>
<td align=center>
Description
</td>
</tr>
</table>
<table border="1"BGColor="#FFFFF0 " width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
String query=null;
where = " WHERE " + where ;
if(choose.equals("*"))
query="Select skillid,skilldescription from skill";
else
query= "Select skillid,skilldescription from skill " + where ;
// PRINT OUT THE QUERY FOR DEBUG PURPOSES
out.println( "<b>Executing " + query + "</b>" ) ;
stmt=connection.createStat ement();
rs = stmt.executeQuery( query ) ;
//This is the place where result is printed out
if( rs.next() )
{
do
{
%>
<tr>
<td align=center><%= rs.getString( "skillid" ) %></td>
<td align=center> <%= rs.getString( "skilldescription" ) %></td>
</tr>
<%
} while( rs.next() ) ;
}
else
{
%>
<tr>
<td colspan="2" align=center>Sorry, no results found</td>
</tr>
<%
}
%>
<%
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
connection.close() ;
connection = null ;
%>
</table>
<%
}
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
finally
{
// MAKE SURE ALL DB THINGS ARE CLOSED
try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
}
%>
</form>
</body>
</html>
The whole thing was done in one jsp
Thanks
The jsp :( i have inserted in commands)
<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTo
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Organizations</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="ti.jsp" name="form1">
&n
&n
Search by Skill &nb
<select name="choose">
<option value="">Select a skill</option>
<option value="*">All</option>
<%
String connectionURL = "jdbc:mysql://localhost:33
Connection connection = null;
Statement stmt=null;
ResultSet rs=null;
try
{
Class.forName("com.mysql.j
connection = DriverManager.getConnectio
String myskill="select * from skill";
stmt=connection.createStat
rs=stmt.executeQuery(myski
while(rs.next())
{
%>
<option value=<%= rs.getString("skillid") %>><%= rs.getString("skillid") %></option>
<%
}
// close this!!
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
%>
</select>
</p>
&n
&n
Search by keyword &
<input type="text" name="keyword" size=20/>
&n
<input type="submit" value="Go">
<br><br><hr>
<%
//this is for search by keyword where the user can enter anything
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword
String where = "" ;
if( keyword != null && keyword.trim().length()!= 0 )
{
StringTokenizer st = new StringTokenizer( keyword ) ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " ( " ;
String word = st.nextToken() ;
//it searches the keyword with description field and skillid which is in the skill table..
where += "( skilldescription LIKE '%" + word +"%'OR skillid LIKE '%" + word +"%')" ;
if( st.hasMoreTokens() )
where += " OR " ;
else
where += " ) " ;
}
}
//This takes the chose chosen by the user from the combo
//currently the query is stored in the where string and now you combine the where with the new query of the choose from combo...
String choose = request.getParameter( "choose" );
if( choose != null && choose.trim().length() != 0 )
{
if( where.length() > 0 )
where += " OR" ;
where += "( skillid = '" + choose + "' )" ;
}
if( where.length() > 0 )
{
%>
<table border="4"BGColor="#FFF8DC
<tr>
<td align=center>
Skill ID
</td>
<td align=center>
Description
</td>
</tr>
</table>
<table border="1"BGColor="#FFFFF0
<%
String query=null;
where = " WHERE " + where ;
if(choose.equals("*"))
query="Select skillid,skilldescription from skill";
else
query= "Select skillid,skilldescription from skill " + where ;
// PRINT OUT THE QUERY FOR DEBUG PURPOSES
out.println( "<b>Executing " + query + "</b>" ) ;
stmt=connection.createStat
rs = stmt.executeQuery( query ) ;
//This is the place where result is printed out
if( rs.next() )
{
do
{
%>
<tr>
<td align=center><%= rs.getString( "skillid" ) %></td>
<td align=center> <%= rs.getString( "skilldescription" ) %></td>
</tr>
<%
} while( rs.next() ) ;
}
else
{
%>
<tr>
<td colspan="2" align=center>Sorry, no results found</td>
</tr>
<%
}
%>
<%
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
connection.close() ;
connection = null ;
%>
</table>
<%
}
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
finally
{
// MAKE SURE ALL DB THINGS ARE CLOSED
try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
}
%>
</form>
</body>
</html>
The whole thing was done in one jsp
Thanks
Okay so as far as I understand it, You have got it going for one table out of three. now you want to extend it for next two more tables....
what I would suggest is this.
Create one more jsp which is having a drop down with your three table names.
let it submit to t1.jsp and in t1 jsp get your file name from the parameter that is coming to it.
something like this....:
<form action="ti.jsp" name="form1">
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by Skill &nb sp; & nbsp;  ; &nb sp; & nbsp;  ; &nb sp; :
<select name="filename">
<option value="skill">skill</optio n>
<option value="oragn">organization </option>
<option value="project">project</o ption>
</select>
</p>
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by keyword & nbsp;  ; &nb sp; :
<input type="submit" value="Go">
<br><br><hr>
</form>
now in t1.jsp make following changes
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>
<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTo kenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<%String fname=request.getParameter ("filename ");
if(fname==null)
fname=skill;
%>
<html>
<body BGColor="#FFFFF0">
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Organizations</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="ti.jsp" name="form1">
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by Skill &nb sp; & nbsp;  ; &nb sp; & nbsp;  ; &nb sp; :
<select name="choose">
<option value="">Select a <%=fname%></option>
<option value="*">All</option>
<%
String connectionURL = "jdbc:mysql://localhost:33 06/mydatab ase?user=; password=" ;
Connection connection = null;
Statement stmt=null;
ResultSet rs=null;
try
{
Class.forName("com.mysql.j dbc.Driver ").newInst ance();
connection = DriverManager.getConnectio n(connecti onURL, "", "");
String myskill="select * from "+fname;
stmt=connection.createStat ement();
rs=stmt.executeQuery(myski ll);
while(rs.next())
{
%>
<option value=<%= rs.getString(1) %>><%= rs.getString(1) %></option>
<%
}
// close this!!
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
%>
</select>
</p>
&n bsp; &nbs p; &n bsp; &nbs p;
&n bsp; &nbs p;
Search by keyword & nbsp;  ; &nb sp; :
<input type="text" name="keyword" size=20/>
&n bsp;
<input type="submit" value="Go">
<br><br><hr>
<%
//this is for search by keyword where the user can enter anything
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword );
String where = "" ;
if( keyword != null && keyword.trim().length()!= 0 )
{
StringTokenizer st = new StringTokenizer( keyword ) ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " ( " ;
String word = st.nextToken() ;
//it searches the keyword with description field and skillid which is in the skill table..
>>>>>>>>>>>//Here you will have to put a if condition using the filename.
if(fname.equals("skills"))
where += "( skilldescription LIKE '%" + word +"%'OR skillid LIKE '%" + word +"%')" ;
if(fname.equals("project") )
where += "( projectdescription LIKE '%" + word +"%'OR projectid LIKE '%" + word +"%')" ;
if(fname.equals("organ"))
where += "( organdescription LIKE '%" + word +"%'OR organid LIKE '%" + word +"%')" ;
//Or something like this
if( st.hasMoreTokens() )
where += " OR " ;
else
where += " ) " ;
}
}
//This takes the chose chosen by the user from the combo
//currently the query is stored in the where string and now you combine the where with the new query of the choose from combo...
String choose = request.getParameter( "choose" );
if( choose != null && choose.trim().length() != 0 )
{
if( where.length() > 0 )
where += " OR" ;
//Same if condtions here as well
where += "( skillid = '" + choose + "' )" ;
}
if( where.length() > 0 )
{
%>
<table border="4"BGColor="#FFF8DC " width="60%" CELLPADDING="4" style="margin-left:190px;" >
<tr>
<td align=center>
Skill ID
</td>
<td align=center>
Description
</td>
</tr>
</table>
<table border="1"BGColor="#FFFFF0 " width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
String query=null;
where = " WHERE " + where ;
if(choose.equals("*"))
//All this qry will also change depending upon what value is present in parameter fname....
query="Select skillid,skilldescription from skill";
else
query= "Select skillid,skilldescription from skill " + where ;
// PRINT OUT THE QUERY FOR DEBUG PURPOSES
out.println( "<b>Executing " + query + "</b>" ) ;
stmt=connection.createStat ement();
rs = stmt.executeQuery( query ) ;
//This is the place where result is printed out
if( rs.next() )
{
do
{
%>
<tr>
<td align=center><%= rs.getString( "skillid" ) %></td>
<td align=center> <%= rs.getString( "skilldescription" ) %></td>
</tr>
<%
} while( rs.next() ) ;
}
else
{
%>
<tr>
<td colspan="2" align=center>Sorry, no results found</td>
</tr>
<%
}
%>
<%
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
connection.close() ;
connection = null ;
%>
</table>
<%
}
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
finally
{
// MAKE SURE ALL DB THINGS ARE CLOSED
try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
}
%>
</form>
</body>
</html>
Please let me know if you need more help.
Regards
what I would suggest is this.
Create one more jsp which is having a drop down with your three table names.
let it submit to t1.jsp and in t1 jsp get your file name from the parameter that is coming to it.
something like this....:
<form action="ti.jsp" name="form1">
&n
&n
Search by Skill &nb
<select name="filename">
<option value="skill">skill</optio
<option value="oragn">organization
<option value="project">project</o
</select>
</p>
&n
&n
Search by keyword &
<input type="submit" value="Go">
<br><br><hr>
</form>
now in t1.jsp make following changes
>>>>>>>>>>>>>>>>>>>>>>>>>>
<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTo
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<%String fname=request.getParameter
if(fname==null)
fname=skill;
%>
<html>
<body BGColor="#FFFFF0">
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Organizations</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="ti.jsp" name="form1">
&n
&n
Search by Skill &nb
<select name="choose">
<option value="">Select a <%=fname%></option>
<option value="*">All</option>
<%
String connectionURL = "jdbc:mysql://localhost:33
Connection connection = null;
Statement stmt=null;
ResultSet rs=null;
try
{
Class.forName("com.mysql.j
connection = DriverManager.getConnectio
String myskill="select * from "+fname;
stmt=connection.createStat
rs=stmt.executeQuery(myski
while(rs.next())
{
%>
<option value=<%= rs.getString(1) %>><%= rs.getString(1) %></option>
<%
}
// close this!!
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
%>
</select>
</p>
&n
&n
Search by keyword &
<input type="text" name="keyword" size=20/>
&n
<input type="submit" value="Go">
<br><br><hr>
<%
//this is for search by keyword where the user can enter anything
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword
String where = "" ;
if( keyword != null && keyword.trim().length()!= 0 )
{
StringTokenizer st = new StringTokenizer( keyword ) ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " ( " ;
String word = st.nextToken() ;
//it searches the keyword with description field and skillid which is in the skill table..
>>>>>>>>>>>//Here you will have to put a if condition using the filename.
if(fname.equals("skills"))
where += "( skilldescription LIKE '%" + word +"%'OR skillid LIKE '%" + word +"%')" ;
if(fname.equals("project")
where += "( projectdescription LIKE '%" + word +"%'OR projectid LIKE '%" + word +"%')" ;
if(fname.equals("organ"))
where += "( organdescription LIKE '%" + word +"%'OR organid LIKE '%" + word +"%')" ;
//Or something like this
if( st.hasMoreTokens() )
where += " OR " ;
else
where += " ) " ;
}
}
//This takes the chose chosen by the user from the combo
//currently the query is stored in the where string and now you combine the where with the new query of the choose from combo...
String choose = request.getParameter( "choose" );
if( choose != null && choose.trim().length() != 0 )
{
if( where.length() > 0 )
where += " OR" ;
//Same if condtions here as well
where += "( skillid = '" + choose + "' )" ;
}
if( where.length() > 0 )
{
%>
<table border="4"BGColor="#FFF8DC
<tr>
<td align=center>
Skill ID
</td>
<td align=center>
Description
</td>
</tr>
</table>
<table border="1"BGColor="#FFFFF0
<%
String query=null;
where = " WHERE " + where ;
if(choose.equals("*"))
//All this qry will also change depending upon what value is present in parameter fname....
query="Select skillid,skilldescription from skill";
else
query= "Select skillid,skilldescription from skill " + where ;
// PRINT OUT THE QUERY FOR DEBUG PURPOSES
out.println( "<b>Executing " + query + "</b>" ) ;
stmt=connection.createStat
rs = stmt.executeQuery( query ) ;
//This is the place where result is printed out
if( rs.next() )
{
do
{
%>
<tr>
<td align=center><%= rs.getString( "skillid" ) %></td>
<td align=center> <%= rs.getString( "skilldescription" ) %></td>
</tr>
<%
} while( rs.next() ) ;
}
else
{
%>
<tr>
<td colspan="2" align=center>Sorry, no results found</td>
</tr>
<%
}
%>
<%
rs.close() ;
rs = null ;
stmt.close() ;
stmt = null ;
connection.close() ;
connection = null ;
%>
</table>
<%
}
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
finally
{
// MAKE SURE ALL DB THINGS ARE CLOSED
try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
}
%>
</form>
</body>
</html>
Please let me know if you need more help.
Regards
ASKER
Hi,
I think understand , but what my lecturer wants is something like this:
Search by skill: <rs.....>(skills from the database),
Search by organization:<rs.....>(org anization names from the database)
Search by all : user can enter anything
For example, if a user choses teh Teacher option, and then click Search.The names of all the organization with the projects that needs the skill gets displayed.
However if he choses the organization (maybe MErcy), again the table concerning the project gets displayed.
However he could also select Teacher option(from combo), select Mercy from combo and then presses Search,
Only mercy with the data skills gets displayed(including the projects gets displayed)
However a user could choose skill from (nurse)combo, organization (little Lamb), and then enter project info in search by keyword and press search, the info gets displayed
However this same info can be obtained by entering Teacher,Mercy at the search by keyword..
Currently, what can be done is that a user could either use
Search by skill: <rs....>
or
use
Search by keyword: where a user can enter skil or descripiton.
.
Thanks
I think understand , but what my lecturer wants is something like this:
Search by skill: <rs.....>(skills from the database),
Search by organization:<rs.....>(org
Search by all : user can enter anything
For example, if a user choses teh Teacher option, and then click Search.The names of all the organization with the projects that needs the skill gets displayed.
However if he choses the organization (maybe MErcy), again the table concerning the project gets displayed.
However he could also select Teacher option(from combo), select Mercy from combo and then presses Search,
Only mercy with the data skills gets displayed(including the projects gets displayed)
However a user could choose skill from (nurse)combo, organization (little Lamb), and then enter project info in search by keyword and press search, the info gets displayed
However this same info can be obtained by entering Teacher,Mercy at the search by keyword..
Currently, what can be done is that a user could either use
Search by skill: <rs....>
or
use
Search by keyword: where a user can enter skil or descripiton.
.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you, i am trying it out now...
ASKER
Hi,
That part works, but know i am having trouble with something like...
..I mean the table gets displayed with all the info but could it be in links. This is because
all info gets displayed in one page.
Something like, when the user enters queries,either by keyword,combo,project and presses Search,
It gets searches but the organizaitonname in links, when the user click the link, the table then
gets displayed.
Mercy(this is a link)
Little lamb(this is a link)
Could it be done?
Thanks
That part works, but know i am having trouble with something like...
..I mean the table gets displayed with all the info but could it be in links. This is because
all info gets displayed in one page.
Something like, when the user enters queries,either by keyword,combo,project and presses Search,
It gets searches but the organizaitonname in links, when the user click the link, the table then
gets displayed.
Mercy(this is a link)
Little lamb(this is a link)
Could it be done?
Thanks
Hmm........ well it should not be that tough a job but when user clicks on the link instead of the table you should be showing them a page full with details of project.
ASKER
..Is it something like passing ids to the link, or do you pass the result to be displayed at the link?
IDs here means the query entered by the user,
Thanks
IDs here means the query entered by the user,
Thanks
(both means one that asks user to select the search crietria and next in which you show the results...
Regards