[thelist] [PHP/MySQL] building a select statement from a mutilple select field

Jeremy Weiss jweiss at cox-internet.com
Wed Jul 27 12:36:25 CDT 2005

I'm working on a search form for a real estate website. I've got most of it working fine but I've run into a situation where I'm not really sure what to do.

I've got a multiple select form box that lists several cities so that people can choose the area where they want.

<select name="city[]" size="6" multiple>
            <option value="">-- Any city --</option>
            <option value="Biloxi">Biloxi</option>
            <option value="Diamondhead">Diamondhead</option>
            <option value="Gulfport">Gulfport</option>
            <option value="Lakeshore">Lakeshore</option>
            <option value="Leaf">Leaf</option>
            <option value="Long Beach">Long Beach</option>
            <option value="Ocean Springs">Ocean Springs</option>
            <option value="Pascagoula">Pascagoula</option>
            <option value="Pass Christian">Pass Christian</option>
            <option value="Waveland">Waveland</option>

Here's the php that I'm thinking will almost work:

  // Cycle through the array of cities
  $sql_cities = "";  // set to nothing for when they select any city
  foreach ($city as $value) {
    $sql_cities .= "AND city = '$value' ";

The problem is, when multiple cities are selected, my sql statement comes out like:
SELECT * FROM cba_condo WHERE bedrooms >= 3 AND bathrooms >= 2 AND squarefeet >= 1200 AND city = 'Biloxi' AND city = 'Diamondhead' AND city = 'Gulfport' AND city = 'Lakeshore' ORDERBY price Desc;

which obviously won't work. So I thought about replacing AND with OR, but that doesn't seem correct either. Thus I'm thinking that I must be going about this wrong. Anyone care to tell the right way to pull this off?


More information about the thelist mailing list