Search problem
Posted: Fri Jan 17, 2003 10:44 am
Hello.
I having some problems with a search I am creating for a website. Basically, the user should be able to query a MySQL database for a library using any combination of 3 elements: keyword, category, and list. I am fairly new to programming and PHP, but I think the problem lies within my conditions somewhere. I have written the code for what I believe to be any combination of the 3 elements above, yet no matter what variables are entered, it only does the search according to the first condition (which as you will see is only the keyword search). I have entered echo statements for the variable before and after to verify the variables are being inserted and parsed through the code, and they are.
Here's the bulk of the code:
Any help you can give would be greatly appreciated.
Thanks,
--Joel
I having some problems with a search I am creating for a website. Basically, the user should be able to query a MySQL database for a library using any combination of 3 elements: keyword, category, and list. I am fairly new to programming and PHP, but I think the problem lies within my conditions somewhere. I have written the code for what I believe to be any combination of the 3 elements above, yet no matter what variables are entered, it only does the search according to the first condition (which as you will see is only the keyword search). I have entered echo statements for the variable before and after to verify the variables are being inserted and parsed through the code, and they are.
Here's the bulk of the code:
Code: Select all
$connection = mysql_connect("localhost", "root") or die("Couldn't connect to the library database.");
$db_select = mysql_select_db("library", $connection) or die("Couldn't select the library database.");
if ((isset($keyword)) && (empty($category)) && (empty($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Title LIKE '%$keyword%'
OR Author LIKE '%$keyword%'
OR BookList.dbase LIKE '%$keyword%'
OR Publisher.Publisher LIKE '%$keyword%'
OR BookList.dbase_user LIKE '%$keyword%'
OR Category.Sub_category LIKE '%$keyword%'
OR BoxSet.BoxSet LIKE '%$keyword%'
OR Category.Category LIKE '%$keyword%'";
} elseif ((isset($keyword)) && (isset($category)) && (empty($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Title LIKE '%$keyword%'
OR Author LIKE '%$keyword%'
OR BookList.dbase LIKE '%$keyword%'
OR Publisher.Publisher LIKE '%$keyword%'
OR BookList.dbase_user LIKE '%$keyword%'
OR Category.Sub_category LIKE '%$keyword%'
OR BoxSet.BoxSet LIKE '%$keyword%'
OR Category.Category LIKE '%$keyword%'
AND Category.Category = '%$category%'";
} elseif ((isset($keyword)) && (empty($category)) && (isset($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Title LIKE '%$keyword%'
OR Author LIKE '%$keyword%'
OR BookList.dbase LIKE '%$keyword%'
OR Publisher.Publisher LIKE '%$keyword%'
OR BookList.dbase_user LIKE '%$keyword%'
OR Category.Sub_category LIKE '%$keyword%'
OR BoxSet.BoxSet LIKE '%$keyword%'
OR Category.Category LIKE '%$keyword%'
AND BookList.dbase LIKE '%$listName%'";
} elseif ((empty($keyword)) && (isset($category)) && (empty($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Category.Category = '%$category%'";
} elseif ((empty($keyword)) && (isset($category)) && (isset($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Category.Category = '%$category%'
AND BookList.dbase LIKE '%$listName%'";
} elseif ((isset($keyword)) && (isset($category)) && (isset($listName))) {
$search = "SELECT * FROM Books LEFT JOIN Category ON Books.CategoryID = Category.CategoryID
LEFT JOIN BookList ON Books.BookListID = BookList.BookListID
LEFT JOIN Publisher ON Books.PublisherID = Publisher.PublisherID
LEFT JOIN BoxSet ON Books.BoxSetID = BoxSet.BoxSetID
LEFT JOIN AuthUsers ON Books.auID = AuthUsers.auID
LEFT JOIN CD ON Books.CD = CD.CD_ID
WHERE Title LIKE '%$keyword%'
OR Author LIKE '%$keyword%'
OR BookList.dbase LIKE '%$keyword%'
OR Publisher.Publisher LIKE '%$keyword%'
OR BookList.dbase_user LIKE '%$keyword%'
OR Category.Sub_category LIKE '%$keyword%'
OR BoxSet.BoxSet LIKE '%$keyword%'
OR Category.Category LIKE '%$keyword%'
AND Category.Category = '%$category%'
AND BookList.dbase LIKE '%$listName%'";
}
$result = mysql_query($search, $connection) or die(mysql_error());
$numResults = mysql_num_rows($result);
print("<table><tr><th>Number of books found: <font color=#62C332>$numResults</font>.</th></tr></table>");
print("<table><form method="post" action="checkBook.php">");
print("<tr><th>Select</th><th>Title</th><th>Author</th><th>ISBN</th><th>Publisher</th><th>CD Included</th><th>Box Set</th>
<th>Library</th><th>Owner</th><th>Category</th><th>Sub-Category</th><th>Checked Out By:</th><th>Date:</th><th>Time:</th></tr>");
print("<tr>");
for ($i=0; $i <$numResults; $i++) {
$row = mysql_fetch_array($result) or die(mysql_error());
print("<td align=center><input type="checkbox" name="choice"></td><td>{$rowї'Title']}</td><td>{$rowї'Author']}</td><td><a
href="http://btobsearch.barnesandnoble.com/booksearch/isbninquiry.asp?ISBN={$rowї'ISBN']}" target="fill_me">{$rowї'ISBN']}</a></td>
<td>{$rowї'Publisher']}</td><td>{$rowї'CD']}</td><td>{$rowї'BoxSet']}</td><td>{$rowї'dbase']}</td><td>{$rowї'dbase_user']}</td>
<td>{$rowї'Category']}</td><td>{$rowї'Sub_category']}</td><td>{$rowї'email']}</td><td>{$rowї'Date']}</td><td>{$rowї'Time']}</td></tr>");
}
print("</table><table>");
print("<tr height=10></tr><tr><td bgcolor="#334728" align=center><input class="inputButton2" type="submit" name="checkOut" value="Check Out"></td><td align=center><input
class="inputButton2" type="reset" name="clear" value="clear"></td></tr><tr height=10></tr>");
print("</form></table>");Thanks,
--Joel