Php Real Estate Script

Hi, i am setting up a real estate database and i am wondering how to do 2 things...
In my index page I have a quick search form. I have the choises of
    Listing Type (Any, Rent, Sale)
     Region (Any, Region1, Region2, etc)
     Property Type (Any,House, Flat, etc)
     Minimum Price (Any, Price1, Price2, etc)
     Maximum Price (Any, Price1, Price2, etc)
a. How do i use the price field to check between the min and max ? To be more clear, i have a field in my table called price and lets say the user wants to check properties between 100,000 and 200,000 so i guess the minimum and the maximum have to be checked against the price field in the database. How do I do that?
b. How the Any choice in the dropdown works? What do i need to have extra in order when the user chooses any to give them all the results for the specific choice
dino_angelidesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Avinash ZalaWeb ExpertCommented:
0
john-formbyCommented:
Hi,

You would have the id of each listing, region and property stored in your properties table.  That way, when you submit the form, you can compare the values.  Please have a look at the code example below.  It uses BETWEEN to select the results in the price range.  I am using IN for other fields since I am using a small sample set.  With the region, I am guessing there will be a lot so I am using an IF statement to determine the range and then using BETWEEN again.  If a value for region is selected then the start and end region are assigned the same value.  I have included the MySQL table for reference.

Hope this helps,

John
page.php
========
<?php
$dbHost = "localhost";
$dbUser = "YOUR_USERNAME";
$dbPass = "YOUR_PASSWORD";
$dbName = "YOUR_DATABASE";
$db = mysql_connect($dbHost,$dbUser,$dbPass);
mysql_select_db($dbName,$db);
?>
<html>
<head>
<title>Property Search</title>
</head>
<body>
<?php
if(isset($_POST['submit'])) {
	foreach($_POST as $key=>$value) {
		$$key = mysql_real_escape_string($value);
	}
	if($region == 'any') {
		$regionstart = 1;
		$regionend = 4;
	} else {
		$regionstart = $region;
		$regionend = $region;
	}
	$query = mysql_query("SELECT * FROM properties WHERE price BETWEEN $price1 AND $price2 AND listing IN($listing) AND region BETWEEN $regionstart AND $regionend AND property IN($property)") or die(mysql_error());
	$numrows = mysql_num_rows($query);
	if($numrows <= 0) {
		echo 'No Results';
	} else {
		while($row = mysql_fetch_array($query)) {
			echo 'Listing: '.$row['listing'].', Region: '.$row['region'].', Property: '.$row['property'].', Price: '.$row['price'].'<br />';
		}
	}
}
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Listing Type: <select name="listing">
<option value="1,2">Any</option>
<option value="1">Rent</option>
<option value="2">Sale</option>
</select><br />
Region: <select name="region">
<option value="any">Any</option>
<option value="1">Region 1</option>
<option value="2">Region 2</option>
<option value="3">Region 3</option>
<option value="4">Region 4</option>
</select><br />
Property Type: <select name="property">
<option value="1,2">Any</option>
<option value="1">Rent</option>
<option value="2">Sale</option>
</select><br />
Minimum Price: <select name="price1">
<option value="0">Any</option>
<option value="100000">100,000</option>
<option value="200000">200,000</option>
<option value="300000">300,000</option>
</select><br />
Maximum Price: <select name="price2">
<option value="999999999">Any</option>
<option value="100000">100,000</option>
<option value="200000">200,000</option>
<option value="300000">300,000</option>
</select><br />
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>




MySQL Table: properties
=======================
CREATE TABLE `properties` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `listing` int(11) NOT NULL,
  `region` int(11) NOT NULL,
  `property` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `properties`
--

INSERT INTO `properties` (`id`, `listing`, `region`, `property`, `price`) VALUES
(1, 1, 2, 1, 250000),
(2, 1, 1, 1, 120000),
(3, 2, 2, 1, 160000),
(4, 2, 2, 2, 300000);

Open in new window

0
dino_angelidesAuthor Commented:
Hi John, i think it got a bit more complicated.....I am giving you the link of the sample site so you can check it out for any errors....
http://www.liteways.com/RealEstateOnline/index.php
when the user submits the search form I want the form to go to searchresults.php
also I am attaching the php code, your php code wiith the changes I made.....so you know all my regions are no more than 5, its just a small island here :)



