I have a DB of homes for sale. It has columns for Zip, Bedrooms, Baths, and State etc. I also have a form where users enter this info, some of which is optional. My form is below:
Code:
<form id="form1" name="form1" method="post" action="inner2.php"> <table width="180" border="0" cellpadding="5">
<tr>
<td>
<input name="city" type="text" class="textfield" value="City" onclick="this.value='';" onfocus="this.select()" onblur="this.value=!this.value?'City':this.value;"/>
</td>
<td> <select name="state" class="dropdown" >
<option value="State">State</option>
<option value="CA">CA</option>
<option value="OK">OK</option>
<option value="OR">OR</option>
</select>
</td>
</tr>
<tr>
<td>
<select name="distance" class="dropdown">
<option>Within 10 miles</option>
<option>Within 30 miles</option>
<option>Within 50 miles</option>
<option>Within 100 miles</option>
<option>Within 250 miles</option>
</select>
</td>
<td> <input name="zip" type="text" class="textfield" onclick="this.value='';" onfocus="this.select()" onblur="this.value=!this.value?'Zip':this.value;" value="Zip"/></td>
</tr>
</table>
<table width="180" border="0" cellpadding="5" style="margin-top:70px;">
<tr>
<td>Beds</td>
<td>Baths</td>
</tr>
<tr>
<td><select name="bedrooms" class="dropdown">
<option value="any">Any</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select></td>
<td><select name="baths" class="dropdown">
<option value="any">Any</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select></td>
</tr>
<tr>
<td>Min sq ft.</td>
<td>Property Type</td>
</tr>
<tr>
<td><select name="sq_ft1" class="dropdown">
<option>Any</option>
<option>500 sq ft</option>
<option>800 sq ft</option>
<option>1000 sq ft</option>
<option>1200 sq ft</option>
</select></td>
<td><select name="property_type" class="dropdown">
<option>Select One</option>
<option>Single Wide</option>
<option>Double Wide</option>
<option>Triple Wide</option>
<option>Your Momma Wide</option>
</select></td>
</tr>
</table>
<table width="180" border="0" cellpadding="5" style="margin-top:60px;">
<tr>
<td>Price Range</td>
<td> </td>
</tr>
<tr>
<td><select name="price_range_min" class="dropdown" >
<option>Min</option>
<option>$1000</option>
<option>$5000</option>
<option>$10000</option>
<option>$15000</option>
<option>$20000</option>
<option>$30000</option>
<option>$40000</option>
</select></td>
<td><select name="price_range_max" class="dropdown" >
<option>Max</option>
<option>$5000</option>
<option>$10000</option>
<option>$15000</option>
<option>$20000</option>
<option>$30000</option>
<option>$60000</option>
<option>$80000</option>
</select></td>
</tr>
<tr>
<td>Min sq ft.</td>
<td> </td>
</tr>
<tr>
<td><select name="sq_ft2_min" class="dropdown">
<option>Any</option>
<option>Within 30 miles</option>
<option>Within 50 miles</option>
<option>Within 100 miles</option>
<option>Within 250 miles</option>
</select></td>
<td><select name="sq_ft2_max" class="dropdown">
<option>Max</option>
<option>Single Wide</option>
<option>Double Wide</option>
<option>Triple Wide</option>
<option>Your Momma Wide</option>
</select></td>
</tr>
</table>
<table width="261" border="0" cellpadding="5" style="margin-top:40px;">
<tr>
<td width="79">Enter ID #</td>
<td width="156"> <input name="home_id" type="text" class="textfield_id" onclick="this.value='';" onfocus="this.select()" onblur="this.value=!this.value?'':this.value;" value=""/></td>
</tr>
</table>
<table width="100%" border="0" cellpadding="5">
<tr>
<td width="68%"> </td>
<td width="32%"> </td>
</tr>
<tr>
<td><p align="right" style=" padding-bottom:15px; "><input type="reset" value="clear" style="text-decoration:underline; background-color:#000; color:#fff; font-weight:bold; border:none;" border="0" ></td>
<td><p align="right"><INPUT TYPE="image" SRC="images/search.png"
BORDER="0" ALT="SUBMIT!"></p> </td>
</tr>
</table>
</form>
Here is the PHP to handle the process.
PHP Code:
$city=$_POST['city'];
$state=$_POST['state'];
$bedrooms=$_POST['bedrooms'];
$baths=$_POST['baths'];
$zip=$_POST['zip'];
$con = mysql_connect("mysql.testing.com","testing","testing");
mysql_select_db("testing", $con);
$query = "SELECT * FROM test WHERE 1=1 ";
if($state != "State") $query .= "AND State = " .$state. " ";
if($bedrooms != "any") $query .= "AND Bedrooms = " .$bedrooms. " ";
if($baths != "any") $query .= "AND Baths = " .$baths. " ";
if($zip != "Zip") $query .= "AND Zip = " .$zip. " ";
$query .= ' ';
echo $query;
$result = mysql_query($query);
while($row = mysql_fetch_array($result) or die(mysql_error()))
{ ........
You'll notice I'm using a string builder. Everything works perfectly for bedrooms, baths, and zip, but when I select a state, I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/inner2.php on line 251
Unknown column 'CA' in 'where clause'
The resulting echo for $query when I select California is "SELECT * FROM test WHERE 1=1 AND State = CA "
I get the same error when I try to code for "City".
I've been stuck on this all week, so any help would be much appreciated. I don't understand how I got it to work so easily for bedrooms, baths, and zip, but not State. This is my first post, so please let me know if I did it wrong. Thanks!
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
