Creating An Advanced Search

jonny7d · November 2, 2012

Hi,

I am attempting to create an advanced form with 9 different search fields.

I am using the WHERE 1 AND sql function to add onto the generated end sql function so I don't have to created over 300,000 IF statements. The code below is showing up this error:

Warning: mysql_fetch_assoc(): 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 261

I cannot seem to hit the nail on the bud to why it shouldn't work. EDIT: I have highlighted where 261 is on my 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="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
if(isset($_GET["search"])){
$search = $_GET['search'];
} else {
null;
}
if(isset($_GET["searchCDID"])){
$searchCDID = $_GET['searchCDID'];
} else {
null;
}
if(isset($_GET["searchPubID"])){
$searchPubID = $_GET['searchPubID'];
} else {
null;
}
if(isset($_GET["searchCatID"])){
$searchCatID = $_GET['searchCatID'];
} else {
null;
}
if(isset($_GET["price"])){
$price = $_GET['price'];
} else {
null;
}
if(isset($_GET["$catDesc"])){
$catDesc = $_GET['$catDesc'];
} else {
null;
}
if(isset($_GET["$CDYear"])){
$CDYear = $_GET['$CDYear'];
} else {
null;
}
if(isset($_GET["$pubName"])){
$pubName = $_GET['$pubName'];
} else {
null;
}
if(isset($_GET["$location"])){
$location = $_GET['$location'];
} else {
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 + "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 '%$search%'
ORDER BY nmc_cd.CDTitle";
}

if($searchCDID=1){
$sqlCondition = $sqlCondition + "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 CDID LIKE '%$searchCDID%'
ORDER BY nmc_cd.CDTitle";
}

if($searchPubID=1){
$sqlCondition = $sqlCondition + "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 pubID LIKE '%$searchPubID%'
ORDER BY nmc_cd.CDTitle";
}

if($pubName=1){
$sqlCondition = $sqlCondition + "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 pubName LIKE '%$pubName%'
ORDER BY nmc_cd.CDTitle";
}

if($location=1){
$sqlCondition = $sqlCondition + "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 location LIKE '%$location%'
ORDER BY nmc_cd.CDTitle";
}

if($price=1){
$sqlCondition = $sqlCondition + "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 CDPrice LIKE '%$price%'
ORDER BY nmc_cd.CDTitle";
}

if($catDesc=1){
$sqlCondition = $sqlCondition + "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 catDesc LIKE '%$catDesc%'
ORDER BY nmc_cd.CDTitle";
}

if($CDYear=1){
$sqlCondition = $sqlCondition + "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 CDYear LIKE '%$CDYear%'
ORDER BY nmc_cd.CDTitle";
}

if($searchCDID=1){
$sqlCondition = $sqlCondition + "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 CDID LIKE '%$searchCDID%'
ORDER BY nmc_cd.CDTitle";
}

$sqlSearch = $sql + $sqlCondition;