<?php 
if(isset($_POST['search'])) { 
        foreach($_POST as $key=>$value) { 
                $$key = mysql_real_escape_string($value); 
        } 
        $query = mysql_query("SELECT * FROM listings WHERE price BETWEEN $minPrice AND $maxPrice AND listingType IN($listingType) AND region IN($region) AND propType IN($propType)") or die(mysql_error()); 
        $numrows = mysql_num_rows($query); 
        if($numrows <= 0) { 
                echo 'No Results'; 
        } else { 
                while($row = mysql_fetch_array($query)) { 
                        
						echo 'Listing: '.$row['listingType'].', Region: '.$row['region'].', Property: '.$row['propType'].', Price: '.$row['price'].'<br />'; 
                } 
        } 
} 
?>

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

john-formbyCommented:
Hi,

That is no problem.  I guess you have:

1.  Changed the form to point at searchresults.php
2.  The submit button name is search
3.  Your region select box any value is like 1,2,3,4,5
4.  You have removed the IF statement below:
                if($region == 'any') {
            $regionstart = 1;
            $regionend = 4;
      } else {
            $regionstart = $region;
            $regionend = $region;
      }

If not, please make these changes and now check.

If you have any problems, please post your entire code for the index and searchresults pages so I can investigate.

Thanks,

John
0
john-formbyCommented:
I just looked at your html source and can see your form tag is like:

<form id="QuickPropertySearchForm" name="QuickPropertySearchForm" method="get" action="">

It should be:

<form id="QuickPropertySearchForm" name="QuickPropertySearchForm" method="post" action="searchresults.php">

Thanks,

