Skip navigation links

MySQL Forums :: PHP :: PHP MySQLadvanced search form


Advanced Search

PHP MySQLadvanced search form
Posted by: nick dewhirst ()
Date: December 17, 2012 03:05PM

Good Evening Everyone,

Im fairly new to php/mysql. I have a form and php search output script that works with a few search criteria. Im want to expand to include multiple types or search criteria from a form. Im coming unstuck with what to use within the string to correctly build the query. Ill post the form and php then explain a bit further

FORM

<table width="980" border="0" cellspacing="0" cellpadding="0">
         <tr>
            <td height="35"><table width="1000" border="0" cellspacing="0" cellpadding="0">
            <td>&nbsp;</td>
              <tr>
                <td><div class="rug-menu-current"><a href="/index.php">Home</a> &gt; <a href="rugs.php">Rugs</a> &gt; <a href="rugsearch.php">Custom rug Search</a></div></td>
                </tr>
            </table></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
          </tr>       
        <!-- page start -->          
         <tr><td>
        <form action="custom-search-results.php" method="GET">
          <p>
          <table width="100%">
  <tr>
    <td></td>
  </tr>
  <tr>
    <td><u>Select Countries</u></td>
  </tr>
  <tr>
    <td><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>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</td>
  </tr>
    <tr>
    <td>&nbsp;</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>&nbsp;
      <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>&nbsp;</td>
  </tr>
</table>
</td>
  </tr>
  <tr>
    <td>&nbsp;</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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<table width="98%" cellspacing="2" cellpadding="2">
  <tr>
    <td width="12%"><u>Size</u></td>
    <td width="11%">&nbsp;</td>
    <td width="12%">&nbsp;</td>
    <td width="65%">&nbsp;</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>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<table width="98%" cellpadding="2" cellspacing="2">
  <tr>
    <td width="12%"><u>Price</u></td>
    <td width="5%">&nbsp;</td>
    <td width="11%">&nbsp;</td>
    <td width="66%">&nbsp;</td>
    <td width="6%">&nbsp;</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>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<br /><br />
            
            <input type='submit' name='submit' value='Search'>

        </form> 
</td></tr>
       <!-- page end -->   
        </table>

PHP Search 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>Larsen Interiors - Rugs</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="#2f2023" style="padding-left:10px; padding-right:10px"><table width="980" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td><div class="rug-menu-current"><a name="Home" href="../index.php">Home</a> &gt; <a href="rugs.php" name="rugs" id="rugs">rugs</a><a href="../rugs.php"></a> &gt; <a href="#" name="modern-rugs" id="custom-rug-searcc">custom Search</a> &gt;<a href="#" name="search-results" title="Results for custom search">search results</a></div></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
          </tr>       
        <!-- page start -->          
         <tr><td><div>
         <div><!-- search area begins -->
<?
		$strSQL = "SELECT * FROM rugs WHERE country='$countries'";
		$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>";
	?>


<?
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>


I know I need to build the query around this point

$strSQL = "SELECT * FROM rugs WHERE country='$countries'";

I want the search to output multiple countries/types and colours

but specific a price/size range.


Im not looking for someone to drop a solution on me, more an idea what combination of LIKE / AGAINST etc etc I should be using, or if I should be looking at it a different way.


Ive tried a#to put a few combinations together, but they either out put 1 item or nothing at all

Any help would be appreciated

Options: ReplyQuote


Subject Written By Posted
PHP MySQLadvanced search form nick dewhirst 12/17/2012 03:05PM
Re: PHP MySQLadvanced search form laptop alias 12/18/2012 01:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.