Link to home
Start Free TrialLog in
Avatar of Jasbir21
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,duration,skillid ,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


Avatar of Kuldeepchaturvedi
Kuldeepchaturvedi
Flag of United States of America image

Hi Jasbir I looked at your previous question and I am kind of not understanding it... can you please post both of your jsps again..?
(both means one that asks user to select the search crietria and next in which you show the results...

Regards
Avatar of Jasbir21
Jasbir21

ASKER

Hi,
The jsp :( i have inserted in commands)

<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, 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">
  &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">
    <option value="">Select a skill</option>
    <option value="*">All</option>


<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  Statement stmt=null;
  ResultSet rs=null;
 
  try
  {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
   
    String myskill="select * from skill";
    stmt=connection.createStatement();
    rs=stmt.executeQuery(myskill);

    while(rs.next())
    {
%>
    <option value=<%= rs.getString("skillid") %>><%= rs.getString("skillid") %></option>
<%
    }

    // close this!!
    rs.close() ;
    rs = null ;
    stmt.close() ;
    stmt = null ;
%>

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

<%
//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.createStatement();
      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">
  &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="filename">
    <option value="skill">skill</option>
    <option value="oragn">organization</option>
  <option value="project">project</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="submit" value="Go">

  <br><br><hr>
</form>
now in t1.jsp make following changes
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, 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">
  &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">
    <option value="">Select a <%=fname%></option>
    <option value="*">All</option>


<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  Statement stmt=null;
  ResultSet rs=null;
 
  try
  {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
   
    String myskill="select * from "+fname;
    stmt=connection.createStatement();
    rs=stmt.executeQuery(myskill);

    while(rs.next())
    {
%>
    <option value=<%= rs.getString(1) %>><%= rs.getString(1) %></option>
<%
    }

    // close this!!
    rs.close() ;
    rs = null ;
    stmt.close() ;
    stmt = null ;
%>

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

<%
//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.createStatement();
      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
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.....>(organization 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
ASKER CERTIFIED SOLUTION
Avatar of Kuldeepchaturvedi
Kuldeepchaturvedi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you, i am trying it out now...
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
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.
..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