#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Error with search query from form


    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>&nbsp;</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>&nbsp;</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%">&nbsp;</td>
        <td width="32%">&nbsp;</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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,233
    Rep Power
    593
    Echo the resulting string '$query' to make sure it is what you expect. We can't be much help without seeing the resulting string.

    P.S. I suggest you consider rewriting your database code to use PDO rather than the depreciated MySQL extensions.

    Comments on this post

    • ccmweb agrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by gw1500se
    Echo the resulting string '$query' to make sure it is what you expect. We can't be much help without seeing the resulting string.

    P.S. I suggest you consider rewriting your database code to use PDO rather than the depreciated MySQL extensions.
    The resulting echo for $query when I select California is ' SELECT * FROM test WHERE 1=1 AND State = CA '

    I already have a column named 'State', and there is definitely a few homes listed as 'CA'.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by gw1500se
    Echo the resulting string '$query' to make sure it is what you expect. We can't be much help without seeing the resulting string.

    P.S. I suggest you consider rewriting your database code to use PDO rather than the depreciated MySQL extensions.
    I figured it out! I just forgot ' ' marks around the state.
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2002
    Location
    Seattle, U.S.A.
    Posts
    712
    Rep Power
    12
    Originally Posted by ccmweb
    The resulting echo for $query when I select California is ' SELECT * FROM test WHERE 1=1 AND State = CA '

    I already have a column named 'State', and there is definitely a few homes listed as 'CA'.
    It's because you don't have quotes around CA.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    You missed the post above you, but thank you anyways. I can't believe those little marks ruined my week.
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2002
    Location
    Seattle, U.S.A.
    Posts
    712
    Rep Power
    12
    Oops sorry. Glad you got t fixed!

IMN logo majestic logo threadwatch logo seochat tools logo