Search by group category description

I would like to write a query to allow the user of my library website written using JSP to search for a book in a different of ways....I have finished to write code to allow to user to search the book by title or author
<sql:query var="booksList" scope="request"  dataSource = "${dB}">
	SELECT * FROM Books
     WHERE    BookTitle LIKE ?
       AND    Author  LIKE ?
       AND    Edition  LIKE ?
	   AND    Year LIKE ?
       AND    categoryID LIKE ?
	<sql:param value="%${param.BookTitle}%" />
	<sql:param value="%${param.Author}%" />
	<sql:param value="%${param.Edition}%" />
	<sql:param value="%${param.Year}%" />
	<sql:param value="%${param.categoryID}%" />
</sql:query>

Open in new window

now the problem is to write code to search by bookcategory( basically i have to query two tables linked by categoryID and should display books titles and authors...

The two tables look llike this ( in access)...

table1 Books
===========

BookTitle      Author      Edition     categoryID

xxxxxx        xxxxx        xxx           001


Table2 Categories

CategoryID      CategoryDescription

001                  Health

The user will be asked to enter categoryDescription of books they would like to search for...The output will be like

BookTitle   Author
xxxxx       xxxxx
ozzyfantaAsked:
Who is Participating?
 
for_yanCommented:

Misspelled Author in the post above.
This is corrected:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Books.Author  FROM Books, Categories
     WHERE    
and Books.CategoryID = Categories.CategoriID
and Category.CategoryDescription like ?
<sql:param value="%${param.CategoryDescription}%" />

0
 
for_yanCommented:

It doesn't seem that for this kind of result ou even ned to link the tables,
as you can just use query:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT BookTitle, Author  FROM Books
     WHERE      categoryID LIKE  ?
<sql:param value="%${param.categoryID}%" />

Do you really like to match just substring with "like"
or direct equality  with

   ...categoryID = ?
  and
<sql:param value="${param.categoryID}" />

Perhaps equality should be more appropriate for categoryID query





0
 
for_yanCommented:
If you want to join tables this is not difficult either, for example:

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Categories.CategoryDescription  FROM Books, Categories
     WHERE    BookTitle LIKE ?
and Book.CategoryID = Categories.CategoriID
<sql:param value="%${param.BookTitle}%" />
<sql:param value="${param.categoryID}" />

This is necessary when you need infgormation from
dufferent tables - e.g.,  BookTitle from one table and
CategoryDescription from another


     
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ozzyfantaAuthor Commented:
it is giving me the following error:

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException:

SELECT Books.BookTitle, Categories.CategoryDescription  FROM Books, Categories
     WHERE    BookTitle LIKE ?
AND Books.CategoryID = Categories.CategoriID

: Unexpected exception : 1
      org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:536)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:410)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
0
 
for_yanCommented:
Perhpas you should look a little bit below in your exceptioon stack trace
where it refers to the  code written by you - not library classes - this stack trace does not say where in
your code the problem happend

Did you assign parameters caorrectly - you don't show it in this snippet?

Do you have access to SQL propmt directly to yoiur database - first
try to run the query you want to execute form your JSP directly
from SQL prompt command line

0
 
ozzyfantaAuthor Commented:
This is the form i have created to get the input from the user ( the input should be a categoryDescription like health,technolgy,etc...)
<html>
<head>
<title>Search by group category</title>
</head>
<body>
Please enter information
<form action="findCategory.jsp" method="get">
<table>
<tr>
<td>Book Description Category:</td>
<td><input type="text" name="CategoryDescription"></td>
</tr>
<tr>
<tr>
<td><input type="submit"  name="submitButton" /></td>
<td><input type="reset"  name="resetButton" /></td>     
</tr>
</table>
</body>
</html>

Open in new window


and the page where i am struggling is called findCategory.jsp..(it will query the databse and forward the result to listCategory.jsp)

the result of the query will be forwarded to this page called listCategory.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<html>
<head>
<title>Search Result</title>
</head>
<body>
<c:choose>
<c:when test="${CatList.rowCount == 0}">
Sorry, no book were found.
</c:when>
<c:otherwise>
The following books were found:
<p>
<table border="1">
<th>BookTitle</th>
<th>CategoryID</th>
<c:forEach items="${CatList.rows}" var="rows">
<tr>
<td><c:out value="${rows.BookTitle}" /></td>
<td><c:out value="${rows.CategoryID}" /></td>
</tr>
</c:forEach>
</table>
<td><a href="category.html">Search by book category</a></td>
</c:otherwise>
</c:choose>
</body>
</html>

Open in new window

0
 
for_yanCommented:
You say that your original query worked for you ?

