Creating An Advanced Search

jonny7d · November 4, 2012

Post your updated code.

If you're still using "WHERE 1 AND" You'll always get every row.

Hmm, here is my updated code, what would you recommend?

EDIT*: If I take the 1 out of WHERE 1, I get an error

EDIT**: If I take the WHERE 1 out all together, it still produces the same result to when it was there in the first place

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search CDs</title>
<link rel="stylesheet" href="stylesheets/style.css" type="text/css" media="all" /><!--Main stylesheet-->
<link rel="icon" href="favicon.ico" type="image/x-icon" /><!--Thumb image for broswer tab-->
</head>
<body>
<div id="container">
<div id="menu">
<div id="logo">
<a href="index.html"><img src="images/Logo.gif" alt="" /></a>
</div><!--end logo div-->
<div id="navigation">
<ul>
 <li ><a href="index.html">Home</a></li>
 <li><a href="cd.php">CDs</a></li>
 <li><a href="search.php" class="selected">Search</a></li>
 <li><a href="admin.php">Administrator</a></li>
 <li><a href="credits.html">Credits</a></li>
</ul>
</div><!--end navigation div-->
</div><!--end menu div-->
<div id="main-body">
<div id="article">
<div id="cd-article-left">
<div id="top-search-bar">
<form id="advsearch" action="search.php" method="get">
<div><h2>Advanced Search</h2></div>
<div id="CDTitle">Search CD Title: <input type="text" name="search"></input></div>
<div><br /><br /><hr /></div>

<div><h5>Search by ID</h5></div>
<div id="CDID">CD ID: <input type="text" name="searchCDID" maxlength="4" size="3"></input></div>
<div id="pubID">Publisher ID: <input type="text" name="searchPubID" maxlength="3" size="3"></input></div>
<div id="catID">Category ID: <input type="text" name="searchCatID" maxlength="2" size="3"></input><br /><br /></div>
<div><br /><br /><hr /></div>

<div id="price">Search by Price:
<input type="radio" name="price" value="7-8"/>£7-8
<input type="radio" name="price" value="8-9"/>£8-9
<input type="radio" name="price" value="9-10"/>£9-10
<input type="radio" name="price" value="10-11"/>£10-11
<input type="radio" name="price" value="11-12"/>£11-12
</div><!--end price div-->
<div><br /><br /><hr /></div>
<?php
include ('connect.php');

$sqlCat = "SELECT * FROM `nmc_category` ORDER BY catDesc Asc";
$rsCat = mysql_query($sqlCat);

 echo "<div id=\"category\">
 Search by Category: <select name=\"category\">";

 while ($row = mysql_fetch_assoc($rsCat)) {
 $catID = $row['catID'];
 $catDesc = $row['catDesc'];
 echo "<option value=\"$catID\">$catDesc</option>";
 }
 echo "</select></div>";
 ?>

 <?php
 $sqlYear = "SELECT DISTINCT CDYear FROM `nmc_cd` ORDER BY CDYear Asc";
 $rsYear = mysql_query($sqlYear);

 echo "<div id=\"CDYear\">
 Search by Year: <select name=\"year\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsYear)) {
 $CDYear = $row['CDYear'];
 echo "<option value=\"$CDYear\">$CDYear</option>";
 }
 echo "</select></div><br /><br /><hr />";
 ?>

 <?php
 $sqlPubName = "SELECT * FROM `nmc_publisher` ORDER BY pubName Asc";
 $rsPubName = mysql_query($sqlPubName);

 echo "<div id=\"PubName\">
 Search by Publisher: <select name=\"pName\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsPubName)) {
 $pubID = $row['pubID'];
 $location = $row['location'];
 $pubName = $row['pubName'];
 echo "<option value=\"$pubID\">$pubName</option>";
 }
 echo "</select></div>";
 ?>

 <?php
 $sqlPubLocation = "SELECT DISTINCT location FROM `nmc_publisher` ORDER BY pubName Asc";
 $rsPubLocation = mysql_query($sqlPubLocation);

 echo "<div id=\"PubLocation\">
 Search by Location: <select name=\"pLocation\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsPubLocation)) {
 $location = $row['location'];
 echo "<option value=\"$location\">$location</option>";
 }
 echo "</select></div>";
 ?>
 <br /><br />
 <input type="submit" name="submit"></input>
 <input type="reset" name="reset"></input>
</form>
</div><!--end top-search-bar div-->
<hr />

<?php
// get each attribute value from the request stream
$search = (isset($_GET["search"]) ? $_GET['search'] : null);
$searchCDID = (isset($_GET["searchCDID"]) ? $_GET['searchCDID'] : null);
$searchPubID = (isset($_GET["searchPubID"]) ? $_GET['searchPubID'] : null);
$searchCatID = (isset($_GET["searchCatID"]) ? $_GET['searchCatID'] : null);
$price = (isset($_GET["price"]) ? $_GET['price'] : null);
$catDesc = (isset($_GET["$catDesc"]) ? $_GET['$catDesc'] : null);
$CDYear = (isset($_GET["$CDYear"]) ? $_GET['$CDYear'] : null);
$pubName = (isset($_GET["$pubName"]) ? $_GET['$pubName'] : null);
$location = (isset($_GET["$location"]) ? $_GET['$location'] : null);

