MYSQL Searching by Zip Code

Question

I am having a tough time searching by zip, which wasn't a problem before and will most likely be an easy problem for a MySQL / PHP programmer.

I have a database full of house listings that I need to search by various fields. I have no problem searching by city, until I search by zip code (which like I said used to work).

Anyways here is some of the code that I am working with as I hope someone will be able to take a quick glance and see the problem.

---

$sql = "SELECT DISTINCT

mls_listings.c1, mls_listings_images.c1, mls_listings.c2, mls_listings.c3, mls_listings_images.c3 as image, mls_listings_images.c5, mls_listings.c4, mls_listings.c5, mls_listings.c6, mls_listings.c7, mls_listings.c8, mls_listings.c9 FROM mls_listings, mls_listings_images WHERE (mls_listings.c1=mls_listings_images.c1) AND (mls_listings_images.c5 = '1') AND (mls_listings_images.c1 LIKE '%{$_GET['uniqueid']}%') AND (mls_listings.c3 IN('{$_GET['redondobeach']}','{$_GET['santamonica']}','{$_GET['specificcity']}', '{$_GET['hermosabeach']}','{$_GET['elsegundo']}','{$_GET['manhattanbeach']}', '{$_GET['marinadelrey']}','{$_GET['palosverdes']}','{$_GET['playadelrey']}', '{$_GET['playavista']}','{$_GET['torrance']}','{$_GET['venice']}', '{$_GET['westchester']}'))

AND (mls_listings.c7 LIKE '%{$_GET['zipwestla']}%')

AND (mls_listings.c5 LIKE '%{$_GET['beds']}%')

AND (mls_listings.c6 LIKE '%{$_GET['baths']}%')

AND (mls_listings.c4 >= '{$_GET['minprice']}'

AND mls_listings.c4 <= '{$_GET['maxprice']}') ORDER BY mls_listings.c4";

---

***Sorry, I couldn't get the pre/code to work on the last half of the code.

Also --

mls_listings.c7 holds the zip codes

mls_listings.c3 holds the city names (ie. Ventura) **Searching by city works

Here is an example of the checkboxes inside the form searching by city and zip:

---

<input type='checkbox' value='Torrance' name='torrance' class="results1">
Torrance<br>
<input type='checkbox' value='Venice' name='venice' class="results1">
Venice<br>
<input type='checkbox' value='90403' name='zipwestla' class="results1">
West LA<br>
<input type='checkbox' value='West Chester' name='westchester' class="results1">
Westchester<br>

---

I would really appreciate some help / advice or even some links to guide me in the right direction.

Please let me know if you need more info. from me as well.

Thank you.

Answer 1

Before I begin: You should NEVER be putting user received values straight into your SQL.

Let me help by making things a little clearer:

$sql = "
SELECT DISTINCT 
    mls_listings.c1, 
    mls_listings_images.c1, 
    mls_listings.c2, 
    mls_listings.c3, 
    mls_listings_images.c3 as image, 
    mls_listings_images.c5, 
    mls_listings.c4, 
    mls_listings.c5, 
    mls_listings.c6, 
    mls_listings.c7, 
    mls_listings.c8, 
    mls_listings.c9 
FROM 
    mls_listings, 
    mls_listings_images 
WHERE 
    (mls_listings.c1 = mls_listings_images.c1) AND 
    (mls_listings_images.c5 = '1') AND 
    (mls_listings_images.c1 LIKE '%{$_GET['uniqueid']}%') AND 
    (mls_listings.c3 IN (
        '{$_GET['redondobeach']}',
        '{$_GET['santamonica']}',
        '{$_GET['specificcity']}',
        '{$_GET['hermosabeach']}',
        '{$_GET['elsegundo']}',
        '{$_GET['manhattanbeach']}',
        '{$_GET['marinadelrey']}',
        '{$_GET['palosverdes']}',
        '{$_GET['playadelrey']}',
        '{$_GET['playavista']}',
        '{$_GET['torrance']}',
        '{$_GET['venice']}',
        '{$_GET['westchester']}')
    ) AND 
    (mls_listings.c7 LIKE '%{$_GET['zipwestla']}%') AND 
    (mls_listings.c5 LIKE '%{$_GET['beds']}%') AND 
    (mls_listings.c6 LIKE '%{$_GET['baths']}%') AND 
    (
        mls_listings.c4 >= '{$_GET['minprice']}' AND 
        mls_listings.c4 <= '{$_GET['maxprice']}'
    ) 
ORDER BY mls_listings.c4";

Any checkbox that is unticked will return nothing back to PHP. If you have errors turned on you'll received a notice about undefined values for all these unticked boxes. The PHP interpreter will give an empty string when you try to use them.

Now this works when you are using cities because the (ml_listings.c3 IN (....)) condition will find a match and the (mls_listings.c7 LIKE '%{$_GET['zipwestla']}%') condition matches everything because it devolves to (mls_listings.c7 LIKE '%%').

If you're just searching with zipwestla ticked (and I'm guessing this is what you are trying) then the (ml_listings.c3 IN (....)) condition matches nothing, it devolves to (ml_listings.c3 IN ('','','', '','','', '','','', '','','', '')) so unless an entry in ml_listings.c3 is a blank string there will be no matches.

You really need to go back and rework this, but since you're asking here's my suggestion:

SELECT DISTINCT 
    mls_listings.c1, 
    mls_listings_images.c1, 
    mls_listings.c2, 
    mls_listings.c3, 
    mls_listings_images.c3 as image, 
    mls_listings_images.c5, 
    mls_listings.c4, 
    mls_listings.c5, 
    mls_listings.c6, 
    mls_listings.c7, 
    mls_listings.c8, 
    mls_listings.c9 
FROM 
    mls_listings, 
    mls_listings_images 
WHERE 
    (mls_listings.c1 = mls_listings_images.c1) AND 
    (mls_listings_images.c5 = '1') AND 
    (mls_listings_images.c1 LIKE '%{$_GET['uniqueid']}%') AND 
    ((mls_listings.c3 IN (
        '{$_GET['redondobeach']}',
        '{$_GET['santamonica']}',
        '{$_GET['specificcity']}',
        '{$_GET['hermosabeach']}',
        '{$_GET['elsegundo']}',
        '{$_GET['manhattanbeach']}',
        '{$_GET['marinadelrey']}',
        '{$_GET['palosverdes']}',
        '{$_GET['playadelrey']}',
        '{$_GET['playavista']}',
        '{$_GET['torrance']}',
        '{$_GET['venice']}',
        '{$_GET['westchester']}')
    ) OR 
    (mls_listings.c7 LIKE '%{$_GET['zipwestla']}%')) AND 
    (mls_listings.c5 LIKE '%{$_GET['beds']}%') AND 
    (mls_listings.c6 LIKE '%{$_GET['baths']}%') AND 
    (
        mls_listings.c4 >= '{$_GET['minprice']}' AND 
        mls_listings.c4 <= '{$_GET['maxprice']}'
) 
ORDER BY mls_listings.c4

Bear in mind that if nothing is ticked that would return everything in the database since it would happily match all rows in mls_listings.c7. For what it looks like you're dong that might even be useful behaviour.

But please, pretty please, pretty please with a cherry on top, go look up bound variables in the manual.