How to combine a search box and dropdown to output results (php, mysql)

Question

I have created an HTML form with a search box and a drop down list of 50 states in the USA. What I want is to let the user type in a keyword in the search box (email, subject, username, etc.) and select a state from the drop down. By selecting a state from the drop down, I want my PHP code to find the keyword from the search box only in that selected state. For example as a user, I would like to find "soccer" in the state of Washington and see a list of results.

In other words, how would I display results only in the selected state combined with my search form?

Here is a bit of my PHP code to give you all an idea of what I'm trying to accomplish.

// this is pulling the text that was typed in the html search box
$searchbox = $_POST['searchbox'];

// this is pulling the state from my HTML drop down menu
$state = $_POST['state'];

$searchboxresult = mysql_query("
  SELECT *
  FROM   table
  WHERE (ID      LIKE '%".$_POST['searchbox']."%')
     OR (stateID LIKE '%".$_POST['searchbox']."%')
     OR (city    LIKE '%".$_POST['searchbox']."%')
     OR (subject LIKE '%".$_POST['searchbox']."%')
     OR (email   LIKE '%".$_POST['searchbox']."%')
     OR (posting LIKE '%".$_POST['searchbox']."%')
     OR (skill   LIKE '%".$_POST['searchbox']."%')
     OR (event   LIKE '%".$_POST['searchbox']."%')
     OR (days    LIKE '%".$_POST['searchbox']."%')
     OR (url     LIKE '%".$_POST['searchbox']."%')
");

// and

$statedropdownresult = mysql_query("
  SELECT * FROM table WHERE stateID LIKE '%$state%'
");

Then I have a while loop that searches for everything and outputs the data. But I can't get the states drop down to limit what the user is searching for. I can only get one or the other mysql_query to work in combination with my while loop, not both. For some reason, combining the queries into one using AND doesn't seem to work either. Any ideas?

I know that the code is sloppy and there are security issues, however I will be going through to do a cleanup later on. If you need more details or clarification on something, I would be happy elaborate more!

I appreciate you acknowledge it in your question, but for the benefit of other readers: this code is vulnerable to SQL injection. One really should be using prepared statements, into which one passes one's variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables. — eggyal
– eggyal, Commented May 10, 2012 at 8:37

user1163513 · Accepted Answer · 2012-05-10 04:43:24Z

1

You could try something like this:-

$sql ="SELECT * FROM table WHERE 1";

if($_POST['searchbox'] != ''){
     $sql =" and (ID LIKE '%".$_POST['searchbox']."%') OR (stateID LIKE              
     '%".$_POST['searchbox']."%') OR (city LIKE '%".$_POST['searchbox']."%') OR   
     (subject LIKE '%".$_POST['searchbox']."%') OR (email LIKE  
     '%".$_POST['searchbox']."%') OR (posting LIKE '%".$_POST['searchbox']."%') OR  
     (skill LIKE '%".$_POST['searchbox']."%') OR (event LIKE  
     '%".$_POST['searchbox']."%') OR (days LIKE '%".$_POST['searchbox']."%') OR (url  
     LIKE '%".$_POST['searchbox']."%')";
}
if($state != ''){
    $sql .= " and stateID LIKE '%".$state."%'";
}

  mysql_query($sql);

Hope this helps.

answered May 10, 2012 at 4:43

user1163513

4,2177 gold badges27 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sonarbiscuit Over a year ago

Thank you for the help! I am a bit confused on why you used 1 at $sql ="SELECT * FROM table WHERE 1"; Is this just a placeholder?

Collectives™ on Stack Overflow

How to combine a search box and dropdown to output results (php, mysql)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related