ozzyfanta
asked on
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
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
<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>
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
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.CategoryDescrip tion 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
<sql:query var="booksList" scope="request" dataSource = "${dB}">
SELECT Books.BookTitle, Categories.CategoryDescrip
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
ASKER
it is giving me the following error:
org.apache.jasper.JasperEx ception: javax.servlet.ServletExcep tion: javax.servlet.jsp.JspExcep tion:
SELECT Books.BookTitle, Categories.CategoryDescrip tion FROM Books, Categories
WHERE BookTitle LIKE ?
AND Books.CategoryID = Categories.CategoriID
: Unexpected exception : 1
org.apache.jasper.servlet. JspServlet Wrapper.ha ndleJspExc eption(Jsp ServletWra pper.java: 536)
org.apache.jasper.servlet. JspServlet Wrapper.se rvice(JspS ervletWrap per.java:4 10)
org.apache.jasper.servlet. JspServlet .serviceJs pFile(JspS ervlet.jav a:313)
org.apache.jasper.servlet. JspServlet .service(J spServlet. java:260)
javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 717)
org.apache.jasper.JasperEx
SELECT Books.BookTitle, Categories.CategoryDescrip
WHERE BookTitle LIKE ?
AND Books.CategoryID = Categories.CategoriID
: Unexpected exception : 1
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
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
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
ASKER
This is the form i have created to get the input from the user ( the input should be a categoryDescription like health,technolgy,etc...)
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
<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>
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>
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)
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)
ASKER
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
The code to query the database called find.jsp
and the last one, the display the result of the query, called list.jsp
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..
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>
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" />
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>
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..
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.
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.
ASKER
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
ASKER
I think there is no need to do that cos i presume that you misunderstood my question...thanks
ASKER
I meant in table Categories and the row is called CategoryDescription ...
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.CategoryDescripti
<sql:param value="%${param.CategoryDe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again ...The solution was excellent
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