John
0
dino_angelidesAuthor Commented:
i am trying somethings before giving it for you to check out....I changed the form to POST but i have some questions......
the php code you provided earlier....where do i put it? to my index page? or to my searchresults page? Also in the searchresults.php i put the code into a form basically just to get the results the error or the success you have given me in your code...
0
john-formbyCommented:
The PHP code should go in the serachresults.php page
0
dino_angelidesAuthor Commented:
Ok here is my code....
note that, I have put the results to display in index page as you had it, also note that, on my drop downs, i have not used numbers on the Any selection I used the names because thats how they are in the database....
Hopefully now we can see whats going on
Also something else, when I have numbers instead of the names on the Any in drop down I was getting the error No Results, which I assumed was right, because there aren't any values like that in the database but when I changed it to the actual names stored in the database I get this error
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 'For Rent, For Sale) AND region IN(Nicosia,Larnaca,Limassol,Paphos,Ayia Napa) AND' at line 1
well, your code makes perfect sense but where is the mistake I cannot see it :)
 <form id="QuickPropertySearchForm" name="QuickPropertySearchForm" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
      <table width="100%" border="0">
        <tr>
          <td width="69%">&nbsp;</td>
          <td width="12%" height="25" class="style8"><span class="style81">Region:</span></td>
          <td height="20" colspan="2"><span class="style81">
            <select name="region" class="style2" id="region" size="">
              <option>Select Region</option>
              <option value="Nicosia,Larnaca,Limassol,Paphos,Ayia Napa">Any</option>
              <option value="Nicosia">Nicosia</option>
              <option value="Larnaca">Larnaca</option>
              <option value="Limassol">Limassol</option>
              <option value="Paphos">Paphos</option>
              <option value="Ayia Napa">Ayia Napa</option>
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="24">&nbsp;</td>
          <td height="25" class="style8">Listing Type</td>
          <td height="20" colspan="2"><span class="style81">
            <select name="listingType" class="style21" id="listingType">
              <option selected="selected">Please Select</option>
              <option value="For Rent, For Sale">Any</option>
              <option value="For Rent">For Rent</option>
              <option value="For Sale">For Sale</option>
              
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="24">&nbsp;</td>
          <td height="25" class="style8"><span class="style81">Property Type: 
            
          </span></td>
          <td height="20" colspan="2"><span class="style81">
            <select name="propType" class="style21" id="propType">
              <option value="">Select Property Type</option>
              <option value="Flat,House,Shop,Office,Plot,Land,Warehouse">Any</option>
              <option value="Flat">Flat</option>
              <option value="House">House</option>
              <option value="Shop">Shop</option>
              <option value="Office">Office</option>
              <option value="Plot">Plot</option>
              <option value="Land">Land</option>
              <option value="Warehouse">Warehouse</option>
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="23">&nbsp;</td>
          <td height="25" class="style8">Minimum Price (&#8364;):</td>
          <td height="20" colspan="2"><select name="minPrice" class="style21" id="minPrice">
            <option value="0" selected="selected">Any</option>
            <option value="10,000">10,000</option>
            <option value="20,000">20,000</option>
            <option value="30,000">30,000</option>
            <option value="40,000">40,000</option>
            <option value="50,000">50,000</option>
            <option value="60,000">60,000</option>
            <option value="70,000">70,000</option>
            <option value="80,000">80,000</option>
            <option value="90,000">90,000</option>
            <option value="100,000">100,000</option>
            <option value="110,000">110,000</option>
            <option value="120,000">120,000</option>
            <option value="130,000">130,000</option>
            <option value="140,000">140,000</option>
            <option value="150,000">150,000</option>
            <option value="160,000">160,000</option>
            <option value="170,000">170,000</option>
            <option value="180,000">180,000</option>
            <option value="190,000">190,000</option>
            <option value="200,000">200,000</option>
            <option value="220,000">220,000</option>
            <option value="240,000">240,000</option>
            <option value="260,000">260,000</option>
            <option value="280,000">280,000</option>
            <option value="300,000">300,000</option>
            <option value="320,000">320,000</option>
            <option value="340,000">340,000</option>
            <option value="360,000">360,000</option>
            <option value="380,000">380,000</option>
            <option value="400,000">400,000</option>
            <option value="420,000">420,000</option>
            <option value="440,000">440,000</option>
            <option value="460,000">460,000</option>
            <option value="480,000">480,000</option>
            <option value="500,000">500,000</option>
            <option value="550,000">550,000</option>
            <option value="600,000">600,000</option>
            <option value="650,000">650,000</option>
            <option value="700,000">700,000</option>
            <option value="40">750,000</option>
            <option value="750,000">800,000</option>
            <option value="900,000">900,000</option>
            <option value="1,000,000">1,000,000</option>
            <option value="1,500,000">1,500,000</option>
            <option value="2,000,000">2,000,000</option>
          </select></td>
        </tr>
        <tr>
          <td height="28">&nbsp;</td>
          <td height="25" class="style8">Maximum Price<span class="style81"> (&#8364;)</span>:</td>
          <td height="20" colspan="2"><select name="maxPrice" class="style21" id="maxPrice">
            <option value="2,000,000" selected="selected">Any</option>
            <option value="10,000">10,000</option>
            <option value="20,000">20,000</option>
            <option value="30,000">30,000</option>
            <option value="40,000">40,000</option>
            <option value="50,000">50,000</option>
            <option value="60,000">60,000</option>
            <option value="70,000">70,000</option>
            <option value="80,000">80,000</option>
            <option value="90,000">90,000</option>
            <option value="100,000">100,000</option>
            <option value="110,000">110,000</option>
            <option value="120,000">120,000</option>
            <option value="130,000">130,000</option>
            <option value="140,000">140,000</option>
            <option value="150,000">150,000</option>
            <option value="160,000">160,000</option>
            <option value="170,000">170,000</option>
            <option value="180,000">180,000</option>
            <option value="190,000">190,000</option>
            <option value="200,000">200,000</option>
            <option value="220,000">220,000</option>
            <option value="240,000">240,000</option>
            <option value="260,000">260,000</option>
            <option value="280,000">280,000</option>
            <option value="300,000">300,000</option>
            <option value="320,000">320,000</option>
            <option value="340,000">340,000</option>
            <option value="360,000">360,000</option>
            <option value="380,000">380,000</option>
            <option value="400,000">400,000</option>
            <option value="420,000">420,000</option>
            <option value="440,000">440,000</option>
            <option value="460,000">460,000</option>
            <option value="480,000">480,000</option>
            <option value="500,000">500,000</option>
            <option value="550,000">550,000</option>
            <option value="600,000">600,000</option>
            <option value="650,000">650,000</option>
            <option value="700,000">700,000</option>
            <option value="40">750,000</option>
            <option value="750,000">800,000</option>
            <option value="900,000">900,000</option>
            <option value="1,000,000">1,000,000</option>
            <option value="1,500,000">1,500,000</option>
            <option value="2,000,000">2,000,000</option>
          </select></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td width="12%" rowspan="2" class="style8"><a href="/RealEstateOnline/searchresults.php">Advanced Search </a></td>
          <td width="5%" rowspan="2" class="style2">&nbsp;</td>
          <td width="14%" rowspan="2" class="style2"><input type="submit" name="search" id="search" value="Search" /></td>


        </tr>
        <tr>
          <td height="21">&nbsp;</td>
        </tr>
      </table>
    </form>
    <p><br />
   <?php 
if(isset($_POST['search'])) { 
        foreach($_POST as $key=>$value) { 
                $$key = mysql_real_escape_string($value); 
        } 
       
        $query = mysql_query("SELECT * FROM listings WHERE price BETWEEN $minPrice AND $minPrice AND listingType IN($listingType) AND region IN($region) AND propType IN($propType)") or die(mysql_error()); 
        $numrows = mysql_num_rows($query); 
        if($numrows <= 0) { 
                echo 'No Results'; 
        } else { 
                while($row = mysql_fetch_array($query)) { 
                        echo 'Listing: '.$row['listingType'].', Region: '.$row['region'].', Property: '.$row['propType'].', Price: '.$row['price'].'<br />'; 
                } 
        } 
} 
?> 

Open in new window

0
john-formbyCommented:
Hi,

There were a couple of problems.

1.  The BETWEEN comparison for price needs to be done on values without commas (,)
2.  If you are using characters for IN, you need to wrap them in quotes

Please try the following code and let me know.

Hope this helps,

John
<form id="QuickPropertySearchForm" name="QuickPropertySearchForm" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
      <table width="100%" border="0">
        <tr>
          <td width="69%">&nbsp;</td>
          <td width="12%" height="25" class="style8"><span class="style81">Region:</span></td>
          <td height="20" colspan="2"><span class="style81">
            <select name="region" class="style2" id="region" size="">
              <option>Select Region</option>
              <option value="'Nicosia','Larnaca','Limassol','Paphos','Ayia Napa'">Any</option>
              <option value="'Nicosia'">Nicosia</option>
              <option value="'Larnaca'">Larnaca</option>
              <option value="'Limassol'">Limassol</option>
              <option value="'Paphos'">Paphos</option>
              <option value="'Ayia Napa'">Ayia Napa</option>
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="24">&nbsp;</td>
          <td height="25" class="style8">Listing Type</td>
          <td height="20" colspan="2"><span class="style81">
            <select name="listingType" class="style21" id="listingType">
              <option selected="selected">Please Select</option>
              <option value="'For Rent','For Sale'">Any</option>
              <option value="'For Rent'">For Rent</option>
              <option value="'For Sale'">For Sale</option>
              
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="24">&nbsp;</td>
          <td height="25" class="style8"><span class="style81">Property Type: 
            
          </span></td>
          <td height="20" colspan="2"><span class="style81">
            <select name="propType" class="style21" id="propType">
              <option value="">Select Property Type</option>
              <option value="'Flat','House','Shop','Office','Plot','Land','Warehouse'">Any</option>
              <option value="'Flat'">Flat</option>
              <option value="'House'">House</option>
              <option value="'Shop'">Shop</option>
              <option value="'Office'">Office</option>
              <option value="'Plot'">Plot</option>
              <option value="'Land'">Land</option>
              <option value="'Warehouse'">Warehouse</option>
            </select>
          </span></td>
        </tr>
        <tr>
          <td height="23">&nbsp;</td>
          <td height="25" class="style8">Minimum Price (&#8364;):</td>
          <td height="20" colspan="2"><select name="minPrice" class="style21" id="minPrice">
            <option value="0" selected="selected">Any</option>
            <option value="10000">10,000</option>
            <option value="20000">20,000</option>
            <option value="30000">30,000</option>
            <option value="40000">40,000</option>
            <option value="50000">50,000</option>
            <option value="60000">60,000</option>
            <option value="70000">70,000</option>
            <option value="80000">80,000</option>
            <option value="90000">90,000</option>
            <option value="100000">100,000</option>
            <option value="110000">110,000</option>
            <option value="120000">120,000</option>
            <option value="130000">130,000</option>
            <option value="140000">140,000</option>
            <option value="150000">150,000</option>
            <option value="160000">160,000</option>
            <option value="170000">170,000</option>
            <option value="180000">180,000</option>
            <option value="190000">190,000</option>
            <option value="200000">200,000</option>
            <option value="220000">220,000</option>
            <option value="240000">240,000</option>
            <option value="260000">260,000</option>
            <option value="280000">280,000</option>
            <option value="300000">300,000</option>
            <option value="320000">320,000</option>
            <option value="340000">340,000</option>
            <option value="360000">360,000</option>
            <option value="380000">380,000</option>
            <option value="400000">400,000</option>
            <option value="420000">420,000</option>
            <option value="440000">440,000</option>
            <option value="460000">460,000</option>
            <option value="480000">480,000</option>
            <option value="500000">500,000</option>
            <option value="550000">550,000</option>
            <option value="600000">600,000</option>
            <option value="650000">650,000</option>
            <option value="700000">700,000</option>
            <option value="750000">750,000</option>
            <option value="800000">800,000</option>
            <option value="900000">900,000</option>
            <option value="1000000">1,000,000</option>
            <option value="1500000">1,500,000</option>
            <option value="2000000">2,000,000</option>
          </select></td>
        </tr>
        <tr>
          <td height="28">&nbsp;</td>
          <td height="25" class="style8">Maximum Price<span class="style81"> (&#8364;)</span>:</td>
          <td height="20" colspan="2"><select name="maxPrice" class="style21" id="maxPrice">
            <option value="9999999" selected="selected">Any</option>
            <option value="10000">10,000</option>
            <option value="20000">20,000</option>
            <option value="30000">30,000</option>
            <option value="40000">40,000</option>
            <option value="50000">50,000</option>
            <option value="60000">60,000</option>
            <option value="70000">70,000</option>
            <option value="80000">80,000</option>
            <option value="90000">90,000</option>
            <option value="100000">100,000</option>
            <option value="110000">110,000</option>
            <option value="120000">120,000</option>
            <option value="130000">130,000</option>
            <option value="140000">140,000</option>
            <option value="150000">150,000</option>
            <option value="160000">160,000</option>
            <option value="170000">170,000</option>
            <option value="180000">180,000</option>
            <option value="190000">190,000</option>
            <option value="200000">200,000</option>
            <option value="220000">220,000</option>
            <option value="240000">240,000</option>
            <option value="260000">260,000</option>
            <option value="280000">280,000</option>
            <option value="300000">300,000</option>
            <option value="320000">320,000</option>
            <option value="340000">340,000</option>
            <option value="360000">360,000</option>
            <option value="380000">380,000</option>
            <option value="400000">400,000</option>
            <option value="420000">420,000</option>
            <option value="440000">440,000</option>
            <option value="460000">460,000</option>
            <option value="480000">480,000</option>
            <option value="500000">500,000</option>
            <option value="550000">550,000</option>
            <option value="600000">600,000</option>
            <option value="650000">650,000</option>
            <option value="700000">700,000</option>
            <option value="750000">750,000</option>
            <option value="800000">800,000</option>
            <option value="900000">900,000</option>
            <option value="1000000">1,000,000</option>
            <option value="1500000">1,500,000</option>
            <option value="2000000">2,000,000</option>
          </select></td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td width="12%" rowspan="2" class="style8"><a href="/RealEstateOnline/searchresults.php">Advanced Search </a></td>
          <td width="5%" rowspan="2" class="style2">&nbsp;</td>
          <td width="14%" rowspan="2" class="style2"><input type="submit" name="search" id="search" value="Search" /></td>


        </tr>
        <tr>
          <td height="21">&nbsp;</td>
        </tr>
      </table>
    </form>
    <p><br />
   <?php 
if(isset($_POST['search'])) { 
        foreach($_POST as $key=>$value) { 
                $$key = $value; 
        } 
        $query = mysql_query("SELECT * FROM listings WHERE price BETWEEN $minPrice AND $maxPrice AND listingType IN($listingType) AND region IN($region) AND propType IN($propType)") or die(mysql_error());        
        $numrows = mysql_num_rows($query); 
        if($numrows <= 0) { 
                echo 'No Results'; 
        } else { 
                while($row = mysql_fetch_array($query)) { 
                        echo 'Listing: '.$row['listingType'].', Region: '.$row['region'].', Property: '.$row['propType'].', Price: '.$row['price'].'<br />'; 
                } 
        } 
} 
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john-formbyCommented:
Oh, one more thing.  If your prices in the listings table have commas, you need to remove these as well, e.g. 250,000 should be 250000
0
dino_angelidesAuthor Commented:
wow it worked :) thx john....very very very much
I am just curious though why not use the commas ? i mean if i want to use them is there any way?
0
dino_angelidesAuthor Commented:
oh and another thing....i just put the php code in to the searchresults.php and it does not show anything....do i need to change anything in the code ?
0
dino_angelidesAuthor Commented:
ok i got it show :)
0
dino_angelidesAuthor Commented:
to be more precise, i used dreamwaver to show region (5 results per page) and show recordset but as i try to implement those we managed to make it work earlier dont work in my display table....if you want I can make a new question so i give the points for the question you answered for me here
0
john-formbyCommented:
Wow, I went for dinner and came back to a plethora of comments ;-)

Ok, you are making a comparison between two values.  If you have a comma, then it is not a number, but a string.  You can format the output using number_format like this:

echo 'Listing: '.$row['listingType'].', Region: '.$row['region'].', Property: '.$row['propType'].', Price: '.number_format($row['price'],0,'.',',').'<br />';

to make it show.  The value in the table should be without commas though.

With regards to the pagination, that is a separate issue so another question would be better for this.

Thanks,

John
0
dino_angelidesAuthor Commented:
I will make another question for that :) thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.