Jump to content

jonny7d

Members
 View Profile  See their activity

  • Posts

    25

  • Joined

  • Last visited

jonny7d's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. jonny7d
    You are best of using AJAX, it allows you to implement server side and client side processing. See this link, I think it is roughly what you are attempting to do: http://www.w3schools.com/ajax/ajax_database.asp
  2. jonny7d
    Ugh.. That's a sample, the whole database is huge
  3. jonny7d
    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
  4. jonny7d
    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>
  5. jonny7d
    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 = 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.%'
  6. jonny7d
    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;
  7. jonny7d
    @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.
  8. jonny7d
    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%'"; }
  9. jonny7d
    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%'"; }
  10. jonny7d
    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%'"; } 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
  11. jonny7d
    I edited the code above and I took out the WHERE clause completely, and it stills gives me the whole database
  12. jonny7d
    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>
  13. jonny7d
    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.
  14. jonny7d
    I have WHERE implemented, but it won't work. I posted the code with the website a couple of posts back
  15. jonny7d
    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.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.