Page 1 of 1

Search problem

Posted: Fri Jan 17, 2003 10:44 am
by jcuz11
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:

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++) &#123;
        $row = mysql_fetch_array($result) or die(mysql_error());

        print("<td align=center><input type="checkbox" name="choice"></td><td>&#123;$row&#1111;'Title']&#125;</td><td>&#123;$row&#1111;'Author']&#125;</td><td><a
                href="http://btobsearch.barnesandnoble.com/booksearch/isbninquiry.asp?ISBN=&#123;$row&#1111;'ISBN']&#125;" target="fill_me">&#123;$row&#1111;'ISBN']&#125;</a></td>
                <td>&#123;$row&#1111;'Publisher']&#125;</td><td>&#123;$row&#1111;'CD']&#125;</td><td>&#123;$row&#1111;'BoxSet']&#125;</td><td>&#123;$row&#1111;'dbase']&#125;</td><td>&#123;$row&#1111;'dbase_user']&#125;</td>
                <td>&#123;$row&#1111;'Category']&#125;</td><td>&#123;$row&#1111;'Sub_category']&#125;</td><td>&#123;$row&#1111;'email']&#125;</td><td>&#123;$row&#1111;'Date']&#125;</td><td>&#123;$row&#1111;'Time']&#125;</td></tr>");

&#125;


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>");
Any help you can give would be greatly appreciated.

Thanks,

--Joel

Posted: Fri Jan 17, 2003 1:23 pm
by jcuz11
I see what I did... :oops:

I completely forgot to add the $category and $listName variables to the search. I was thinking I was done after I did the different combinations...Jeesh! :)