$rsSearch = mysql_query($sqlSearch);
echo "<div>";
while($row = mysql_fetch_assoc($rsSearch)){ [size=6][b]/* This is line 261 */[/b][/size]
$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></div>
<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 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>

Thanks for looking,

Jonathan

Edited November 2, 2012 by jonny7d

White_Lily · November 2, 2012

when using joins you need to use mysql_fetch_array() not mysql_fetch_assoc().

MDCode · November 2, 2012

Edit: ^^

Which line is 261?

Edited November 2, 2012 by ExtremeGaming

White_Lily · November 2, 2012

line 261 is the while loop near the bottom

Tip: copy the code into notepad++ or dreamweaver, then you will find line 261...

Edited November 2, 2012 by White_Lily

jonny7d · November 2, 2012

Edit: ^^

Which line is 261?

I have put where line 261 is, however it is hard to find because it is wrapped to code only (so I cannot add bold text etc)

It's roughly near the bottom and it is the start of the while loop. mysql_fetch_assoc

jonny7d · November 2, 2012

when using joins you need to use mysql_fetch_array() not mysql_fetch_assoc().

Thanks for the quick response.

I have tried changing it to array but that does nothing and generates the same error.

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 261

mysql_fetch_assoc also works on my other documents fine.

Christian F. · November 2, 2012

White_Lily: mysql_fetch_array () or mysql_fetch_assoc () has absolutely no bearing on this, and neither function cares one bit about what the SQL query looks like.

As I've told others: It is very nice to see that you want to help people, but please make sure that you're actually helping them. Giving answers like the one above only serves to create even more misunderstandings, which is nothing but detrimental to the process.

Jonny: What you need to do is described in this post:

http://forums.phpfreaks.com/index.php?topic=365029.msg1730174#msg1730174

Also, the WHERE 1 AND bit of your query is quite useless, you don't need the 1 AND part at all. It doesn't do anything.

As for your IF-sentences, well... Just follow the post I linked above, and you'll see what the problem is.

jonny7d · November 2, 2012

White_Lily: mysql_fetch_array () or mysql_fetch_assoc () has absolutely no bearing on this, and neither function cares one bit about what the SQL query looks like.

As I've told others: It is very nice to see that you want to help people, but please make sure that you're actually helping them. Giving answers like the one above only serves to create even more misunderstandings, which is nothing but detrimental to the process.

Jonny: What you need to do is described in this post:

http://forums.phpfre...0174#msg1730174

Also, the WHERE 1 AND bit of your query is quite useless, you don't need the 1 AND part at all. It doesn't do anything.

As for your IF-sentences, well... Just follow the post I linked above, and you'll see what the problem is.

Now I get these errors.

Notice: Query Failed! SQL: 0 - 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 '0' at line 1 in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 259

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 261

This is how it looks right now

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

Jessica · November 2, 2012

$sqlSearch = $sql + $sqlCondition;

Why do you keep trying to add strings? In PHP concatenation is with the .

jonny7d · November 2, 2012

$sqlSearch = $sql + $sqlCondition;

Why do you keep trying to add strings? In PHP concatenation is with the .

Well that is something I didn't know, thanks Jessica.

However, it is still giving the same error

Jessica · November 2, 2012

You're going to have to fix it everywhere you were using the + sign. Do that, and post your updated code and errors.

jonny7d · November 2, 2012

You're going to have to fix it everywhere you were using the + sign. Do that, and post your updated code and errors.

Here you are, 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="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
               if(isset($_GET["search"])){
                $search = $_GET['search'];
               } else {
                null;
               }
               if(isset($_GET["searchCDID"])){
                $searchCDID = $_GET['searchCDID'];
               } else {
                null;
               }
               if(isset($_GET["searchPubID"])){
                $searchPubID = $_GET['searchPubID'];
               } else {
                null;
               }
               if(isset($_GET["searchCatID"])){
                $searchCatID = $_GET['searchCatID'];
               } else {
                null;
               }
               if(isset($_GET["price"])){
                $price = $_GET['price'];
               } else {
                null;
               }
               if(isset($_GET["$catDesc"])){
                $catDesc = $_GET['$catDesc'];
               } else {
                null;
               }
               if(isset($_GET["$CDYear"])){
                $CDYear = $_GET['$CDYear'];
               } else {
                null;
               }
               if(isset($_GET["$pubName"])){
                $pubName = $_GET['$pubName'];
               } else {
                null;
               }
               if(isset($_GET["$location"])){
                $location = $_GET['$location'];
               } else {
                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."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 '%$search%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($searchCDID=1){
                $sqlCondition = $sqlCondition."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 CDID LIKE '%$searchCDID%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($searchPubID=1){
                $sqlCondition = $sqlCondition."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 pubID LIKE '%$searchPubID%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($pubName=1){
                $sqlCondition = $sqlCondition."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 pubName LIKE '%$pubName%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($location=1){
                $sqlCondition = $sqlCondition."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 location LIKE '%$location%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($price=1){
                $sqlCondition = $sqlCondition."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 CDPrice LIKE '%$price%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($catDesc=1){
                $sqlCondition = $sqlCondition."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 catDesc LIKE '%$catDesc%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($CDYear=1){
                $sqlCondition = $sqlCondition."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 CDYear LIKE '%$CDYear%'
                ORDER BY nmc_cd.CDTitle";
               }

               if($searchCDID=1){
                $sqlCondition = $sqlCondition."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 CDID LIKE '%$searchCDID%'
                ORDER BY nmc_cd.CDTitle";
               }

               $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></div>
  <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 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>

Errors:

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 1 ORDER BY nmc_cd.CDTitleSELECT 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 '%1%' ORDER BY nmc_cd.CDTitleSELECT 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 in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 259

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 261

Jessica · November 2, 2012

We can't read your error messages when you post them like that, you realize that right?

At least now there's your query. There's clearly a big problem with it, if you can read it.

jonny7d · November 2, 2012

We can't read your error messages when you post them like that, you realize that right?

At least now there's your query. There's clearly a big problem with it, if you can read it.

My bad, here you go.

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 1 ORDER BY nmc_cd.CDTitleSELECT 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 '%1%' ORDER BY nmc_cd.CDTitleSELECT 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 in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 259

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 261

Edited November 2, 2012 by jonny7d

Jessica · November 2, 2012

Once again. A clear problem.

jonny7d · November 2, 2012

Once again. A clear problem.

Yup I'm narrowing it right down now. Now I have this 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="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
if(isset($_GET["search"])){
$search = $_GET['search'];
} else {
null;
}
if(isset($_GET["searchCDID"])){
$searchCDID = $_GET['searchCDID'];
} else {
null;
}
if(isset($_GET["searchPubID"])){
$searchPubID = $_GET['searchPubID'];
} else {
null;
}
if(isset($_GET["searchCatID"])){
$searchCatID = $_GET['searchCatID'];
} else {
null;
}
if(isset($_GET["price"])){
$price = $_GET['price'];
} else {
null;
}
if(isset($_GET["$catDesc"])){
$catDesc = $_GET['$catDesc'];
} else {
null;
}
if(isset($_GET["$CDYear"])){
$CDYear = $_GET['$CDYear'];
} else {
null;
}
if(isset($_GET["$pubName"])){
$pubName = $_GET['$pubName'];
} else {
null;
}
if(isset($_GET["$location"])){
$location = $_GET['$location'];
} else {
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($pubName=1){
$sqlCondition = $sqlCondition." AND pubName LIKE '%$pubName%'";
}

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

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($searchCDID=1){
$sqlCondition = $sqlCondition." AND CDID LIKE '%$searchCDID%'";
}

$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></div>
 <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 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>

And this error

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 1 AND CDTitle LIKE '%1%' AND CDID LIKE '%1%' AND pubID LIKE '%1%' AND pubName LIKE '%1%' AND location LIKE '%1%' AND CDPrice LIKE '%1%' AND catDesc LIKE '%1%' AND CDYear LIKE '%1%' AND CDID LIKE '%1%' - Error: Column 'pubID' in where clause is ambiguous in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 214

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 216

I am not too sure what the first error is?

Edited November 2, 2012 by jonny7d

jonny7d · November 2, 2012

Solved that one! it was the if statements, I was assigning the variables to 1 with '=' instead of giving them an if '==' true.

Now here's the error:

Notice: Undefined variable: search in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 176

Notice: Undefined variable: searchCDID in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 180

Notice: Undefined variable: searchPubID in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 184

Notice: Undefined variable: price in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 196

Notice: Undefined variable: searchCDID in/var/www/vhosts/numyspace.co.uk/web_users/home/~unn_w11014928/public_html/search.php on line 208

Edited November 2, 2012 by jonny7d

Jessica · November 2, 2012

if(isset($_GET["search"])){
$search = $_GET['search'];
} else {
null;
}

This does nothing useful.

You could use a ternary.

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

jonny7d · November 2, 2012

if(isset($_GET["search"])){
$search = $_GET['search'];
} else {
null;
}
This does nothing useful.

You could use a ternary.

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

No errors Jessica! However, when the user tries to enter a field to show up on the database, nothing happens, and it just sticks with the same database.

Here is the website: http://numyspace.co.uk/~unn_w11014928/search.php

And here is the 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);
               $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>

Thanks for all your help so far!

jonny7d · November 3, 2012

Any ideas on how to get the sql to actually look for specific data when typed in? Instead of it just displaying the whole data automatically.

Jessica · November 3, 2012

WHERE.

jonny7d · November 4, 2012

I have WHERE implemented, but it won't work. I posted the code with the website a couple of posts back

Christian F. · November 4, 2012

Define "won't work". Also, please ensure that the data you're searching is actually in the database, and that the query actually returns any rows. Telling us what you've searched for would probably also be of help, especially if you show the rows from the database that you're expecting to get from said search.

jonny7d · November 4, 2012

Define "won't work". Also, please ensure that the data you're searching is actually in the database, and that the query actually returns any rows. Telling us what you've searched for would probably also be of help, especially if you show the rows from the database that you're expecting to get from said search.

For instance, when I search for black in the CD Title search box, it should show a CD with the title black. However, the search does nothing and still insists in displaying the whole database.

Jessica · November 4, 2012

Post your updated code.

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

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