从多个下拉列表中过滤数据库的结果

Question

我已将搜索功能并入我的网站，该功能搜索特定邮政编码和选定半径内的所有组织。这部分运作良好。我现在正在尝试整合一个类别搜索，因此它不是只搜索一个压缩文件的选定距离内的所有组织，而是在一个压缩文件的选定距离内搜索选定类别中的所有组织。但是，搜索未检测到所选类别。任何想法为什么？ 下面是代码：

<?php include('./Connections/ecsdb.php'); ?> 
<script type="text/JavaScript"> 

function MM_goToURL() { //v3.0 
    var i, args=MM_goToURL.arguments; document.MM_returnValue = false; 
    for (i=0; i<(args.length-1); i+=2) eval(args[i]+".location='"+args[i+1]+"'"); 
} 

</script> 
<?php 

mysql_select_db($database_ecsdb, $ecsdb); 
$query_getCat = "SELECT DISTINCT category FROM food ORDER BY category DESC"; 
$getCat = mysql_query($query_getCat, $ecsdb) or die(mysql_error()); 
$row_getCat = mysql_fetch_assoc($getCat); 
$totalRows_getCat = mysql_num_rows($getCat); 

?> 

<a name="top" id="top"></a> 


    <div style="float:right;"> 

    <?php 

    $my_loginbutton = "<input name=\"toLogin\" type=\"button\" onclick=\"MM_goToURL('parent','./login/');return document.MM_returnValue\" value=\"Login\" />"; 
    $my_logoutbutton = "<input name=\"Logout\" type=\"button\" onclick=\"MM_goToURL('parent','./login/logout.php');return document.MM_returnValue\" value=\"Logout\" />"; 

    if (isset($_SESSION['MM_Username'])) 
     echo $my_logoutbutton; 
    else 
     echo $my_loginbutton; 
    ?> 

</div> 


    <div id="header" > 
<div style="float:left;"><img name="Alpha Accounting logo" src="./images/banner.png" width="800" height="140" border="0" alt="Alpha Accounting"></div> 
<div style="margin: 10px 0 0 5px; float:left;"></div> 
<div id="search"> 
<h2> Search for Organizations</h2> 
<br> 


<form action="../caretest/search.php" method="post"> 

<label name="category"> 
    Category: 
     <select name="category"> 
      <?php 
    do { 
    ?> 
       <option value="<?php echo $row_getCat['category']?>"><?php echo $row_getCat['category']?></option> 
       <?php 
    } while ($row_getCat = mysql_fetch_assoc($getCat)); 
     $rows = mysql_num_rows($getCat); 
     if($rows > 0) { 
      mysql_data_seek($getCat, 0); 
      $row_getCat = mysql_fetch_assoc($getCat); 
     } 
    ?> 
    </select> 
    </label> 

    <label>Enter your ZIP Code: 
    <input maxlength="5" name="zipcode" size="6" type="text" /></label> </br> 

<label>Select a distance:</label> 
<select name="distance"> 
<option>5</option> 
<option>10</option> 
<option>25</option> 
<option>50</option> 
<option>100</option> 
</select> 

<input name="submit" type="submit" value="search" /> 


</form> 

<?php 
     if(isset($_POST['submit'])) { 
      if(!preg_match('/^[A-Za-z]{3-50}$/', $_POST['category'])){ 
       echo "<p><strong>You must select a category. </strong> Please try again.</p>\n"; 
      } 
      if(!preg_match('/^[0-9]{5}$/', $_POST['zipcode'])) { 
       echo "<p><strong>You did not enter a properly formatted ZIP Code.</strong> Please try again.</p>\n"; 
      } 
      elseif(!preg_match('/^[0-9]{1,3}$/', $_POST['distance'])) { 
       echo "<p><strong>You did not enter a properly formatted distance.</strong> Please try again.</p>\n"; 
      } 
      else { 
       //connect to db server; select database 
       $link = mysql_connect($hostname_ecsdb, $username_ecsdb, $password_ecsdb) or die('Cannot connect to database server'); 
       mysql_select_db($database_ecsdb) or die('Cannot select database'); 

       //query for coordinates of provided ZIP Code 
       if(!$rs = mysql_query("SELECT * FROM zip WHERE zip_code = '$_POST[zipcode]'")) { 
        echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>\n"; 
       } 
       else { 
        if(mysql_num_rows($rs) == 0) { 
         echo "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>\n"; 
        } 
        else { 
         //if found, set variables 
         $row = mysql_fetch_array($rs); 
         $lat1 = $row['latitude']; 
         $lon1 = $row['longitude']; 
         $d = $_POST['distance']; 
         $r = 3959; 

         //compute max and min latitudes/longitudes for search square 
         $latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d/$r) + cos(deg2rad($lat1)) * sin($d/$r) * cos(deg2rad(0)))); 
         $latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d/$r) + cos(deg2rad($lat1)) * sin($d/$r) * cos(deg2rad(180)))); 
         $lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d/$r) * cos(deg2rad($lat1)), cos($d/$r) - sin(deg2rad($lat1)) * sin(deg2rad($latN)))); 
         $lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d/$r) * cos(deg2rad($lat1)), cos($d/$r) - sin(deg2rad($lat1)) * sin(deg2rad($latN)))); 

         //find all coordinates within the search square's area 
         //exclude the starting point and any empty city values 
         $query = "SELECT * FROM zip WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude"; 
         if(!$rs = mysql_query($query)) { 
          echo "<p><strong>There was an error selecting nearby ZIP Codes from the database.</strong></p>\n"; 
         } 
         elseif(mysql_num_rows($rs) == 0) { 
          echo "<p><strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.</p>\n";        
         } 
         else { 
          //output all matches to screen 

          echo"<br />"; 
          echo"<br />"; 
          //echo "<h2> Organization Search Results</h2>"; 

          echo "<table class=\"bordered\" cellspacing=\"0\">\n"; 
          echo"<tr><th></th></tr>\n"; 
          echo "<h2> Organization Search Results</h2>"; 
          echo"<tr><th></th></tr>\n"; 

          echo "<tr><th>Agency</th><th>Telephone</th><th>Hours</th><th>Street Address</th><th>City</th><th>State</th><th>Zip</th><th>Website</th><th>Directions</th></tr>\n"; 
          while($row = mysql_fetch_array($rs)) { 
           //search for matching zip codes in the organization table 
           if(!$rt = mysql_query("SELECT * FROM food WHERE zip = '$row[zip_code]'")){ 
            echo "<p>Didn't work, try again!</p>"; 
            } 
           else{ 
             while($rowt = mysql_fetch_array($rt)) { 
              //output results 
              echo "<tr><td>$rowt[Agency]</td><td>$rowt[Telephone]</td><td>$rowt[Hours]</td><td>$rowt[street_address]</td><td>$rowt[city]</td><td>$rowt[state]</td><td>$rowt[zip]</td><td>$rowt[website]</td><td>"; 
              echo "</td></tr>\n"; 
             } 
            } 
          } 
          echo "</table>\n<br />\n"; 
         } 
        } 
       } 
      } 
     } 
    ?> 


</div> 

Answer 1

您提交的部分，你做一个正则表达式来检查类的存在，但你有没有进一步...

你需要更新你的查询：

$query = "SELECT * FROM zip WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude"; 

真的不知道如何更新查询，因为我无法推断出你的食物和zip表之间的关系。