CODEWITHSUNDEEP
jspsearch
AliDeV
AliDeV

Reputation: 213

error in using search in jsp

I'm working on a jsp page that the user can browse through collection of movies by using a search form that allows him to search by category, title, or release dyear. If there is not any search condition, all movies should be displayed. my problem is that only *the search by release year works fine, but searching by title not working and it just displays nothing with no results*searching by title works when I put it at the bottom of the year search doesn't work, and Vice versa 

 <% 

         String s1 = request.getParameter("Ttl");
         String s2 = request.getParameter("Yr");

       out.println("<TABLE >");
      out.println("<TR>");
      out.println("<TH>Category</TH>");
      out.println("<TH>Title</TH>");
      out.println("<TH>Release Year</TH>");
      out.println("<TH>Rental Price</TH>");
      out.println(" <TH></TH>");
      out.println("<TH></TH>");
      out.println(" </TR>");

          String sql = //search if no search condition
        "SELECT *" +
        " FROM users" +
        " WHERE ";

          if (s1!=null && !(s1.equals(""))){ //search by title 
                       sql =  sql +
        "title LIKE '%" +
        s1 + "%'";
                                             } 
              if (s2!=null){                   //search by year
                       sql =  sql +
        " year LIKE '%" +
        s2 + "%'";
                                             }  



     try{  
  Connection con =
        DriverManager.getConnection("jdbc:odbc:UsersData");
      System.out.println("got connection");

    Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(sql);                
      while (rs.next()) {
          out.println("<TR>");
       String id = rs.getString(5);
        out.println("<TD>" + rs.getString(1) + "</TD>");
        out.println("<TD>" +    rs.getString(2) + "</TD>");
        out.println("<TD>" +  rs.getString(3) + "</TD>");
        out.println("<TD>" + rs.getString(4) + "</TD>");
       out.println("<TD><A HREF=Add to shoping cart?id=" + id + ">Add to shoping cart</A></TD>");
        out.println("</TR>");
      }
      s.close();
      con.close();
    }

    catch (SQLException e) {
    }
    catch (Exception e) {
    }
    %>

Upvotes: 0

Views: 117

Answers (2)

Dave Newton
Dave Newton

Reputation: 160191

You need a condition in your SQL, like an OR.

More importantly, even if you must use a scriptlet in JSP, this is a poor way to go about doing it: the HTML should be just that, HTML; there's absolutely no reason to be doing that in the scriptlet too.

Upvotes: 1

Marvo
Marvo

Reputation: 18143

Are you searching by BOTH year and title? If so, I don't see an "AND" in between those two conditional clauses.

Upvotes: 1

Related Questions