//sql to show everything
$sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
 FROM nmc_cd
 INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
 INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
 WHERE 1";
 //ORDER BY nmc_cd.CDTitle";

// make an empty string that will become the AND parts of the query
// if any values were entered in the search form
$sqlCondition = "";


if($search==1){
$sqlCondition = $sqlCondition." AND CDTitle LIKE '%$search%'";
}

if($searchCDID==1){
$sqlCondition = $sqlCondition." AND CDID LIKE '%$searchCDID%'";
}

if($searchPubID==1){
$sqlCondition = $sqlCondition." AND pubID LIKE '%$searchPubID%'";
}

if($searchCatID==1){
$sqlCondition = $sqlCondition." AND catID LIKE '%$searchCatID%'";
}

if($price==1){
$sqlCondition = $sqlCondition." AND CDPrice LIKE '%$price%'";
}

if($catDesc==1){
$sqlCondition = $sqlCondition." AND catDesc LIKE '%$catDesc%'";
}

if($CDYear==1){
$sqlCondition = $sqlCondition." AND CDYear LIKE '%$CDYear%'";
}

if($pubName==1){
$sqlCondition = $sqlCondition." AND pubName LIKE '%$pubName%'";
}

if($location==1){
$sqlCondition = $sqlCondition." AND location LIKE '%$location%'";
}



$sqlSearch = $sql.$sqlCondition;

$rsSearch = mysql_query($sqlSearch) or trigger_error("Query Failed! SQL: $sqlSearch - Error: ".mysql_error());
echo "<div>";
while($row = mysql_fetch_array($rsSearch)){
$CDID = $row['CDID'];
$CDTitle = $row['CDTitle'];
$CDYear = $row['CDYear'];
$CDPrice = $row['CDPrice'];
$pubID = $row['pubID'];
$catID = $row['catID'];
$pubName = $row['pubName'];
$location = $row['location'];
$catDesc = $row['catDesc'];
echo "
 <div class=\"title\"><h4><a href='cd-description.php?cd=$CDID'>$CDTitle</a></h4>
 <table class=\"searchtable\">
 <tr>
 <td>Year of Release: <strong>$CDYear</strong><br /></td>
 <td>Price: <strong>£$CDPrice</strong><br /></td>
 </tr>
 <tr>
 <td>Location: <strong>$location</strong><br /></td>
 <td>Category: <strong>$catDesc</strong><br /></td>
 </tr>
 </table><hr />
 </div>";
}
echo "</div>";
?>
</div><!--end cd-article-left div-->
</div><!--end article div-->


<div id="footer">
<div id="left-footer">
 <img src="images/LogoFade.gif" alt="" />
</div><!--end left-footer div-->
<div id="validator">
 <a href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href,'_blank');return false;"><img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0 Strict" height="31" width="88" /></a>
 <a href="http://jigsaw.w3.org/css-validator/check/referer" onclick="window.open(this.href,'_blank');return false;"><img style="border:0;width:88px;height:31px" src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" /></a>
</div><!--end validator div-->
</div><!--end footer div-->
</div><!--end main-body div-->
</div><!--end container div-->
</body>
</html>

Edited November 4, 2012 by jonny7d

Jessica · November 4, 2012

Saying WHERE 1 will always return every row. You need to only add the WHERE part if you have a WHERE clause. Think about it.

jonny7d · November 4, 2012

Saying WHERE 1 will always return every row. You need to only add the WHERE part if you have a WHERE clause. Think about it.

I edited the code above and I took out the WHERE clause completely, and it stills gives me the whole database

Jessica · November 4, 2012

You need a where clause for your search. You DON'T need the "1 AND" part.

jonny7d · November 4, 2012

You need a where clause for your search. You DON'T need the "1 AND" part.

I see what you mean now, I have changed this code, and I have the following errors even when I try to search:

               $sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
  FROM nmc_cd
  INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
  INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
  WHERE";
  //ORDER BY nmc_cd.CDTitle";

               // make an empty string that will become the AND parts of the query
               // if any values were entered in the search form
   $sqlCondition = "";


               if($search==1){
                $sqlCondition = $sqlCondition." CDTitle LIKE '%$search%'";
               }

               if($searchCDID==1){
                $sqlCondition = $sqlCondition." CDID LIKE '%$searchCDID%'";
               }

               if($searchPubID==1){
                $sqlCondition = $sqlCondition." pubID LIKE '%$searchPubID%'";
               }

               if($searchCatID==1){
                $sqlCondition = $sqlCondition." catID LIKE '%$searchCatID%'";
               }

               if($price==1){
                $sqlCondition = $sqlCondition." CDPrice LIKE '%$price%'";
               }

               if($catDesc==1){
                $sqlCondition = $sqlCondition." catDesc LIKE '%$catDesc%'";
               }

               if($CDYear==1){
                $sqlCondition = $sqlCondition." CDYear LIKE '%$CDYear%'";
               }

               if($pubName==1){
                $sqlCondition = $sqlCondition." pubName LIKE '%$pubName%'";
               }

               if($location==1){
                $sqlCondition = $sqlCondition." location LIKE '%$location%'";
               }

