Advanced Html Search To Mysql Query

mashamit · December 16, 2012

Hi Guys,

My brian is failing trying to get this 'advanced search form' to query my database, ill post code then explain what im trying to do.

HTML form (straight forward if a little large

<table cellpadding="2" cellspacing="2">
 <tr>
   <td><input type="checkbox" name="persian" id="persian" rel="persian" value="persian" />
  <label for="Persian">Iran (Persia)</label></td>
   <td><input type="checkbox" name="turkmenistani" id="turkmenistani" value="turkmenistani" />
  <label for="Turkmenistani">Turkmenistan</label></td>
   <td><input type="checkbox" name="afghani" id="Afghani" value="afghanistani" />
  <label for="Afghan">Afghanistan</label></td>
   <td><input name="pakistani" type="checkbox" id="Pakistani" value="pakistani" />
  <label for="Pakistani">Pakistan</label></td>
   <td><input type="checkbox" name="kashmiri" id="Kashmiri" value="kashmiri" />
  <label for="Kashmiri">Kashmir</label></td>
   <td><input type="checkbox" name="indian" id="Indian"  value="indian" />
  <label for="Indian">India</label></td>
   <td><input type="checkbox" name="nepalese" id="Nepalese" value="nepalese" />
  <label for="Nepalese">Nepal</label></td>
   <td><input type="checkbox" name="chinese" id="Chinese" value="chinese" />
  <label for="Chinese">China</label></td>
   <td> </td>
   <td> </td>
 </tr>
</table>
</td>
 </tr>
   <tr>
   <td> </td>
 </tr>
 <tr>
   <td><u>Select Types</u></td>
 </tr>
 <tr rel="persian">
   <td><table cellpadding="2" cellspacing="2">
 <tr>
   <td width="82">Persian: </td>
   <td><input type="checkbox" name="city" id="City" value="city" />
  <label for="City">City</label></td>
   <td><input type="checkbox" name="village" id="Village" value="village" />
  <label for="Village">Village</label></td>
   <td><input type="checkbox" name="tribal" id="Tribal" value="tribal" />
  <label for="Tribal">Tribal</label></td>
 </tr>
</table>
</td>
 </tr>
   <tr>
   <td><table cellpadding="2" cellspacing="2">
 <tr>
   <td width="81">Traditional: </td>
   <td><input type="checkbox" name="geometric" id="Geometric" value="geometric" />
  <label for="Geometric">Geometric</label></td>
   <td> 
  <input type="checkbox" name="floral" id="Floral" value="floral" />
  <label for="Floral">Floral</label>
  </td>
 </tr>
</table>
</td>
 </tr>
   <tr>
   <td><table>
 <tr>
   <td width="84">Modern: </td>
   <td><input type="checkbox" name="plain" id="Plain" value="plain" />
  <label for="Plain">Plain</label></td>
	  <td><input type="checkbox" name="bordered" id="bordered" value="bordered" />
  <label for="Bordered">Plain With Border</label></td>
   <td><input type="checkbox" name="funky" id="Funky" value="funky" />
  <label for="Funky">Funky</label></td>
   <td> </td>
 </tr>
</table>
</td>
 </tr>
 <tr>
   <td> </td>
 </tr>
</table>
<table width="488" cellpadding="2" cellspacing="2">
 <tr align="center">
   <td colspan="13" align="left"><u>Choose  Main Colour</u></td>
 </tr>
 <tr align="center">
   <td width="20"><img src="searchimgs/black.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/brown.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/cream.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/darkblue.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/denimblue.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/green.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/grey.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/oatmeal.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/paleblue.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/purple.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/red.gif" width="20" height="20" /></td>
   <td width="32"><img src="searchimgs/rust.gif" width="20" height="20" /></td>
   <td width="34"><img src="searchimgs/brickorange.gif" width="20" height="20" /></td>
   </tr>
 <tr align="center">
   <td><input type="checkbox" name="black" id="black" value="black"/>
  <label for="black"></label></td>
   <td><input type="checkbox" name="brown" id="brown" value="brown" />
  <label for="brown"></label></td>
   <td><input type="checkbox" name="white" id="white" value="white" />
  <label for="white"></label></td>
   <td><input type="checkbox" name="dark blue" id="dark-blue" value="dark-blue" />
  <label for="dark-blue"></label></td>
   <td><input type="checkbox" name="denim blue" id="denim-blue" value="denim-blue" />
  <label for="denim-blue"></label></td>
   <td><input type="checkbox" name="green" id="green" value="green" />
  <label for="green"></label></td>
   <td><input type="checkbox" name="grey" id="grey" value="grey" />
  <label for="grey"></label></td>
   <td><input type="checkbox" name="oatmeal" id="oatmeal" value="oatmeal" />
  <label for="oatmeal"></label></td>
   <td><input type="checkbox" name="pale blue" id="pale-blue" value="pale-blue" />
  <label for="pale-blue"></label></td>
   <td><input type="checkbox" name="purple" id="purple" value="purple" />
  <label for="purple"></label></td>
   <td><input type="checkbox" name="red" id="red"  value="red"/>
  <label for="red"></label></td>
   <td><input type="checkbox" name="rust" id="rust" value="rust" />
  <label for="rust"></label></td>
   <td><input type="checkbox" name="brick-orange" id="brick-orange" value="" />
  <label for="brick-orange"></label></td>
   </tr>
 <tr align="center">
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
 </tr>
</table>
<table width="98%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="12%"><u>Size</u></td>
   <td width="11%"> </td>
   <td width="12%"> </td>
   <td width="65%"> </td>
 </tr>
 <tr>
   <td><label for="Width-Min">Width Min (feet): </label></td>
   <td><select name="widthmin" id="Width-Min">
  <option value="1" selected="selected">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
  <option value="5">5</option>
  <option value="6">6</option>
  <option value="7">7</option>
  <option value="8">8</option>
  <option value="9">9</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20">20</option>
   </select></td>
   <td><label for="Width-Max">Width Max (feet):</label></td>
   <td><select name="widthmax" id="Width-Max">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
  <option value="5">5</option>
  <option value="6">6</option>
  <option value="7">7</option>
  <option value="8">8</option>
  <option value="9">9</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20" selected="selected">20</option>
   </select></td>
 </tr>
 <tr>
   <td><label for="Length-Min">Length Min (feet): </label></td>
   <td><select name="lengthmin" id="Length-Min">
  <option value="1" selected="selected">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
  <option value="5">5</option>
  <option value="6">6</option>
  <option value="7">7</option>
  <option value="8">8</option>
  <option value="9">9</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20">20</option>
   </select></td>
   <td><label for="Length-Max">Length Max (feet):</label></td>
   <td><select name="lengthmax" id="Length-Max">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
  <option value="5">5</option>
  <option value="6">6</option>
  <option value="7">7</option>
  <option value="8">8</option>
  <option value="9">9</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20" selected="selected">20</option>
   </select></td>
 </tr>
 <tr>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
 </tr>
</table>
<table width="98%" cellpadding="2" cellspacing="2">
 <tr>
   <td width="12%"><u>Price</u></td>
   <td width="5%"> </td>
   <td width="11%"> </td>
   <td width="66%"> </td>
   <td width="6%"> </td>
 </tr>
 <tr>
   <td>Price Min (£):</td>
   <td><label for="Price-Min"></label>
  <select name="pricemin" id="Price-Min">
    <option value="1" selected="selected">1</option>
    <option value="100">100</option>
    <option value="1000">1000</option>
    <option value="2500">2500</option>
    <option value="5000">5000</option>
    <option value="10000">10000</option>
    <option value="15000">15000</option>
  </select></td>
   <td>Price Min (£):</td>
   <td>	  <select name="pricemax" id="pricemax">
    <option value="1">1</option>
    <option value="100">100</option>
    <option value="1000">1000</option>
    <option value="2500">2500</option>
    <option value="5000">5000</option>
    <option value="10000">10000</option>
    <option value="15000" selected="selected">15000</option>
  </select></td>
   <td> </td>
 </tr>
 <tr>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
   <td> </td>
 </tr>
</table>
<br /><br />

	    <input type='submit' name='submit' value='Search'>
    </form>
</td></tr>
   <!-- page end -->  
    </table>

And the PHP page for query and output

<?php session_start();
include('connect.php');
/* countries   */
$persian = $_GET['persian'];
$turk = $_GET['turkmenistani'];
$afghan = $_GET['afghani'];
$pakis = $_GET['pakistani'];
$kash = $_GET['kashmiri'];
$india = $_GET['indian'];
$nepal = $_GET['nepalese'];
$china = $_GET['chinese'];
/* if persian */
$city = $_GET['city'];
$village = $_GET['village'];
$tribal = $_GET['tribal'];
/* traditional  */
$geo = $_GET['geometric'];
$floral = $_GET['floral'];
/* modern */
$plain = $_GET['plain'];
$bordered = $_GET['bordered'];
$funky = $_GET['funky'];
/* colour */
$black = $_GET['black'];
$brown = $_GET['brown'];
$white = $_GET['white'];
$darkblue = $_GET['dark-blue'];
$denimblue = $_GET['denim-blue'];
$green = $_GET['green'];
$grey = $_GET['grey'];
$oatmeal = $_GET['oatmeal'];
$paleblue = $_GET['pale-blue'];
$purple = $_GET['purple'];
$red = $_GET['red'];
$rust = $_GET['rust'];
$brickorange = $_GET['brickorange'];
/* sizes */
$widthmin = $_GET['widthmin'];
$widthmax = $_GET['widthmax'];
$lengthmin = $_GET['lengthmin'];
$lengthmax = $_GET['lengthmax'];
/* prices */
$pricemin = $_GET['pricemin'];
$pricemax = $_GET['pricemax'];
/*output  */
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<meta name="description" content="rug search results" />
<link href="/css/main.css" rel="stylesheet" type="text/css" />
<link href="../css/ac.css" rel="stylesheet" type="text/css" />
<link href="/css/top-navigation.css" rel="stylesheet" type="text/css" />
<link href="../css/mainz.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="css/newrug.css"/>
<!--[if IE 6]><link rel="stylesheet" type="text/css" href="css/ie6.css" media="screen, projection" /><![endif]-->
<!--[if IE 7]><link rel="stylesheet" type="text/css" href="css/ie7.css" media="screen, projection" /><![endif]-->
<script type="text/javascript" src="/js/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/js/global.js"></script>
<!--[if gte IE 9]>
<script type="text/javascript">
Cufon.set('engine', 'canvas');
</script>
<![endif]-->
<script type="text/javascript" src="/js/jquery.min.js"></script>
<script type="text/javascript" src="/js/cloud-zoom.1.0.2.js"></script>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0" class="rugpage">
 <tr>
   <td><table width="1000" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td height="127" class="header-nav"><?php include("rug-header.php");?></td>
  </tr>

  <tr>
    <td height="35" bgcolor=""><table width="1000" border="0" cellpadding="0" cellspacing="0" bgcolor="#81c6e7">
	  <tr>
	    <td width="697" height="45"  class="head-text">rugS</td>
	  </tr>
    </table></td>
  </tr>
  <tr>
    <td bgcolor=""><table width="1000" border="0" cellspacing="0" cellpadding="0">
	  <tr>
	    <td> </td>
	  </tr>
	  <tr>
	    <td><div class="rug-menu-current"><a name="Home" href="../index.php">Home</a> > <a href="rugs.php" name="rugs" id="rugs">rugs</a><a href="../rugs.php"></a> > <a href="#" name="modern-rugs" id="custom-rug-searcc">custom Search</a> ><a href="#" name="search-results" title="Results for custom search">search results</a></div></td>
	  </tr>
	  <tr>
	    <td> </td>
	  </tr>	  
    <!-- page start -->		 
	 <tr><td><div>
	 <div><!-- search area begins -->
<?
 $strSQL = "SELECT * FROM rugs WHERE ";
 $objQuery = mysql_query($strSQL);
 $Num_Rows = mysql_num_rows($objQuery);
 $Per_Page = 100;   // Per Page
 $Page = $_GET["Page"];
 if(!$_GET["Page"])
 {
  $Page=1;
 }
 $Prev_Page = $Page-1;
 $Next_Page = $Page+1;
 $Page_Start = (($Per_Page*$Page)-$Per_Page);
 if($Num_Rows<=$Per_Page)
 {
  $Num_Pages =1;
 }
 else if(($Num_Rows % $Per_Page)==0)
 {
  $Num_Pages =($Num_Rows/$Per_Page) ;
 }
 else
 {
  $Num_Pages =($Num_Rows/$Per_Page)+1;
  $Num_Pages = (int)$Num_Pages;
 }
 echo"Your search has found $Num_Rows rugs, please click on an image to get full details.<br><BR><br><br>";
 echo $countries;
 $strSQL .=" order by rugid ASC LIMIT $Page_Start , $Per_Page";
 $objQuery  = mysql_query($strSQL);

 echo"<table border=\"0\"  cellspacing=\"1\" cellpadding=\"1\"><tr>";
 $intRows = 0;
 while($objResult = mysql_fetch_array($objQuery))
 {
  echo "<td width='110'>";
  $intRows++;
?>
  <center>
<a name="<?=$objResult["type"];?> <?=$objResult["maincolour"];?>" title="Click here for more information on the <?=$objResult["type"];?> <?=$objResult["maincolour"];?> rug" href="/rug/rugs-modern-view-rug.php?rugid=<?=$objResult["rugid"];?>"><img class="searchthumbs" src="/rug/thumbrug/<?=$objResult["thumbimg"];?>" width="110" height="152" /></a>			  <br>
   <?=$objResult["type"];?> <?=$objResult["maincolour"];?>
   <br /><br />
  </center>
<?
  echo"</td>";
  if(($intRows)%7==0)
  {
   echo"</tr>";
  }
 }
 echo"</tr></table>";
?>

 <script type="text/javascript">
var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1");
 </script>
</body>
</html>
<?
mysql_close($objConnect);
?>






	   </div>		 </td>
 </tr>
   <!-- page end -->  
    </table></td>
  </tr></table></td>
 </tr>
 <tr>
   <td height="150" valign="top" bgcolor="#2f2023"><table width="1000" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td height="156" class="footer-nav-bg"><?php include("../footer.php");?></td>
  </tr>
   </table></td>
 </tr>
</table>
<!-- Load jQuery and idTabs -->
<script type="text/javascript" src="/js/jquery.js"></script>
<script type="text/javascript" src="/js/jquery.idTabs.js"></script>
<script type="text/javascript">
var fade = function(id,s){
 s.tabs.removeClass(s.selected);
 s.tab(id).addClass(s.selected);
 s.items.fadeOut();
 s.item(id).fadeIn();
 return false;
};
$.fn.fadeTabs = $.idTabs.extend(fade);
$(".fade").fadeTabs();
</script>
</body>
</html>

You will have to excuse the layout, its very much work in progress.

the php outputs tiled results from the search parameters, this works great with a couple set parameters, however from the form id like to have a few 'must have' criteria and a few 'optional' criteria,

For instance

Any Country

Any Colour

But a set price bracket and size

Im assuming that changing this following line will do that.

$strSQL = "SELECT * FROM rugs WHERE ";

Ive tried a few variations but cant seem to get anything near, so any hints or pointers would be much appreciated

Nick

Sign In

Advanced Html Search To Mysql Query

Recommended Posts

mashamit

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information