PHP MySQL multiple search query using option / select HTML form tags

Question

I'm trying to set up a basic MySQL LIKE search using two search fields. I wan't to have it so it's multiple optional search fields e.g. if(isset($_POST['city']) || isset($_POST['name'])) I'm not sure how to do it with a HTML<select name=""><option value="">... seeing as you can't define a name for each <select> field. So, first... How do you make a MySQL <select>search where each yields it's own result? Then, how would I properly query multiple search options? Below I'm using a "text box" + <select> search.

            <input type="text" name="type" />  
            <select name="location">
               <option value="Chicago">Chicago</option>
               <option value="New York">New York</option>
            </select>
            <input type="submit" value="Search" />

MySQL:

              $query = "SELECT 
              name, type, description, location, zip_code, phone_number
              FROM search
              WHERE type LIKE '%$type%'
              OR location LIKE 'Chicago'
              OR location LIKE 'New York'
              ";

I know I'm doing something wrong on the OR clauses. I"m not totally sure how to properly do the <option> / <select> HTML form tag search query.

What are you trying to do? Are you trying to populate your <select> with values from MySQL, or are you trying to look up MySQL-rows based on values from a multiple <select>? — Ivar Bonsaksen
– Ivar Bonsaksen, Commented Sep 6, 2011 at 7:02
MySQL-rows based on values from a multiple <select>. Just a search based on multiple select. — Graham
– Graham, Commented Sep 6, 2011 at 7:05

Your Common Sense · Accepted Answer · 2011-09-06 08:18:06Z

4

HTML

<select multiple name="location[]" size="2">

PHP

$w     = array();
$where = '';
foreach ($_POST['location'] as $loc){
  $loc = mysql_real_escape_string($loc);
  $w[] = "location = '$loc'";
}
if (!empty($_POST['type'])){
  $w[] = "type LIKE '%".mysql_real_escape_string($_POST['type'])."%'";
}
if ($w) $where = implode(' OR ',$w);

edited Sep 6, 2011 at 8:18

answered Sep 6, 2011 at 7:11

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

3 Comments

Graham Over a year ago

Does this make the result based on the <option>? Is the <option> value returned in the from of an array() from $wh[]? Do <option> values typically get returned this way? Thanks for the answer.

Your Common Sense Over a year ago

1. just look at HTML part carefully. 2. var_dump() every variable in this code to see it's contents. 3. think

Vytautas Over a year ago

why are you using like search location is hardcoded city names and your version can select some unwanted results

Vytautas · Accepted Answer · 2011-09-06 08:27:44Z

0

I am not sure what you want but try this:

PHP:

// mysql escape
$locations = array_map("mysql_real_escape_string", $_POST['location']);
// add locations to query string
$locations = implode("','",$location);

$query = "SELECT 
              name, type, description, location, zip_code, phone_number
              FROM search
              WHERE location IN ('$locations')
              ";
if (isset($_POST['type']) && !empty($_POST['type'])){
  $query .= " or type LIKE '%".mysql_real_escape_string($_POST['type'])."%'";
}

edited Sep 6, 2011 at 8:27

answered Sep 6, 2011 at 7:01

Vytautas

3,5391 gold badge30 silver badges43 bronze badges

Collectives™ on Stack Overflow

PHP MySQL multiple search query using option / select HTML form tags

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related