Notice: Query Failed! SQL: SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc FROM nmc_cd INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID WHERE - Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5 in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 181

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 183

Line 181 and 183 is this:

               $rsSearch = mysql_query($sqlSearch) or trigger_error("Query Failed! SQL: $sqlSearch - Error: ".mysql_error());
               echo "<div>";
               while($row = mysql_fetch_array($rsSearch)){

I am now unsure where I have gone wrong

Jessica · November 4, 2012

You have a WHERE without anything after it. Basic syntax.

Look. You are either going to do:

SELECT fields FROM tbl

SELECT fields FROM tbl WHERE field1 = 'value1'

SELECT fields FROM tbl WHERE field1 = 'value1' AND field2 = 'value2' (etc, etc)

Think about how you can build your SQL statement to handle ALL of these cases.

Edited November 4, 2012 by Jessica

jonny7d · November 4, 2012

You have a WHERE without anything after it. Basic syntax.

Look. You are either going to do:

SELECT fields FROM tbl

SELECT fields FROM tbl WHERE field1 = 'value1'

SELECT fields FROM tbl WHERE field1 = 'value1' AND field2 = 'value2' (etc, etc)

Think about how you can build your SQL statement to handle ALL of these cases.

Ok, I see where you are coming from.

I have done what you say, but for some strange reason it still does now work.

               $sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
  FROM nmc_cd
  INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
  INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
  ";
  //ORDER BY nmc_cd.CDTitle";

               // make an empty string that will become the AND parts of the query
               // if any values were entered in the search form
   $sqlCondition = "";


               if($search==1){
                $sqlCondition = $sqlCondition." WHERE CDTitle LIKE '%$search%'";
               }

               if($searchCDID==1){
                $sqlCondition = $sqlCondition." WHERE CDID LIKE '%$searchCDID%'";
               }

               if($searchPubID==1){
                $sqlCondition = $sqlCondition." WHERE pubID LIKE '%$searchPubID%'";
               }

               if($searchCatID==1){
                $sqlCondition = $sqlCondition." WHERE catID LIKE '%$searchCatID%'";
               }

               if($price==1){
                $sqlCondition = $sqlCondition." WHERE CDPrice LIKE '%$price%'";
               }

               if($catDesc==1){
                $sqlCondition = $sqlCondition." WHERE catDesc LIKE '%$catDesc%'";
               }

               if($CDYear==1){
                $sqlCondition = $sqlCondition." WHERE CDYear LIKE '%$CDYear%'";
               }

               if($pubName==1){
                $sqlCondition = $sqlCondition." WHERE pubName LIKE '%$pubName%'";
               }

               if($location==1){
                $sqlCondition = $sqlCondition." WHERE location LIKE '%$location%'";
               }

Barand · November 4, 2012

So you only ever want to search for values that have a "1" in them :confused:

jonny7d · November 4, 2012

So you only ever want to search for values that have a "1" in them

Hold on Sen, you might be onto something.. I typed in 1 in CD Title and it displayed the CDs with a 1 in them.

I thought that the following code meant if true do this. It appears I am wrong, how can I fix this?

               if($search==1){
                $sqlCondition = $sqlCondition." WHERE CDTitle LIKE '%$search%'";
               }

Barand · November 4, 2012

if ($search) {
   ...
}

jonny7d · November 4, 2012

@Jessica I have realised that I do need WHERE 1 in my sql statement, this is because I want to search with multiple criteria.

For instance, go to my site and type 'blue' in the CD Title, and '694' in the CDID, this will bring up that CD with the two fields together.

I have tweaked the code a bit and now it is working nearly, I got rid of the ==1, as I forgot (stupidly) that I have already tested if the variable was true.

My site:

http://numyspace.co....4928/search.php

EDIT: How do I find out specific areas of a form, to see where they are filled in or not? Is this is:

               $search = (isset($_GET["search"]) ? $_GET['search'] : null);

Here is the updated code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search CDs</title>
<link rel="stylesheet" href="stylesheets/style.css" type="text/css" media="all" /><!--Main stylesheet-->
<link rel="icon" href="favicon.ico" type="image/x-icon" /><!--Thumb image for broswer tab-->
</head>
<body>
<div id="container">
<div id="menu">
<div id="logo">
<a href="index.html"><img src="images/Logo.gif" alt="" /></a>
</div><!--end logo div-->
<div id="navigation">
<ul>
 <li ><a href="index.html">Home</a></li>
 <li><a href="cd.php">CDs</a></li>
 <li><a href="search.php" class="selected">Search</a></li>
 <li><a href="admin.php">Administrator</a></li>
 <li><a href="credits.html">Credits</a></li>
</ul>
</div><!--end navigation div-->
</div><!--end menu div-->
<div id="main-body">
<div id="article">
<div id="cd-article-left">
<div id="top-search-bar">
<form id="advsearch" action="search.php" method="get">
<div><h2>Advanced Search</h2></div>
<div id="CDTitle">Search CD Title: <input type="text" name="search"></input></div>
<div><br /><br /><hr /></div>

<div><h5>Search by ID</h5></div>
<div id="CDID">CD ID: <input type="text" name="searchCDID" maxlength="4" size="3"></input></div>
<div id="pubID">Publisher ID: <input type="text" name="searchPubID" maxlength="3" size="3"></input></div>
<div id="catID">Category ID: <input type="text" name="searchCatID" maxlength="2" size="3"></input><br /><br /></div>
<div><br /><br /><hr /></div>

<div id="price">Search by Price:
<input type="radio" name="price" value="7-8"/>£7-8
<input type="radio" name="price" value="8-9"/>£8-9
<input type="radio" name="price" value="9-10"/>£9-10
<input type="radio" name="price" value="10-11"/>£10-11
<input type="radio" name="price" value="11-12"/>£11-12
</div><!--end price div-->
<div><br /><br /><hr /></div>
<?php
include ('connect.php');

$sqlCat = "SELECT * FROM `nmc_category` ORDER BY catDesc Asc";
$rsCat = mysql_query($sqlCat);

 echo "<div id=\"category\">
 Search by Category: <select name=\"category\">";

 while ($row = mysql_fetch_assoc($rsCat)) {
 $catID = $row['catID'];
 $catDesc = $row['catDesc'];
 echo "<option value=\"$catID\">$catDesc</option>";
 }
 echo "</select></div>";
 ?>

 <?php
 $sqlYear = "SELECT DISTINCT CDYear FROM `nmc_cd` ORDER BY CDYear Asc";
 $rsYear = mysql_query($sqlYear);

 echo "<div id=\"CDYear\">
 Search by Year: <select name=\"year\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsYear)) {
 $CDYear = $row['CDYear'];
 echo "<option value=\"$CDYear\">$CDYear</option>";
 }
 echo "</select></div><br /><br /><hr />";
 ?>

 <?php
 $sqlPubName = "SELECT * FROM `nmc_publisher` ORDER BY pubName Asc";
 $rsPubName = mysql_query($sqlPubName);

 echo "<div id=\"PubName\">
 Search by Publisher: <select name=\"pName\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsPubName)) {
 $pubID = $row['pubID'];
 $location = $row['location'];
 $pubName = $row['pubName'];
 echo "<option value=\"$pubID\">$pubName</option>";
 }
 echo "</select></div>";
 ?>

 <?php
 $sqlPubLocation = "SELECT DISTINCT location FROM `nmc_publisher` ORDER BY pubName Asc";
 $rsPubLocation = mysql_query($sqlPubLocation);

 echo "<div id=\"PubLocation\">
 Search by Location: <select name=\"pLocation\">
 <option value=\"#\"></option>";

 while ($row = mysql_fetch_assoc($rsPubLocation)) {
 $location = $row['location'];
 echo "<option value=\"$location\">$location</option>";
 }
 echo "</select></div>";
 ?>
 <br /><br />
 <input type="submit" name="submit"></input>
 <input type="reset" name="reset"></input>
