?
Solved

trying to create a search function for my real estate program?

Posted on 2006-05-07
14
Medium Priority
?
185 Views
Last Modified: 2008-02-26
I'm having some major problems with my logic on my search function. For example,

 Here is the form if there is text after a colon thats text from a drop down list.

Search For A Specific House
City  
State  
Zip  
Number of Bedrooms:   any 1 2 3 4 5  
Number Of Bathrooms:   any 1 2 3 4 5  
Square Footage:  Min:  any 400 500 600 700 800 900 1000 1500 2000 2500 3000 3500 4000 4500 5000 5500 Max:  any 400 500 600 700 800 900 1000 1500 2000 2500 3000 3500 4000 4500 5000 5500  
Garage:   any yes no  
Pool:   any yes no  
Style:   modern spanish victorian colonial bungalow ranchstyle  
Price Range

they fill in all or any of this form above and click submit.
so now here is my query problems.............

            $result = mysql_query("SELECT* FROM house WHERE city='$city' AND zipcode='$zip' AND garage='$garage' ");
so this was just my first test with just zip,city,and garage. The first problem i'm noticing is lets say zip,city were matches in the database but they left the garage part blank therefore that variable is null or blank or something and when it looks for all these together because garage was left blank it displays zero results when i would have liked to show at least the things that matched city and zip. So, I figured what if instead i "OR" everything. The problem there is it shows way too much example would be they put in city as san jose and filled in other stuff like garage etc... this would return other cities houses because other parts match and it is or'd.   How do I create a decent search function it does not need to be to technical at all just sort of decent at returning results that match. Here is the columns in my phpadmin tables. any ideas will be very helpfull!



      address  |city  |zipcode  |sqrfootage  |bed  |bath  |garage|  pool  |fireplace|  style | realtor|  askingprice  |picture  
      2388 cherry ave san jose 95125 1000 2 1 no no no modern johny five 650,000 http://www.southbaybeagles.com/house/h1.jpg 
      1156 lincoln san jose 95050 900 4 2 yes no no modern   700,000 http://www.southbaybeagles.com/house/h2.jpg 
      4567 harry wy santa clara 95056 1200 3 2 yes no yes spanish johny 750,000 http://www.southbaybeagles.com/house/h3.jpg 
      2323 potter ln cambell 94123 800 2 1 no no no ranch style   500,000 http://www.southbaybeagles.com/house/h4.jpg 
      23456 fread wy santa clara 95123 1300 3 2 yes no no spanish harry 700,500 http://www.southbaybeagles.com/house/h5.jpg 

0
Comment
Question by:tyweed420
12 Comments
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16626984
A couple points:

1) For the blank issue, you can create your SQL like this:
$sql = "SELECT * FROM house WHERE";
$comma = false;
if ($city != '')
{
  $sql .= " city = '$city'";
  $comma = true;
}
if ($zip != '')
{
  if ($comma)
    $sql .= ',';
  $sql .= " zip = '$zip'";
  $comma = true;
}
if ($garage != '')
{
  if ($comma)
    $sql .= ',';
  $sql .= " garage = '$garage'";
}
// Execute the query
$result = mysql_query($sql);

Notice how that seperates the logics.  If one is blank it will not be added to the SQL.  Also you need commas in SQL, I use a simple boolean variable to tell wethere we need to add a comma before or not.

2) Do not use ORs.  Using the code above should be sufficient to solve most of your problems. Bare in mind I did not test the code above, I just typed it.  It requires that there was text input from at least one of the three textareas (you can test that real quick to make sure all three are not equal to the empty string).

Try that out, if you have any problems let me know!
Joe P
0
 
LVL 16

Accepted Solution

by:
dr_dedo earned 672 total points
ID: 16627000
here is a trick
<?

do if condition or switch case or anything to create each part of query, and save each element of the search query to an array eg

