Search and Results Page

Question

I'd like to get some help with my search script. A little about the site: I have registered users and when they register they select their user type (ie Nanny, Homecare, Daycare). Right now I have a search bar and visitors can input their zip code and the results page shows everything in the database with that zip code. Current form:

<form id="form1" name="form1" method="get" action="results.php">
    <p>
      <label>Search for<input type="text" name="Search" id="Search"  /></label>
      <label>
        <select name="Field" id="Field">
          <option value="Zip">Zip</option>   
        </select>
      </label>
      <input type="submit" name="button" id="button" value="Search" />
   </p>

What I would like to do is to have it where users enter their zip code and select Nanny, Homecare, or Daycare from a drop down and the results shows only that type in the search results instead on everything within that zip.

How I want the form to look:

<form id="form1" name="form1" method="get" action="results.php">
    <p>
      <label>Search for<input type="text" name="Search" id="Search"  value="Zip" /></label>
      <label>
        <select name="Field" id="Field">
          <option value="Nanny">Nanny</option>
          <option value="Homecare">Homecare</option>
          <option value="Childcare">Childcare</option>   
        </select>
      </label>
      <input type="submit" name="button" id="button" value="Search" />
    </p>
</form>

The php:

<?php
require_once('scripts/_config.php');
if(isset($_GET['Field'])){
$searchQ1=mysql_query("SELECT `First_Name`,`Last_Name`,`Nanny`,`Homecare`,`Childcare`,`City`,`State`,`Zip`,`userID` FROM `sys_profile` WHERE `".$_GET['Field']."` LIKE '".$_GET['Search']."';");
}else{
$searchQ1=mysql_query("SELECT `First_Name`,`Last_Name`,`Nanny`,`Homecare`,`Childcare`,`City`,`State`,`Zip`,`userID` FROM `sys_profile` WHERE `userID`!=0;");
}
?>

How do I change the php so that the search results displays only nannies within the entered zip, or childcare in the entered zip, etc? Right now the php shows everything in that zip and when I change the form to the second example it still doesn't work because I'm not sure how to adjust the search query.

I think you have an error in your data model. How does your tables look like? Which fields are in there? — Stefan S, Jun 24 '12 at 17:46
u can do this using jquery very easily.. its dynamic dropdown... in php try http://www.coremediadesign.co.uk/learn_web_design/free_web_design_tutorials/tutorial/array_drop_down_menu_php.html — sree, Jun 24 '12 at 17:48
+1 because well formed question deserve upvotes. While it's not an answer to your question, please look into preventing SQL Injection attacks and use _mysqli_ or _PDO_ since the _mysql_ class of functions is deprecated. — Herbert, Jun 24 '12 at 17:51
@StefanS Everything listed in the query is a field in my database. The search works fine I'm not getting any errors. Its just right now its a simple search with a text field and a 'zip' option in the drop down. What I want is to change it so that instead of having the 'zip' drop down it has 'nanny' 'homecare' 'childcare' and when the user types their zip in the text field and selects one of those three options only that option shows. Right now if you type in the zip everything with that zip will show. Not sure how to make it so that the entered zip grabs only nannies or only homecare etc. — Jo Anne, Jun 24 '12 at 17:57
this depends on your data model, please check my answer below. what types are the nany, homecare and childcare fields in the db? — Stefan S, Jun 24 '12 at 17:59

Stefan S · Accepted Answer · 2012-06-24T18:08:09.880

0

You have to include the second constraint with an AND inside the query. Compare the Zip, that is contained in the $_GET['Search'] with the Zip file in the DB, and the selected value form the dropdown with the user type in the db.

How does your DB look like? Especially, how is the user type information saved? Guessing from your posted query, these are boolean values (Nanny, Homecare, Childcare)?

EDIT: When Nanny, Homecare and Childcare are boolean values, a possible query would be:

$sql = "SELECT First_Name,Last_Name, City, State, userID FROM sys_profile WHERE Zip = " . $_GET['Search'] . " AND $_GET['Field'] = true";

So you check if the Zip matches the search string, AND if the selected drop down item has the value true.

edited Jun 24 '12 at 18:08

answered Jun 24 '12 at 17:56

Stefan S

650
2
10
23

Yes Nanny, Homecare, and Childcare are boolean values. When the registered user creates their profile they check one of those options indicating which one they are. Each one is a field in the database set to 0 if checked it switches to 1. – Jo Anne Jun 24 '12 at 18:02
I understand what you're saying. I'm just not sure how to write it. – Jo Anne Jun 24 '12 at 18:10
[Best way to prevent SQL Injection in PHP](http://stackoverflow.com/q/60174/911182) – Herbert Jun 24 '12 at 18:15
No problem. And if you plan to go into production environment, please read Herberts link, as your solution is absolutely not safe against SQL Injection. – Stefan S Jun 24 '12 at 18:27
@Herbert Im reading a bit about SQL injection and its above my knowledge base. I'm still relatively new to php. So based on my query what about it is bad? – Jo Anne Jun 24 '12 at 19:26
Anytime user input is inserted directly into a SQL query, the application is vulnerable to SQL injection. Specifically, the use of `$_GET` variables in your query string. You _should_ be using "prepared statements", but at a minimum, at least sanitize the input. See [mysqli_real_escape_string](http://php.net/manual/en/mysqli.real-escape-string.php). As I said in my first comment, mysql_* functions have been deprecated and should not be used in new projects.... – Herbert Jun 24 '12 at 20:56
1

... Learning [PDO](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) early will save a lot of headaches later on. I realize all this may seem a bit steep, but it's relatively easy to handle and well worth the effort. Learn to build safe applications from the start and you'll thank yourself later. – Herbert Jun 24 '12 at 20:59

Search and Results Page

1 Answers1