</form>
</div><!--end top-search-bar div-->
<hr />

<?php
// get each attribute value from the request stream
$search = (isset($_GET["search"]) ? $_GET['search'] : null);
$searchCDID = (isset($_GET["searchCDID"]) ? $_GET['searchCDID'] : null);
$searchPubID = (isset($_GET["searchPubID"]) ? $_GET['searchPubID'] : null);
$searchCatID = (isset($_GET["searchCatID"]) ? $_GET['searchCatID'] : null);
$price = (isset($_GET["price"]) ? $_GET['price'] : null);
$catDesc = (isset($_GET["$catDesc"]) ? $_GET['$catDesc'] : null);
$CDYear1 = (isset($_GET["$CDYear"]) ? $_GET['$CDYear'] : null);
$pubName = (isset($_GET["$pubName"]) ? $_GET['$pubName'] : null);
$location = (isset($_GET["$location"]) ? $_GET['$location'] : null);

//sql to show everything
$sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
 FROM nmc_cd
 INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
 INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
 WHERE 1";
 //ORDER BY nmc_cd.CDTitle";

// make an empty string that will become the AND parts of the query
// if any values were entered in the search form
$sqlCondition = "";


if($search){
$sqlCondition = $sqlCondition." AND CDTitle LIKE '%$search%'";
}

if($searchCDID){
$sqlCondition = $sqlCondition." AND CDID LIKE '%$searchCDID%'";
}

if($searchPubID){
$sqlCondition = $sqlCondition." AND pubID LIKE '%$searchPubID%'";
}

if($searchCatID){
$sqlCondition = $sqlCondition." AND catID LIKE '%$searchCatID%'";
}

if($price){
$sqlCondition = $sqlCondition." AND CDPrice LIKE '%$price%'";
}

if($catDesc){
$sqlCondition = $sqlCondition." AND catDesc LIKE '%$catDesc%'";
}

if($CDYear){
$sqlCondition = $sqlCondition." AND CDYear LIKE '%$CDYear%'";
}

