Solved

SEARCH by keyword

Posted on 2003-11-04
20
398 Views
Last Modified: 2010-04-01
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:3306/mydatabase?user=;password=";
Connection connection = null;
ResultSet rs2=null;
ResultSet rs=null;
Statement stmt=null;

try
{
 
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   connection = DriverManager.getConnection(connectionURL, "", "");

 
   String myskill="select * from skill";
 stmt=connection.createStatement();
 rs2=stmt.executeQuery(myskill);


}


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">

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
<select name="choose">

<%
while(rs2.next()){ %>

<option value=<%=rs2.getString("skillid")%>><%=rs2.getString("skillid")%></option>
<%}%>

</select>

</p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
<input type="text" name="keyword" size=20/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<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.getString("skillid")%></td>  
<td align=center> <%=rs.getString("skilldescription")%></td>  
</tr>
<%}
%>
</table>


</form>

<body>
<html>

For example, you can type, help + doc and the system would search it
0
Comment
Question by:Jasbir21
  • 10
  • 9
20 Comments
 
LVL 15

Expert Comment

by:dualsoul
ID: 9685343
hm...i can't understand what you exactly want, but:
  you can split your keyword by '+' delimiter, and search the same way like skill search.

Or please be more precise.
0
 
LVL 35

Accepted Solution

by:
TimYates earned 230 total points
ID: 9685772
StringTokenizer st = new StringTokenizer( keywords ) ;
String where = "" ;

while( st.hasMoreTokens() )
{
    if( where.length() == 0 )
      where = " WHERE " ;
    String word = st.nextToken() ;
    where += "( field LIKE '%" + word + "%' )" ;
    if( st.hasModeTokens() )
        where += " OR " ;
}

String sqlQuery = "SELECT * FROM table " + where ;
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9685815
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.
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 

Author Comment

by:Jasbir21
ID: 9685902
Hi,
  When i tried that i had this error:

java.lang.NullPointerException
      java.util.StringTokenizer.(StringTokenizer.java:119)
      java.util.StringTokenizer.(StringTokenizer.java:149)
      org.apache.jsp.jsp2.viewskill_jsp._jspService(viewskill_jsp.java:145)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

0
 

Author Comment

by:Jasbir21
ID: 9685919
The 145 line is :

StringTokenizer st = new StringTokenizer( keyword ) ;
0
 

Author Comment

by:Jasbir21
ID: 9685924
Tim,
   That i was finding , thanks a lot, but i don't know why the error is.


Thanks
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9685941
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)
0
 

Author Comment

by:Jasbir21
ID: 9685984
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.ServletException: Column not found,  message from server: "Unknown column 'field' in 'where clause'"
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686021
yeah, you have to change "table" and "field" to be the table name and field name you want to search...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686024
in:

    where += "( field LIKE '%" + word + "%' )" ;

and

String sqlQuery = "SELECT * FROM table " + where ;
0
 

Author Comment

by:Jasbir21
ID: 9686085
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
0
 

Author Comment

by:Jasbir21
ID: 9686098
...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(sqlQuery);




 String choose=request.getParameter("choose");

 String query="Select skillid,skilldescription from skill where skillid='"+choose+"'";
 rs=stmt.executeQuery(query);

 while(rs3.next())
               {
%>
               


<tr>
<td align=center><%=rs3.getString("skillid")%></td>  
<td align=center> <%=rs3.getString("skilldescription")%></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
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686102
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...
0
 

Author Comment

by:Jasbir21
ID: 9686171
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
0
 

Author Comment

by:Jasbir21
ID: 9686261
....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..
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686309
> 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...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686316
> ...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!!
0
 

Author Comment

by:Jasbir21
ID: 9686512
Thanks,is it right that i could i either search by keyword or skill but not both???
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686527
Hmmmm...to do both, you would have to combine your SELECT statement...

It is possible, it would just require a bit more work...
0
 

Author Comment

by:Jasbir21
ID: 9686577
Hi,
  I really need to change that, could you pls help me ? I have posted the question at this urL:

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20788645.html

Thanks
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
This problem is more common than not and I will show you some things to check to solve this problem.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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