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

Dave.Phillips at alltel.com Dave.Phillips at alltel.com
Wed Jul 27 12:58:07 CDT 2005


I'm not a PHP developer, but I think you should make your query look like this:

SELECT * FROM cba_condo WHERE bedrooms >= 3 AND bathrooms >= 2 AND squarefeet >= 1200 AND (city = 'Biloxi' OR city = 'Diamondhead' OR city = 'Gulfport' OR city = 'Lakeshore')

You need to use OR and you need parenthesis around the city comparisons so that only one of them has to be true, but the rest of the statment must be true also.

I'll take a stab at the PHP (watch my syntax - I'm a CF developer):

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

That last statement is required because there will be an extra OR at the end of your query and if you add 1 = 0, that will always return false never giving you a recordset as a result of it.  Then, of course, the ending ')'.

Hope this helps.

Dave



-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org]On Behalf Of Jeremy Weiss
Sent: Wednesday, July 27, 2005 1:36 PM
To: Evolt
Subject: [thelist] [PHP/MySQL] building a select statement from a
mutilple select field


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>
          </select>

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?

TIA,
-jeremy
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 
******************************************************************************************
The information contained in this message, including attachments, may contain 
privileged or confidential information that is intended to be delivered only to the 
person identified above. If you are not the intended recipient, or the person 
responsible for delivering this message to the intended recipient, ALLTEL requests 
that you immediately notify the sender and asks that you do not read the message or its 
attachments, and that you delete them without copying or sending them to anyone else. 



More information about the thelist mailing list