$where[]=(isset($_GET['garage']) && $_GET['garage'] !='any')? $where[]."garase='$_GET['garage']' ": '':
..........
.........
........ // u do a lot of testing and if conditions
$where[]='ddd=dddd';
$where[]='ddd=dddd';
$where[]='ddd=dddd';
$where[]='ddd=dddd';
$where_clause =(sizeof($where)>0)?' where ':' '; // here u check that there are some conditions built
$where_clause .= implode(' and ', $where); here u build ur where cluase
echo $where_clause;//print it for test

notice the use of $_GET, it will facilitate for your clients to bookmark their search
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16627013
Nice tricks dr dedo!
I like the $_GET bookmark trick and the implode on the array is pretty smart because you can implode them on the keyword AND or use commas. Cool.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

Expert Comment

by:gamebits
ID: 16627048
I would go with a serie of if statements

if($_POST['zipcode'] == ''){$c_zipcode = '';}else{$c_zipcode = "AND zipcode = '$zipcode'";}
if($_POST['garage'] == ''){$c_garage = '';}else{$c_garage = "AND garage = '$garage'";}

$resultID = mysql_query("SELECT *
                         FROM house
                         WHERE city = '$city'
                         $c_zipcode
                         $c_garage");

You add as many if as required as well as the corresponding variables in the query.

Gamebits
0
 
LVL 1

Assisted Solution

by:Chrisbzd
Chrisbzd earned 664 total points
ID: 16627220
As a side note you'll also want to make your queries take parameters instead of using the variable name $whatever.  If someone enters a city name that contains a single quote or other special sql characters, it will break your code. Parameters can also help protect your code against sql injection attacks.

Below is a link that will describe this better.

http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/mysql_execute.html
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16627405
Along the same lines:
mysql_real_escape_string($whatever)

That basically escapes single quotes, and anything that might mess up a sql query
0
 

Author Comment

by:tyweed420
ID: 16627519
BogoJoker I ended up adding AND to where you wanted me to put commas because it kept giving me errors with the commas. Are the commas a better way than using AND?

$sql = "SELECT * FROM house WHERE";
$comma = false;
if ($city != '')
{
  $sql .= " city = '$city'";
  $comma = true;
}
if ($zip != '')
{
  if ($comma)
    $sql .= 'AND';
  $sql .= " zipcode = '$zip'";
  $comma = true;
}
if ($garage != '')
{
  if ($comma)
    $sql .= 'AND';
  $sql .= " garage = '$garage'";
}
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16627553
Commands are basically the same thing as AND.  Probably safer to just use AND.
Did it work well for you?

Joe P
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16627558
Gamebits code is more concise.  Mine was borderline explanitory.  His is efficient, short, fast code.  Getting a working version, then try to use his method to shorten the code (saving the working method).

Joe P
0
 

Author Comment

by:tyweed420
ID: 16627739
I'm having a problem with the squarefootage dropdown menus. There are two drop downs a min value and max value. Once you give me a min and max then i search for sqr footages between these values. So after doing some research i found that i can use betwwen keword however i'm getting errors

if ($squareMin != '') <== if min box  is not empty..........
{
  if ($comma)
  $sql .= 'AND';

  if($squareMax != '') <== if max box is not empty
  {
      $sql .= " sqrfootage BETWEEN $squareMin AND  $squareMAX ";  <==sqrfootage is the column in table and trying to search between min and max

  }
  else
  {
      $sql .= " sqrfootage BETWEEN $squareMin";
  }

 // $sql .= " sqrfootage = '$squareMin'";
  $comma = true;
}


this is error  

SELECT * FROM house WHERE city = 'cambell'AND sqrfootage BETWEEN 400 AND Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


does a between have to be at end of query? can it between more AND search filters?   how can i add a search between two values of a column to my query?
0
 

Author Comment

by:tyweed420
ID: 16627749
figured it out i'm going to split between you 3 thanks guys!
0
 
LVL 17

Assisted Solution

by:BogoJoker
BogoJoker earned 664 total points
ID: 16627752
1) You forgot the space before the AND in that if ($comma) statement:
$sql .= 'AND';
Should be:
$sql .= ' AND';
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question