Jasbir21
asked on
SEARCH by keyword
Hi,
I am trying to search by skill and search by keyword function. How do i do search by keyword function . The code for search by skill is :
......(using java bean)
<html>
<body BGColor="#FFFFF0">
<%
String connectionURL = "jdbc:mysql://localhost:33 06/mydatab ase?user=; password=" ;
Connection connection = null;
ResultSet rs2=null;
ResultSet rs=null;
Statement stmt=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();
rs2=stmt.executeQuery(mysk ill);
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
%>
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Skills</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="viewskill.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">
<%
while(rs2.next()){ %>
<option value=<%=rs2.getString("sk illid")%>> <%=rs2.get String("sk illid")%>< /option>
<%}%>
</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>
<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 choose=s.getChoose();
String keyword=s.getKeyword();
String query="Select skillid,skilldescription from skill where skillid='"+s.getChoose()+" '";
rs=stmt.executeQuery(query );
while(rs.next())
{
%>
<tr>
<td align=center><%=rs.getStri ng("skilli d")%></td>
<td align=center> <%=rs.getString("skilldesc ription")% ></td>
</tr>
<%}
%>
</table>
</form>
<body>
<html>
For example, you can type, help + doc and the system would search it
I am trying to search by skill and search by keyword function. How do i do search by keyword function . The code for search by skill is :
......(using java bean)
<html>
<body BGColor="#FFFFF0">
<%
String connectionURL = "jdbc:mysql://localhost:33
Connection connection = null;
ResultSet rs2=null;
ResultSet rs=null;
Statement stmt=null;
try
{
Class.forName("com.mysql.j
connection = DriverManager.getConnectio
String myskill="select * from skill";
stmt=connection.createStat
rs2=stmt.executeQuery(mysk
}
catch( SQLException ex )
{
ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
ex.printStackTrace() ;
}
%>
<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Skills</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">
&n
&n
Search by Skill &nb
<select name="choose">
<%
while(rs2.next()){ %>
<option value=<%=rs2.getString("sk
<%}%>
</select>
</p>
&n
&n
Search by keyword &
<input type="text" name="keyword" size=20/>
&n
<input type="submit" value="Go">
<br>
<br>
<hr>
<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 choose=s.getChoose();
String keyword=s.getKeyword();
String query="Select skillid,skilldescription from skill where skillid='"+s.getChoose()+"
rs=stmt.executeQuery(query
while(rs.next())
{
%>
<tr>
<td align=center><%=rs.getStri
<td align=center> <%=rs.getString("skilldesc
</tr>
<%}
%>
</table>
</form>
<body>
<html>
For example, you can type, help + doc and the system would search it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That code should build up an SQL query string so if you type;
hi there
into the keywords field, sqlQuery will end up being:
SELECT * FROM table WHERE ( field LIKE '%hi%' ) OR ( field LIKE '%there%' )
which *should* return all rows from table "table" where "field" contains "hi" or "there"
Hope this is helpful...
Tim.
hi there
into the keywords field, sqlQuery will end up being:
SELECT * FROM table WHERE ( field LIKE '%hi%' ) OR ( field LIKE '%there%' )
which *should* return all rows from table "table" where "field" contains "hi" or "there"
Hope this is helpful...
Tim.
ASKER
Hi,
When i tried that i had this error:
java.lang.NullPointerExcep tion
java.util.StringTokenizer. (StringTok enizer.jav a:119)
java.util.StringTokenizer. (StringTok enizer.jav a:149)
org.apache.jsp.jsp2.viewsk ill_jsp._j spService( viewskill_ jsp.java:1 45)
org.apache.jasper.runtime. HttpJspBas e.service( HttpJspBas e.java:133 )
javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 856)
org.apache.jasper.servlet. JspServlet Wrapper.se rvice(JspS ervletWrap per.java:3 11)
org.apache.jasper.servlet. JspServlet .serviceJs pFile(JspS ervlet.jav a:301)
org.apache.jasper.servlet. JspServlet .service(J spServlet. java:248)
javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 856)
When i tried that i had this error:
java.lang.NullPointerExcep
java.util.StringTokenizer.
java.util.StringTokenizer.
org.apache.jsp.jsp2.viewsk
org.apache.jasper.runtime.
javax.servlet.http.HttpSer
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
ASKER
The 145 line is :
StringTokenizer st = new StringTokenizer( keyword ) ;
StringTokenizer st = new StringTokenizer( keyword ) ;
ASKER
Tim,
That i was finding , thanks a lot, but i don't know why the error is.
Thanks
That i was finding , thanks a lot, but i don't know why the error is.
Thanks
put:
if( keywords == null )
keywords= "" ;
before
StringTokenizer st = new StringTokenizer( keywords ) ;
And make sure you are setting the keywords variable by getting the value from your form post:
String keywords = request.getParameter( "keywords" ) ;
(or whatever the NAME of your KEYWORDS entry box is)
if( keywords == null )
keywords= "" ;
before
StringTokenizer st = new StringTokenizer( keywords ) ;
And make sure you are setting the keywords variable by getting the value from your form post:
String keywords = request.getParameter( "keywords" ) ;
(or whatever the NAME of your KEYWORDS entry box is)
ASKER
Hi,
When i run the program the table come up when i never search anything and when i press something to search i get this error
javax.servlet.ServletExcep tion: Column not found, message from server: "Unknown column 'field' in 'where clause'"
When i run the program the table come up when i never search anything and when i press something to search i get this error
javax.servlet.ServletExcep
yeah, you have to change "table" and "field" to be the table name and field name you want to search...
in:
where += "( field LIKE '%" + word + "%' )" ;
and
String sqlQuery = "SELECT * FROM table " + where ;
where += "( field LIKE '%" + word + "%' )" ;
and
String sqlQuery = "SELECT * FROM table " + where ;
ASKER
Hi,
It works fine, but the whole table gets displayed when i load it,
and it is now whether i could use search by skill or search by keyword and search by skill is always used ,
Pls help me to modify the code
It works fine, but the whole table gets displayed when i load it,
and it is now whether i could use search by skill or search by keyword and search by skill is always used ,
Pls help me to modify the code
ASKER
...the jdbc part :
<%
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword );
if( keyword == null )
keyword= "" ;
StringTokenizer st = new StringTokenizer( keyword ) ;
String where = "" ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " WHERE " ;
String word = st.nextToken() ;
where += "( skilldescription LIKE '%" + word + "%' )" ;
if( st.hasMoreTokens() )
where += " OR " ;
}
String sqlQuery = "SELECT * FROM skill " + where ;
rs3=stmt.executeQuery(sqlQ uery);
String choose=request.getParamete r("choose" );
String query="Select skillid,skilldescription from skill where skillid='"+choose+"'";
rs=stmt.executeQuery(query );
while(rs3.next())
{
%>
<tr>
<td align=center><%=rs3.getStr ing("skill id")%></td >
<td align=center> <%=rs3.getString("skilldes cription") %></td>
</tr>
<%}
%>
</table>
</form>
....with this, the whole table gets displayed when i type the url http://localhost...
...since search by skill is choosen from the drop down list and there is always a value at the drop down list, whether i need it or not , the value is chosen,
if i use search by keyword, does it mean that search by skill cannot now be displayed in table format..
Thanks
<%
String keyword = request.getParameter( "keyword" ) ;
System.out.println(keyword
if( keyword == null )
keyword= "" ;
StringTokenizer st = new StringTokenizer( keyword ) ;
String where = "" ;
while( st.hasMoreTokens() )
{
if( where.length() == 0 )
where = " WHERE " ;
String word = st.nextToken() ;
where += "( skilldescription LIKE '%" + word + "%' )" ;
if( st.hasMoreTokens() )
where += " OR " ;
}
String sqlQuery = "SELECT * FROM skill " + where ;
rs3=stmt.executeQuery(sqlQ
String choose=request.getParamete
String query="Select skillid,skilldescription from skill where skillid='"+choose+"'";
rs=stmt.executeQuery(query
while(rs3.next())
{
%>
<tr>
<td align=center><%=rs3.getStr
<td align=center> <%=rs3.getString("skilldes
</tr>
<%}
%>
</table>
</form>
....with this, the whole table gets displayed when i type the url http://localhost...
...since search by skill is choosen from the drop down list and there is always a value at the drop down list, whether i need it or not , the value is chosen,
if i use search by keyword, does it mean that search by skill cannot now be displayed in table format..
Thanks
Check if the keyword is null or the empty string when you get the page
if it is, no keyword was typed in, so don't execute the query, and don't show the results...
if it is, no keyword was typed in, so don't execute the query, and don't show the results...
ASKER
Hi,
that works but now i having problem search by skill, this is because it is always choosen since it is
from the drop down list, how do i do it so that at the drop down list there might be
a value of empty string and if it is empty string , then it is not chosen..
....but still i could just use search by skill or keyword and not both , is that right???
Thanks
that works but now i having problem search by skill, this is because it is always choosen since it is
from the drop down list, how do i do it so that at the drop down list there might be
a value of empty string and if it is empty string , then it is not chosen..
....but still i could just use search by skill or keyword and not both , is that right???
Thanks
ASKER
....Another problem came up, when i load the form no table is displayed but when i press go with nothing inside, the whole table gets displayed..
> how do i do it so that at the drop down list there might be a value of empty string and if it is empty string , then it is not chosen..
Add a line into the dropdown like:
After this line:
<select name="choose">
Add:
<option value="">Select a skill</option>
Then, choose will send an empty string if they dont select anything, and you can skip the skill search...
Add a line into the dropdown like:
After this line:
<select name="choose">
Add:
<option value="">Select a skill</option>
Then, choose will send an empty string if they dont select anything, and you can skip the skill search...
> ...Another problem came up, when i load the form no table is displayed but when i press go with nothing inside, the whole table gets displayed..
Check for the keyword string being the empty string ""
ie:
if( keywords == null || keywords.trim().length == 0 )
// dont search!!
Check for the keyword string being the empty string ""
ie:
if( keywords == null || keywords.trim().length == 0 )
// dont search!!
ASKER
Thanks,is it right that i could i either search by keyword or skill but not both???
Hmmmm...to do both, you would have to combine your SELECT statement...
It is possible, it would just require a bit more work...
It is possible, it would just require a bit more work...
ASKER
Hi,
I really need to change that, could you pls help me ? I have posted the question at this urL:
https://www.experts-exchange.com/questions/20788645/Search-by-keyword-skill-display-in-a-table.html
Thanks
I really need to change that, could you pls help me ? I have posted the question at this urL:
https://www.experts-exchange.com/questions/20788645/Search-by-keyword-skill-display-in-a-table.html
Thanks
you can split your keyword by '+' delimiter, and search the same way like skill search.
Or please be more precise.