Creating an Advanced Form
| From: | Jonathan Davies | Date: | Fri, 02 Nov 2012 15:15:51 +0000 |
| Subject: | Creating an Advanced Form | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-319649@lists.php.net to get a copy of this message | ||
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