if($pubName){
$sqlCondition = $sqlCondition." AND pubName LIKE '%$pubName%'";
}

if($location){
$sqlCondition = $sqlCondition." AND location LIKE '%$location%'";
}



$sqlSearch = $sql.$sqlCondition;

$rsSearch = mysql_query($sqlSearch) or trigger_error("Query Failed! SQL: $sqlSearch - Error: ".mysql_error());
echo "<div>";
while($row = mysql_fetch_array($rsSearch)){
$CDID = $row['CDID'];
$CDTitle = $row['CDTitle'];
$CDYear = $row['CDYear'];
$CDPrice = $row['CDPrice'];
$pubID = $row['pubID'];
$catID = $row['catID'];
$pubName = $row['pubName'];
$location = $row['location'];
$catDesc = $row['catDesc'];
echo "
 <div class=\"title\"><h4><a href='cd-description.php?cd=$CDID'>$CDTitle</a></h4>
 <table class=\"searchtable\">
 <tr>
 <td>Year of Release: <strong>$CDYear</strong><br /></td>
 <td>Price: <strong>£$CDPrice</strong><br /></td>
 </tr>
 <tr>
 <td>Location: <strong>$location</strong><br /></td>
 <td>Category: <strong>$catDesc</strong><br /></td>
 </tr>
 </table><hr />
 </div>";
}
echo "</div>";
?>
</div><!--end cd-article-left div-->
</div><!--end article div-->


<div id="footer">
<div id="left-footer">
 <img src="images/LogoFade.gif" alt="" />
</div><!--end left-footer div-->
<div id="validator">
 <a href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href,'_blank');return false;"><img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0 Strict" height="31" width="88" /></a>
 <a href="http://jigsaw.w3.org/css-validator/check/referer" onclick="window.open(this.href,'_blank');return false;"><img style="border:0;width:88px;height:31px" src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" /></a>
</div><!--end validator div-->
</div><!--end footer div-->
</div><!--end main-body div-->
</div><!--end container div-->
</body>
</html>

You will see I now have some problems with the dropdown boxes, for some reason they are not registering like the text area searches are.

Edited November 4, 2012 by jonny7d

Zane · November 4, 2012

In the code your posted earlier, you have your variables defaulting to null if they are not set, so it is null you should check for when you a creating your conditions..

It also wouldn't hurt to just put all of your condition strings into an array and then implode them with AND... so you don't end up with a trailing AND. Actually, I think it would be better if you used OR, then again I am just writing this to be writing it....

I thought that the following code meant if true do this. It appears I am wrong, how can I fix this?
               if($search==1){
                $sqlCondition = $sqlCondition." WHERE CDTitle LIKE '%$search%'";
               }

You would use the triple equals sign to check for true like that

$search === 1

But like I said earlier, you defaulted all of your variables to null if they are not set so none of them will ever be "true". You need to use if(!is_null($variable))

Here is how you would do it with an array

// Create variable for your WHERE conditions
$where = array();
if(!is_null($search)) $where[] = "CDTitle LIKE '%$search%'";
if(!is_null($searchCDID)) $where[] = "CDID LIKE '%$searchCDID%'";
if(!is_null($searchPubID)) $where[] = "pubID LIKE '%$searchPubID%'";
// etcetera etcetera

$sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
          FROM nmc_cd
          INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
          INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID";
$sql .= " WHERE " . implode(" OR ", $where);
mysql_query($sql) or die(mysql_error() . "\r\n" . $sql);

It will also be important to check the size of the $where array before you even run the query, otherwise, you will get ALL the records.... or actually you will get an SQL error for the empty WHERE clause.

Edited November 4, 2012 by Zane

Barand · November 4, 2012

I have not done all conditions but this should give you the idea of searching with multiple criteria.

   $sqlCondition = '';

   $where = array();

   if($search=){
 $where[] = "(CDTitle LIKE '%$search%')";
   }

   if($searchCDID){
 $where[] = "(CDID = $searchCDID)";
   }

   if($searchPubID){
 $where[] =  "(pubID = $searchPubID)";
   }

   if($searchCatID){
 $where[] = "(catID = $searchCatID)";
   }


   /**
   * construct the where clause
   */
   if (count($where)) {
    $sqlCondition = "WHERE " .join(' AND ', $where);
   }

PS Note: you need to sanitize the data but I didn't want to clutter the example.

Zane · November 5, 2012

Hmm.. well I guess you dont need to use is_null...it seems that when you put just the variable in the condition is checks for a non-null value or a true value. Though I do still think you should implode with OR rather than AND, but you can figure that out on your own...

jonny7d · November 5, 2012

Thanks Zane and Barand for the the feedback.

