Creating an Advanced Form

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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"/>&#163;7-8
                        <input type="radio" name="price" value="8-9"/>&#163;8-9
                        <input type="radio" name="price" value="9-10"/>&#163;9-10
                        <input type="radio" name="price" value="10-11"/>&#163;10-11
                        <input type="radio" name="price" value="11-12"/>&#163;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>&#163;$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

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux