Search problems

jcuz11

Hello. I'm having some problems getting an "advanced" search to work on a website I'm building. The search entails any combination of three parts - keyword, category and list. My code looks like this:

if (($keyword) && (!$category) && (!$listName)) {
$select = ....

} elseif (($keyword) && ($category) && (!$listName)) {
$select = ....

} elseif etc... {

and every combination of the three values finally ending with...

} elseif (($keyword) && ($category) && ($listName)) {
$select = ...
}

For some reason, no matter the combination of values entered by the user, the script only generates output according to the first condition - the keyword being the only value inserted into the search.

I'm not sure what the problem is. I've setup echo's for each of the variables before and after the conditions to ensure the values are being passed to the search, and they are. So it appears it is something with my conditions.

I'd appreciate any help you can give.

Thanks alot,

--Joel

matt_4013

Try,

if ((isset($keyword)) && (!isset($category)) && (!isset($listName))) { 
$select = .... 

} elseif ((isset($keyword)) && (isset($category)) && (!isset($listName))) { 
$select = .... 

} elseif etc... {

jcuz11

Thanks for the tip, Matt. I just read about the isset function in a book I have and wondered if that might do the trick.

I tried it, but I get the same result. Only the keyword value is used.

--Joel

pyfsapple

More...

if (($keyword!="")) && ($category=="") && ($listName== "")) { 
$select = .... 

} elseif etc...

If also not work, try use empty().

jcuz11

Thanks, p. I tried that, also with no luck. Perhaps I should post more of the code:

$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>");

Again, the search works, but no matter the combination, only the first condition is used.

Thanks for your help, Gang!

rupertbj

The problem is that your searching and have inserted the keyword value into the search box. When you ask if keyword has been set AND that the other variables have not been set, then that if statement returns true and therefore executes.

What you need to do is find another way of writing the if statements in an order in which the result will return true for the variables that are entered.................and i'm thinking of it...................more later!

reidme

[Disclaimer]Im a newbie, Im prolly way off track, but I am keen to learn and don't know any coders personally, so this is as close as I get to having someone to pose a problem and for me to come up with a possible solution. I appreciate any correction anyone has to offer, thank you.[/Disclaimer]

I would try and get the variables stored in arrays and then code the query once, breaking the where statements into for each loops (if I knew how to do them anyway 😃):
Heres what I came up with:

$vars = array();
if isset($keyword)) {$vars = $vars[0] =>$keyword ;}
if isset($category)) {$vars = $vars[1] =>$category ;}
if isset($listName)) {$vars = $vars[2] =>$listName ;}
$column = array("Title", "Author", "Booklist.dbase", "etc...");
$search = "SELECT * FROM Books LEFT JOIN Category...";
if (isset($vars[0])){
	foreach ($column as $col_var){
		if ($col_var == "Title"){
		$search .= " WHERE $col_var LIKE '%$keyword%'";
		} 	else {
			$search .= " OR $col_var LIKE '%$keyword%'";
			}
	}
		if (isset($vars[1])) {
		$search .= " AND Category.Category LIKE '%keyword%'";
		}
		if (isset($vars[2])) {
		$search .=" AND BookList.dbase LIKE '%$listName%'";
		}
}	elseif (isset($vars[1])){
	$search .= "WHERE Category.Category = '%$category%'";
		if (isset($vars[2])){
		$search .= "AND BookList.dbase LIKE '%$listName%'";
		}
	}

I looked at all the queries you need to construct and i think that covers them all.

jcuz11

Oh my gosh! Thanks, rupertbj! I don't believe I forgot that. I created a quicksearch off of just keyword first. I then copied that code to a new file and was going to add the category and listName variables after. I worked on the different combinations and was thinking I was done....:o Doh!

Thanks again for your help. Thank you also, reidme!