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: [quote]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[/quote] I cannot seem to hit the nail on the bud to why it shouldn't work. [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)){ $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>[/code] Thanks for looking, Jonathan
