Chapter 6. Property Search

In the previous chapter we saw a very simple application that performed a full-text search on the feed items. In this chapter we will create a more advanced application. So pull up your socks, and get ready to build a complex search application that will search through real estate property listings.

In this chapter we shall:

• Create a form to add properties

• Create a form to perform simple full-text search on properties

• Create an advanced search form

• Create a form to perform geo-location based searching

So let's get on with it...

The application

This application will search a database of real estate properties. We will create this database and populate it with some data. A property will have the following associated information (fields):

• Property type

• Description

• Price

• City

• Address

• Zip code

• Number of bedrooms

• Number of bathrooms

• Property area

• Year in which the property was built

• Geo-coordinates of the property

• Amenities

We will be creating different search forms; such as a simple, an advanced, and a geo location based search. These forms will have the following characteristics:

• Simple: A city based filter and full-text search with different weights assigned to each field. We will have a drop-down from which the user can select the city, and a text box to enter search keywords.

• Advanced: A purely filter based search. We will also use ranged filters in this form. A user will be able to specify the type of property, budget, minimum number of bedrooms, minimum area, and the age of the property as search criteria.

• Geo location: In...

Time for action - creating the MySQL database and structure

1. 1. Open phpMyAdmin and create a database sphinx_properties. You can use an existing database as well.

