restricting or defining search

[tommr]

I have a search code for my site and I want to narrow the scope of the search with a drop down that will allow users to pick a state, ( the sql table includes a state field ) or set the script so that i will only search for results for a particular state.

In the sql select I have tried every way of using WHERE venue_state='NY' but it will not work.

I would really rather allow the users to select a state from a dropdown and then enter the search term - city, zip or venue name.

Thank you for looking

 

<?php
include('config.db.php');
$find  = trim($_GET['find']);
$field = $_GET['field'];


if($find && $field) { // we have search form submitted
   // check for values to prevent sql injection
   $valid_fields = array("venue_zip", "venue_city" , "show_name");
   if(!in_array($field, $valid_fields)) die("Error: Invalid field!");
   
   $rpp = 10; // results per page
   $adjacents = 4;
   
   $page = intval($_GET["page"]);
   if(!$page) $page = 1;
   
   $reload = $_SERVER['PHP_SELF'] . "?find=" . urlencode($find) . "&field=" . urlencode($field);
   
   echo "<h4>Search Results for $find</h4>\n";
   
   
   $find = addslashes($find);
     $result = mysql_query("SELECT *, DATE_FORMAT(`start_date`, '%b %e, %Y') AS s_date FROM craft_shows WHERE $field LIKE '%$find%'");
   if(mysql_num_rows($result) == 0) {
      echo "<p>0 matches found.</p>";
   } else {
      echo "<table class='table7' cellpadding='2'>";
      echo "<tr><td> </td><td><strong>Date</strong></td><td><strong>Show Name</strong></td><td><strong>City</strong></td><td><strong>Attendance</strong></td></tr>";
      echo "<tr><td colspan='5'><hr class=\"hr2\"></td></tr>";
      
      // count total number of appropriate listings:
       $tcount = mysql_num_rows($result);
      
      // count number of pages:
      $tpages = ($tcount) ? ceil($tcount/$rpp) : 1; // total pages, last page number
      
      $count = 0;
      $i = ($page-1)*$rpp;
      while(($count<$rpp) && ($i<$tcount)) {
         mysql_data_seek($result,$i);
         $row = mysql_fetch_array($result);
         $id = $row['id'];
         echo "<tr><td>";
         echo "<a href=\"/show_submits/show_detail.php?id=$id\">Details</a>";
         echo "</td><td>";
         echo $row['s_date'];
         echo "</td><td>";
         echo $row['show_name'];
         echo "</td><td>";
         echo $row['venue_city'];
         echo "</td><td>";
         echo $row['venue_state'];
         echo "</td></tr>";
echo "<tr><td colspan='5'><hr class=\"hr3\"></td></tr>";
         $i++;
         $count++;
      }

      echo "</table><br>";
      
function paginate_one($reload, $page, $tpages) {
   
   $firstlabel = "First";
   $prevlabel  = "Prev";
   $nextlabel  = "Next";
   $lastlabel  = "Last";
   
   $out = "<div class=\"pagin\">\n";
   
   // first
   if($page>1) {
      $out.= "<a href=\"" . $reload . "\">" . $firstlabel . "</a>\n";
   }
   else {
      $out.= "<span>" . $firstlabel . "</span>\n";
   }
   
   // previous
   if($page==1) {
      $out.= "<span>" . $prevlabel . "</span>\n";
   }
   elseif($page==2) {
      $out.= "<a href=\"" . $reload . "\">" . $prevlabel . "</a>\n";
   }
   else {
      $out.= "<a href=\"" . $reload . "&page=" . ($page-1) . "\">" . $prevlabel . "</a>\n";
   }
   
   // current
   $out.= "<span class=\"current\">Page " . $page . " of " . $tpages . "</span>\n";
   
   // next
   if($page<$tpages) {
      $out.= "<a href=\"" . $reload . "&page=" .($page+1) . "\">" . $nextlabel . "</a>\n";
   }
   else {
      $out.= "<span>" . $nextlabel . "</span>\n";
   }
   
   // last
   if($page<$tpages) {
      $out.= "<a href=\"" . $reload . "&page=" . $tpages . "\">" . $lastlabel . "</a>\n";
   }
   else {
      $out.= "<span>" . $lastlabel . "</span>\n";
   }
   
   $out.= "</div>";
   
   return $out;
}
   echo paginate_one($reload, $page, $tpages, $adjacents);
   }
}
?>

[Pikachu2000]

I've added them for you this time, but when posting code please enclose it in the forum's

 . . . 

BBCode tags.

[tommr]

sorry about the code brackets.

Also I should have included the form.

 

<table class="search-shows">
<tr><td>
<form name="search" method="get" action="<?php $PHP_SELF?>">
<select name="state"  VALUE="venue_state">
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DE">Delaware</option>
<option value="DC">District of Columbia</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="TX">Texas</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</select>

<input type="text" name="find"  value="Search for ..." onfocus="if
(this.value==this.defaultValue) this.value='';" class="c-imput"/>  <Select NAME="field" class="c-select"> 
<Option VALUE="venue_city">  City  </option> 
<Option VALUE="venue_zip">  Zip Code  </option> 
<Option VALUE="show_name">  Show Name  </option> 
</Select>  
<input type="hidden" name="searching" value="yes" />
<input type="submit"  value=">>" class="c-button" />
</form>
</td></tr>
</table>

[BlueSkyIS]

What do you mean by "In the sql select I have tried every way of using WHERE venue_state='NY' but it will not work."?

 

What's not working? Getting any errors? I suggest that you make sure your queries run like this:

 

$sql = "SELECT *, DATE_FORMAT(`start_date`, '%b %e, %Y') AS s_date FROM craft_shows WHERE $field LIKE '%$find%'";
$result = mysql_query($sql) or die(mysql_error(). " IN $sql");

 

 

also: remove <?php $PHP_SELF?>

 

that is not a set value, so it is blank. you probably meant $_SERVER['PHP_SELF'], but that shouldn't be used. either use the actual script name, or leave the action value empty.

[tommr]

The script works fine the way I posted it, without

WHERE venue_state='NY'

but I wanted to restrict the search to a single state, such as ny.  I want the search to look only in rows that include NY.

I tried to use WHERE venue_state='NY' in the query but since there is already an instance of WHERE there seems to be a conflict.

[BlueSkyIS]

if there is already a WHERE, add an AND

 

... WHERE $field LIKE '%$find%' AND venue_state = 'NY'

[tommr]

Ok then!

That is a good thing to know.

I really appreciate it.

Thanks for the help