Barand: I have implemented your code and it gives the same results as mine previously did.

               $sqlCondition = "";


               $where = array();

               if($search){
                $where[] = "(CDTitle LIKE '%$search%')";
               } else {
                null;
               }

               if($searchCDID){
                $where[] = "(CDID LIKE '%$searchCDID%')";
               } else {
                null;
               }

               if($searchPubID){
                $where[] = "(pubID LIKE '%$searchPubID%')";
               } else {
                null;
               }

               if($searchCatID){
                $where[] = "(catID LIKE '%$searchCatID%')";
               } else {
                null;
               }

               if($price){
                $where[] = "(CDPrice LIKE '%$price%')";
               } else {
                null;
               }

               if($catDesc){
                $where[] = "(catDesc LIKE '%$catDesc%')";
               } else {
                null;
               }

               if($CDYear){
                $where[] = "(CDYear LIKE '%$CDYear%')";
               } else {
                null;
               }

               if($pubName){
                $where[] = "(pubName LIKE '%$pubName%')";
               } else {
                null;
               }

               if($location){
                $where[] = "(location LIKE '%$location%')";
               } else {
                null;
               }


               if(count($where)){
                $sqlCondition = "WHERE " .join(' AND ', $where);
               }

               $sqlSearch = $sql.$sqlCondition;

Zane: For some reasons yours does not work at all:

               // get each attribute value from the request stream
               $where = array();

               if(!is_null($search)) $where[] = "CDTitle LIKE '%$search%'";
               if(!is_null($searchCDID)) $where[] = "CDID LIKE '%$searchCDID%'";
   if(!is_null($searchPubID)) $where[] = "pubID LIKE '%$searchPubID%'";
   if(!is_null($searchCatID)) $where[] = "catID LIKE '%$searchCatID%'";
   if(!is_null($price)) $where[] = "price LIKE '%$price%'";
   if(!is_null($catDesc)) $where[] = "catDesc LIKE '%$catDesc%'";
   if(!is_null($CDYear)) $where[] = "CDYear LIKE '%$CDYear%'";
   if(!is_null($pubName)) $where[] = "pubName LIKE '%$pubName%'";
   if(!is_null($location)) $where[] = "location LIKE '%$location%'";

               //sql to show everything
               $sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
  FROM nmc_cd
  INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
  INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
  ";
               //ORDER BY nmc_cd.CDTitle";

               // make an empty string that will become the AND parts of the query
               // if any values were entered in the search form
               $sqlCondition .= " WHERE " . implode(" OR ", $where);


               $sqlSearch = $sql.$sqlCondition;

Barand · November 5, 2012

In my experience most multiple searches tend to narrow down the search eg new house search

location = '$city' AND bedrooms = $beds AND price < $maxprice

Jessica · November 5, 2012

What is the query that gets generated? What values are in your database?

jonny7d · November 5, 2012

What is the query that gets generated? What values are in your database?

Say I search for blue in the CD Title search box again, this is what is generated in the URL:

http://numyspace.co....#&submit=Submit

EDIT: LINK ABOVE =

numyspace.co.uk/~unn_w11014928/search.php?search=blue&searchCDID=&searchPubID=&searchCatID=&category=1&year=%23&pName=%23&pLocation=%23&submit=Submit

I think this is where I am going wrong, as I am just searching for black, not everything else as well.

This gets generated when I search for nothing:

SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc FROM nmc_cd INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID WHERE 1

And this gets generated when I search for, let's say blue and a price range of £11-12.

SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc FROM nmc_cd INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID WHERE 1 AND CDTitle LIKE '%blue%' AND CDPrice LIKE '%11.%'

Edited November 5, 2012 by jonny7d

Barand · November 5, 2012

else {
  null;
}

Totally meaningless.

jonny7d · November 5, 2012

else {
 null;
}
Totally meaningless.

Roger.