2. 2. Import the following SQL to create the database tables:

   CREATE TABLE IF NOT EXISTS `amenities` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
   CREATE TABLE IF NOT EXISTS `amenities_properties` (
   `amenity_id` int(11) NOT NULL,
   `property_id` int(11) NOT NULL,
   PRIMARY KEY (`amenity_id`,`property_id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
   CREATE TABLE IF NOT EXISTS `cities` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
   CREATE TABLE IF NOT EXISTS `properties` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `type` enum('1','2') NOT NULL DEFAULT '1',
   `transaction_type` enum('1','2','3') NOT NULL DEFAULT '1',
   `description` text NOT NULL,
   `price` int(11) NOT NULL,
   `city_id` int(11) NOT...

Time for action - populating the database

Import the following SQL to populate amenities and cities tables:

--
-- Dumping data for table `amenities`
--
INSERT INTO `amenities` (`id`, `name`) VALUES
(1, 'Parking'),
(2, 'Swimming Pool'),
(3, 'Garden'),
(4, 'Elevator'),
(5, 'Club House'),
(6, 'Watchman');
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`id`, `name`) VALUES
(1, 'Nagpur'),
(2, 'Mumbai'),
(3, 'New Delhi'),
(4, 'London'),
(5, 'New York'),
(6, 'Hyderabad');

What just happened?

We populated the amenities and cities tables with some data. We did not populate the properties table, as we will be creating a simple form to add a property in the next exercise.

Basic setup

We will use the exact same setup as we did in Chapter 5, Feed Search, and you can use the same directory structure and common files, such as init.php.

Time for action - setting up the application

1. 1. Create a properties directory in your webroot, /path/to/webroot/properties:

   $ mkdir /path/to/webroot/properties
   

2. 2. Create the /path/to/webroot/properties/views directory:

   $ mkdir /path/to/webroot/properties/views
   

3. 3. Copy the sphinxapi.php file from the Sphinx source directory to the properties directory:

   $ cp /path/to/sphinx-0.9.9/api/sphinxapi.php /path/to/webroot/properties/
   

4. 4. Create the file /path/to/webroot/properties/init.php with the following code:

   <?php
   /**
   * File: /path/to/webroot/properties/init.php
   */
   // Database connection credentials
   $dsn ='mysql:dbname=sphinx_properties;host=localhost';
   $user = 'root';
   $pass = '';
   // Instantiate the PDO (PHP 5 specific) class
   try {
   $dbh = new PDO($dsn, $user, $pass);
   } catch (PDOException $e){
   echo'Connection failed: '.$e->getMessage();
   }
   // Array to hold variables to be used in views
   $viewVars = array();
   /**
   * Method to fetch the contents of a view (thtml) file
   * and return the contents...

Time for action - creating the form to add property

1. 1. Create a file /path/to/webroot/properties/add.php with the following content:

   <?php
   /**
   * File: /path/to/webroot/properties/add.php
   */
   include('init.php');
   // Get the list of cities
   $query = "SELECT id, name FROM cities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['cities'][$row['id']] = $row['name'];
   }
   // Get the list of localities
   $query = "SELECT id, name FROM localities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['localities'][$row['id']] = $row['name'];
   }
   // Get the list of amenities
   $query = "SELECT id, name FROM amenities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['amenities'][$row['id']] = $row['name'];
   }
   // If form is submitted then save the data
   // We aren't doing any validation but in a real
   // web application you should.
   if (!empty($_POST['description'])) {
   // Query to insert the property
   $query = "INSERT INTO
   properties
   SET
   type = :type,
   transaction_type = :transaction_type,
   description...

Time for action - creating the index

1. 1. Create the Sphinx configuration file at /usr/local/sphinx/etc/properties.conf with the following content:

   source properties-source
   {
   type = mysql
   sql_host = localhost
   sql_user = root
   sql_pass =
   sql_db = sphinx_properties
   sql_query_range = SELECT MIN(id), MAX(id) FROM properties
   sql_range_step = 1000
   sql_query = SELECT id, type, \
   transaction_type, description, \
   price, city_id, bedrooms, bathrooms, area, \
   address, zip_code, built_year, \
   (latitude * PI() / 180) AS latitude, \
   (longitude * PI() / 180) AS longitude, \
   UNIX_TIMESTAMP(date_added) AS date_added \
   FROM properties
   WHERE id >= $start AND id <= $end
   sql_attr_uint = type
   sql_attr_uint = transaction_type
   sql_attr_uint = price
   sql_attr_uint = city_id
   sql_attr_uint = bedrooms
   sql_attr_uint = bathrooms
   sql_attr_float = area
   sql_attr_uint = built_year
   sql_attr_float = latitude
   sql_attr_float = longitude
   sql_attr_timestamp = date_added
   sql_attr_multi = uint amenity_id from query; \
   SELECT...

Time for action - creating the simple search form

1. 1. Create a script /path/to/webroot/properties/index.php with the following content (this will be our home page):

   <?php
   /**
   * File: /path/to/webroot/properties/index.php
   */
   include('init.php');
   // Get the list of cities
   $query = "SELECT id, name FROM cities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['cities'][$row['id']] = $row['name'];
   }
   // Get the query and city from form (if submitted)
   $q = !empty($_POST['q']) ? $_POST['q'] : '';
   $city = !empty($_POST['city_id']) ? $_POST['city_id'] : '';
   $viewVars['q'] = $q;
   $viewVars['city_id'] = $city;
   render('index');
   

2. 2. Create the view for the form at /path/to/webroot/properties/views/index.thtml:

   <form action="index.php" method="post">
   <fieldset>
   <legend>Search</legend>
   <div class="input">
   <label>City: </label>
   <select name="city_id">
   <?php foreach ($viewVars['cities']
   as $id => $name): ?>
   <?php
   $selected = '';
   if ($id =...

Time for action - adding code to perform full-text search

1. 1. Start the searchd daemon (as root):

   $ /usr/local/sphinx/bin/searchd c /usr/local/sphinx/etc/properties.conf
   

   

2. 2. Modify index.php and add the following (highlighted) code:

   <?php
   /**
   * File: /path/to/webroot/properties/index.php
   */
   include('init.php');
   // Get the list of cities
   $query = "SELECT id, name FROM cities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['cities'][$row['id']] = $row['name'];
   }
   $q = !empty($_POST['q']) ? $_POST['q'] : '';
   $city = !empty($_POST['city_id']) ? $_POST['city_id'] : '';
   // If we have the search term
   if (!empty($q)) {
   // Include the api class
   require_once('sphinxapi.php');
   $client = new SphinxClient();
   // Set search options
   $client->SetServer('localhost', 9312);
   $client->SetConnectTimeout(1);
   $client->SetArrayResult(true);
   // Set the mode to SPH_MATCH_ANY
   $client->SetMatchMode(SPH_MATCH_ANY);
   // Weights for each field
   $weights = array(
   'description' => 1,
   'address' => 10...

Time for action - creating the Advanced search form

1. 1. Create a script /path/to/webroot/properties/search.php with the following content:

   <?php
   /**
   * File: /path/to/webroot/properties/search.php
   */
   include('init.php');
   // Get the list of cities
   $query = "SELECT id, name FROM cities";
   foreach ($dbh->query($query) as $row) {
   $viewVars['cities'][$row['id']] = $row['name'];
   }
   // Render the view
   render('search');
   

2. 2. Create the view for the search page at /path/to/webroot/properties/views/search.thtml:

   <form action="advanced_search.php" method="post">
   <fieldset>
   <legend>Advanced search</legend>
   <div class="input">
   <label>City: </label>
   <select name="city_id">
   <?php foreach ($viewVars['cities'] as $id => $name):?>
   <option value="<?php echo $id; ?>">
   <?php echo $name; ?></option>
   <?php endforeach; ?>
   </select>
   </div>
   <div class="input">
   <label>Type: </label>
   <input type...

Time for action - adding ranged filters

1. 1. Modify /path/to/webroot/properties/views/search.thtml as highlighted in the following code:

   <!-- File: /path/to/webroot/properties/views/search.thtml -->
   <input type="radio"
   name="transaction_type" value="2" /> Rent
   &nbsp;&nbsp;&nbsp;
   <input type="radio"
   name="transaction_type" value="3" /> PG
   </div>
   <div class="input">
   <label>Budget ($): </label>
   <input type="text" name="min_price" size="5" /> to
   <input type="text" name="max_price" size="5" />
   </div>
   <div class="input">
   <label>Min Bedrooms: </label>
   <input type="text" name="bedrooms" size="1" />
   </div>
   <div class="input">
   <label>Min Bathrooms: </label>
   <input type="text" name="bathrooms" size="1" />
   </div>
   <div class="input">
   <label>Min Area: </label>
   <input type="text" name="area" size="4" />
   </div>
   <div class="input">
   <label...

Time for action - creating the search form

1. 1. Create a script /path/to/webroot/properties/geo_search.php with the following content:

   <?php
   /**
   * File: /path/to/webroot/properties/geo_search.php
   */
   include('init.php');
   // Get the data from form (if submitted)
   $latitude = !empty($_POST['latitude']) ? $_POST['latitude'] : '';
   $longitude = !empty($_POST['longitude']) ? $_POST['longitude'] : '';
   $radius = !empty($_POST['radius']) ? $_POST['radius'] : 5;
   // Set the variables for view
   $viewVars['latitude'] = $latitude;
   $viewVars['longitude'] = $longitude;
   $viewVars['radius'] = $radius;
   render('geo_search');
   

2. 2. Create the view for the geo search page at /path/to/webroot/properties/views/geo_search.thtml:

   <!-- File: /path/to/webroot/properties/views/geo_search.thtml -->
   <form action="geo_search.php" method="post">
   <fieldset>
   <legend>Geo Location Search</legend>
   <div class="input">
   <label>Latitude: </label>
   <input type="text" name="latitude...

Time for action - adding code to perform geo distance search

1. 1. Modify /path/to/webroot/properties/geo_search.php and add the following (highlighted) code:

   <?php
   /**
   * File: /path/to/webroot/properties/geo_search.php
   */
   include('init.php');
   // Get the data from form (if submitted)
   $latitude = !empty($_POST['latitude']) ? $_POST['latitude'] : '';
   $longitude = !empty($_POST['longitude']) ? $_POST['longitude'] : '';
   $radius = !empty($_POST['radius']) ? $_POST['radius'] : 5;
   // If we have coordinates then perform the search
   if (!empty($latitude) && !empty($longitude)) {
   // Include the api class
   require_once('sphinxapi.php');
   $client = new SphinxClient();
   // Set search options
   $client->SetServer('localhost', 9312);
   $client->SetConnectTimeout(1);
   $client->SetArrayResult(true);
   // Set the mode to SPH_MATCH_FULLSCAN
   $client->SetMatchMode(SPH_MATCH_FULLSCAN);
   // Convert the latitude and longitude to radians
   $lat = $latitude * (M_PI / 180);
   $lon = $longitude * (M_PI / 180)...

Summary

In this chapter:

• We saw some more API methods and explored them. We created an application to search a properties database.

• We created a simple Search form to perform a full-text search on properties and filter them by city. We used different field weights, so that results are sorted with closest matches at the top.

• We also created an Advanced search form where we implemented a lot of filters.

• We learned how to use ranged filters.

• We created a search form for Geo Location Search. We saw how Sphinx makes it easy to find locations within the specified radius if the index contains geographical coordinates.

In the next chapter we will learn about the Sphinx configuration file and explore some advanced configuration options.