1) So show the corresponding snippet which you created for this new query.

2) Then look at the excpetion stack trace below of waht you posted - do you see the line referrring to your code,
not standard library code

3) Then try to execute the query as you want it from SQL command prompt

Post the results of 1),2) ,3)
0
 
ozzyfantaAuthor Commented:
in the code you posted for me, i dont think you are taking into accounts that the user input only the CategoryDescription...
You mean i should post the result of my first query? when the user input the BookTitle or Author?

This is the code:
search.html
<html>
<head>
<title>Search in user Database</title>
</head>
<body>
Please enter information
<form action="find.jsp" method="get">
<table>
<tr>
<td>Book Title:</td>
<td><input type="text" name="BookTitle"></td>
</tr>
<tr>
<td>Author:</td>
<td><input type="text" name="Author"></td>
</tr>
<tr>
<td><input type="submit"  name="submitButton" /></td>     
<td><input type="reset"  name="resetButton" /></td>     
</tr>
</table>
</body>
</html>

Open in new window


The code to query the database called find.jsp
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<sql:setDataSource
      var          = "dB"
      scope        = "session" 
      driver       = "sun.jdbc.odbc.JdbcOdbcDriver" 
      url          = "jdbc:odbc:dB"
 /> 

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
	SELECT * FROM Books
     WHERE    BookTitle LIKE ?
       AND    Author  LIKE ?
       AND    Edition  LIKE ?
	   AND    Year LIKE ?
       AND    categoryID LIKE ?
	<sql:param value="%${param.BookTitle}%" />
	<sql:param value="%${param.Author}%" />
	<sql:param value="%${param.Edition}%" />
	<sql:param value="%${param.Year}%" />
	<sql:param value="%${param.categoryID}%" />
</sql:query>
<jsp:forward page="list.jsp" />

Open in new window


and the last one, the display the result of the query, called list.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<html>
<head>
<title>Search Result</title>
</head>
<body>
<c:choose>
<c:when test="${booksList.rowCount == 0}">
Sorry, no book were found.
</c:when>
<c:otherwise>
The following books were found:
<p>
<table border="1">
<th>BookTitle</th>
<th>Author</th>
<c:forEach items="${booksList.rows}" var="rows">
<tr>
<td><c:out value="${rows.BookTitle}" /></td>
<td><c:out value="${rows.Author}" /></td>
</tr>
</c:forEach>
</table>
<td><a href="category.html">Search by book category</a></td>
</c:otherwise>
</c:choose>
</body>
</html>

Open in new window


Now the probleme lie when i want to search using the categoryDescription in Categories table and display only the Booktitle,Author of the Books table..
0
 
for_yanCommented:
Well, we don't seem to understand each other.

Look at your original question.
You stated that you have one query working
You asked how to create another query.

Your question was a little bit strange ffrom the beginning because
you stated that you want to search in two tables by category,
but you wanted back only BookTitle and Awthor - both fields
are coming from one of the tables, so - no reason to
search in two tables and to join them to have such output

Nevertheless, I suggested the query how to select
by category from this one table and
also in my second posting  showed how to join tables, assuming
that you also wanted to select the
categori description which is indeed in another table

Now I'd think you'd tell me how these new queries work.
Instead you are posting lots of codes which do not seem to have
connection to your original question, at least that connection is not obvious.
 Please, read attentively
the trail from the very beginning and try to formulate
what it is that you want.
Then I can try to think how to help you.

0
 
ozzyfantaAuthor Commented:
In the first code you posted, we could clearly see that you did not take account of the input...the user will input the CategoryDescription( like Health) and the output will go first to the second take to get the corresponding CategoryID and display the BookTitle and Author that correspond to that CategoryID in the first table(table 1 books)...I will post shortly the error i am getting from the second code you posted...Thanks
0
 
ozzyfantaAuthor Commented:
I think there is no need to do that cos i presume that you misunderstood my question...thanks
0
 
ozzyfantaAuthor Commented:
I meant in table Categories and the row is called CategoryDescription ...
0
 
for_yanCommented:

Indeed, sorry I misread one word in your question.
idn't understand that they provide Category Description

Then I think you should do it like that, with user providing substring of
Category Descriptin

<sql:query var="booksList" scope="request"  dataSource = "${dB}">
SELECT Books.BookTitle, Books.Awthor  FROM Books, Categories
     WHERE    
and Books.CategoryID = Categories.CategoriID
and Category.CategoryDescription like ?
<sql:param value="%${param.CategoryDescription}%" />


0
 
ozzyfantaAuthor Commented:
Thanks again ...The solution was excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.