Code so far everyone:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Search CDs</title>
<link rel="stylesheet" href="stylesheets/style.css" type="text/css" media="all" /><!--Main stylesheet-->
<link rel="icon" href="favicon.ico" type="image/x-icon" /><!--Thumb image for broswer tab-->
</head>
<body>
<div id="container">
 <div id="menu">
  <div id="logo">
   <a href="index.html"><img src="images/Logo.gif" alt="" /></a>
  </div><!--end logo div-->
  <div id="navigation">
   <ul>
 <li ><a href="index.html">Home</a></li>
 <li><a href="cd.php">CDs</a></li>
 <li><a href="search.php" class="selected">Search</a></li>
 <li><a href="admin.php">Administrator</a></li>
 <li><a href="credits.html">Credits</a></li>
   </ul>
  </div><!--end navigation div-->
 </div><!--end menu div-->
 <div id="main-body">
  <div id="article">
  <div id="cd-article-left">
   <div id="top-search-bar">
                   <form id="advsearch" action="search.php" method="get">
                    <div><h2>Advanced Search</h2></div>
                       <div id="CDTitle">Search CD Title: <input type="text" name="search"></input></div>
                       <div><br /><br /><hr /></div>

                       <div><h5>Search by ID</h5></div>
                       <div id="CDID">CD ID: <input type="text" name="searchCDID" maxlength="4" size="3"></input></div>
                       <div id="pubID">Publisher ID: <input type="text" name="searchPubID" maxlength="3" size="3"></input></div>
                       <div id="catID">Category ID: <input type="text" name="searchCatID" maxlength="2" size="3"></input><br /><br /></div>
                       <div><br /><br /><hr /></div>

                       <div id="price">Search by Price:
                        <input type="radio" name="price" value="7."/>£7-8
                        <input type="radio" name="price" value="8."/>£8-9
                        <input type="radio" name="price" value="9."/>£9-10
                        <input type="radio" name="price" value="10."/>£10-11
                        <input type="radio" name="price" value="11."/>£11-12
                       </div><!--end price div-->
                       <div><br /><br /><hr /></div>
                       <?php
                       include ('connect.php');

                       $sqlCat = "SELECT * FROM `nmc_category` ORDER BY catDesc Asc";
                       $rsCat = mysql_query($sqlCat);

  echo "<div id=\"category\">
    Search by Category: <select name=\"category\">";

  while ($row = mysql_fetch_assoc($rsCat)) {
   $catID = $row['catID'];
   $catDesc = $row['catDesc'];
   echo "<option value=\"$catID\">$catDesc</option>";
  }
  echo "</select></div>";
  ?>

  <?php
  $sqlYear = "SELECT DISTINCT CDYear FROM `nmc_cd` ORDER BY CDYear Asc";
  $rsYear = mysql_query($sqlYear);

  echo "<div id=\"CDYear\">
    Search by Year: <select name=\"year\">
    <option value=\"#\"></option>";

  while ($row = mysql_fetch_assoc($rsYear)) {
   $CDYear = $row['CDYear'];
   echo "<option value=\"$CDYear\">$CDYear</option>";
  }
  echo "</select></div><br /><br /><hr />";
  ?>

  <?php
  $sqlPubName = "SELECT * FROM `nmc_publisher` ORDER BY pubName Asc";
  $rsPubName = mysql_query($sqlPubName);

  echo "<div id=\"PubName\">
    Search by Publisher: <select name=\"pName\">
    <option value=\"#\"></option>";

  while ($row = mysql_fetch_assoc($rsPubName)) {
   $pubID = $row['pubID'];
   $location = $row['location'];
   $pubName = $row['pubName'];
   echo "<option value=\"$pubID\">$pubName</option>";
  }
  echo "</select></div>";
  ?>

  <?php
  $sqlPubLocation = "SELECT DISTINCT location FROM `nmc_publisher` ORDER BY pubName Asc";
  $rsPubLocation = mysql_query($sqlPubLocation);

  echo "<div id=\"PubLocation\">
    Search by Location: <select name=\"pLocation\">
    <option value=\"#\"></option>";

  while ($row = mysql_fetch_assoc($rsPubLocation)) {
   $location = $row['location'];
   echo "<option value=\"$location\">$location</option>";
  }
  echo "</select></div>";
  ?>
  <br /><br />
  <input type="submit" name="submit"></input>
  <input type="reset" name="reset"></input>
                   </form>
               </div><!--end top-search-bar div-->
               <hr />

               <?php
               // get each attribute value from the request stream
               $search = (isset($_GET["search"]) ? $_GET['search'] : null);
   $searchCDID = (isset($_GET["searchCDID"]) ? $_GET['searchCDID'] : null);
   $searchPubID = (isset($_GET["searchPubID"]) ? $_GET['searchPubID'] : null);
               $searchCatID = (isset($_GET["searchCatID"]) ? $_GET['searchCatID'] : null);
               $price = (isset($_GET["price"]) ? $_GET['price'] : null);
               $catDesc = (isset($_GET["$catDesc"]) ? $_GET['$catDesc'] : null);
               $CDYear = (isset($_GET["$CDYear"]) ? $_GET['$CDYear'] : null);
               $pubName = (isset($_GET["$pubName"]) ? $_GET['$pubName'] : null);
               $location = (isset($_GET["$location"]) ? $_GET['$location'] : null);

               //sql to show everything
               $sql = "SELECT nmc_cd.CDID, nmc_cd.CDTitle, nmc_cd.CDYear, nmc_cd.CDPrice, nmc_cd.pubID, nmc_cd.catID, nmc_publisher.pubName, nmc_publisher.location, nmc_category.catDesc
  FROM nmc_cd
  INNER JOIN nmc_publisher ON nmc_cd.pubID = nmc_publisher.pubID
  INNER JOIN nmc_category ON nmc_cd.catID = nmc_category.catID
  WHERE 1";
  //ORDER BY nmc_cd.CDTitle";

               // make an empty string that will become the AND parts of the query
               // if any values were entered in the search form
   $sqlCondition = "";


               if($search){
                $sqlCondition = $sqlCondition." AND CDTitle LIKE '%$search%'";
               }

               if($searchCDID){
                $sqlCondition = $sqlCondition." AND CDID LIKE '%$searchCDID%'";
               }

               if($searchPubID){
                $sqlCondition = $sqlCondition." AND pubID LIKE '%$searchPubID%'";
               }

               if($searchCatID){
                $sqlCondition = $sqlCondition." AND catID LIKE '%$searchCatID%'";
               }

               if($price){
                $sqlCondition = $sqlCondition." AND CDPrice LIKE '%$price%'";
               }

               if($catDesc){
                $sqlCondition = $sqlCondition." AND catDesc LIKE '%$catDesc%'";
               }

               if($CDYear){
                $sqlCondition = $sqlCondition." AND CDYear LIKE '%$CDYear%'";
               }

               if($pubName){
                $sqlCondition = $sqlCondition." AND pubName LIKE '%$pubName%'";
               }

               if($location){
                $sqlCondition = $sqlCondition." AND location LIKE '%$location%'";
               }



               $sqlSearch = $sql.$sqlCondition;

               $rsSearch = mysql_query($sqlSearch) or die(mysql_error() . "\r\n" . $sql);
               echo "<div>";
               while($row = mysql_fetch_array($rsSearch)){
                $CDID = $row['CDID'];
                $CDTitle = $row['CDTitle'];
                $CDYear = $row['CDYear'];
                $CDPrice = $row['CDPrice'];
                $pubID = $row['pubID'];
                $catID = $row['catID'];
                $pubName = $row['pubName'];
                $location = $row['location'];
                $catDesc = $row['catDesc'];
                echo "
 <div class=\"title\"><h4><a href='cd-description.php?cd=$CDID'>$CDTitle</a></h4>
  <table class=\"searchtable\">
   <tr>
    <td>Year of Release: <strong>$CDYear</strong><br /></td>
    <td>Price: <strong>£$CDPrice</strong><br /></td>
   </tr>
   <tr>
    <td>Location: <strong>$location</strong><br /></td>
    <td>Category: <strong>$catDesc</strong><br /></td>
   </tr>
  </table><hr />
 </div>";
               }
               echo "</div>";
               ?>
               </div><!--end cd-article-left div-->
  </div><!--end article div-->


  <div id="footer">
   <div id="left-footer">
 <img src="images/LogoFade.gif" alt="" />
   </div><!--end left-footer div-->
   <div id="validator">
 <a href="http://validator.w3.org/check?uri=referer" onclick="window.open(this.href,'_blank');return false;"><img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0 Strict" height="31" width="88" /></a>
 <a href="http://jigsaw.w3.org/css-validator/check/referer" onclick="window.open(this.href,'_blank');return false;"><img style="border:0;width:88px;height:31px" src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" /></a>
   </div><!--end validator div-->
  </div><!--end footer div-->
 </div><!--end main-body div-->
</div><!--end container div-->
</body>
</html>

Jessica · November 5, 2012

STOP SEARCHING WHERE 1!!!

If you want help, you have to actually try. You've been asked twice for samples of your data. You're not making any effort to debug this yourself. Do you HAVE any rows that have CDTitle like blue and price like 11.? You keep using code you've been told is wrong.

jonny7d · November 5, 2012

I don't see any issue with WHERE 1

Ok, so I have the main nmc_cd table:

CDID CDTitle CDYear pubID catID CDPrice
  978 Black President 1981 a7 63 11.70
  976 I.T.T.-International Thief Thief 1979 a7 63 11.70
  977 Sorrow, Tears and Blood 1977 a7 63 11.70
  651 A Biography of the Rev. Absalom Dawe 2000 a7 27 11.70
  652 A Bronx Tale Soundtrack 2000 a1 36 7.80
  653 A Little Deeper 2000 a1 46 8.30
  654 A Meeting by the River 2000 a1 50 12.80
  655 a new day at midnight 2000 a1 50 11.50
  656 A Night at the Village Vanguard [2 CD Set] 2000 a7 27 10.70
  657 A Night at the Village Vanguard, Vol. 2 2000 a7 27 8.30
  658 A Rush of Blood to the Head 2000 a1 50 9.30
  660 A Walk Across The Rooftops 2000 a1 49 8.80
  661 Acoustic Dead 2000 a1 44 7.90
  662 Ágætis Byrjun 2000 a1 62 8.40
  663 All Good Men 2000 a1 20 12.90

The nmc_publisher table:

pubID pubName location
a1 Warner Bros US
a2 Realworld UK
a3 Sony JP
a4 Atlantic US
a5 Naxos UK
a6 Blue Note US
a7 Decca UK
a8 Rounder US
a9 Virgin UK

And the nmc_category table:

catID catDesc
1
2 Acid Jazz
3 Alternative
4 Alternative Pop/Rock
5 Ambient
6 AvantGarde
7 Ballad
8 Bluegrass
9 Blues
10 Chamber
11 Classic Rock
12 Classical
13 Comedy
14 Contemporary Folk
15 Country
16 Cult
17 Death Metal
18 Easy Listening
19 Electronic
20 Electronica
21 Folk
22 Free Style
23 General Folk
24 General Rock
25 genre
26 Gothic
27 Jazz
28 Jungle/Drum 'N Bass
29 Keyboard
30 Latin

Edited November 5, 2012 by jonny7d

Jessica · November 5, 2012

You don't even have any rows that have a title with blue in it, let alone also being 11. in price.

Barand · November 5, 2012

Where you are searching for an ID you should be using "=" and NOT "LIKE".

jonny7d · November 5, 2012

You don't even have any rows that have a title with blue in it, let alone also being 11. in price.

Ugh.. That's a sample, the whole database is huge

Sign In

Creating An Advanced Search

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information