Complicated MySQL Query - Property Searches.

[mctoys]

Hi There

 

I have a postcode database and a properties database. I also have a PHP function which calculates the distance between two Long and Lat Values which I can get from the postcode database.

 

I am looking to search for properties which are within a certain radius of the inputted postcode. I can do this in a roundabout sort of way but am convinced their ought to be a better way of doing it! Here are snippets of what I have done (which works). Can anyone help me write a better Query to avoid having to select all the property data and only displaying the properties in range.

 

// Get Values from posted form

$distance = $_REQUEST['distance'];

$postcode = $_REQUEST['keyword'];

 

// Get Lat and Long values for postcode entered in form and store them to variables

$pc_result=mysql_query("SELECT * FROM postcode_data WHERE postcode LIKE '$postcode' LIMIT 1");

while($row = mysql_fetch_assoc($pc_result)) {

$longitude = $row['long_dec'];

$latitude = $row['lat_dec'];

}

mysql_free_result($pc_result);

 

// Get all property data

$result=mysql_query("SELECT * FROM properties");

$num_results = mysql_num_rows($result);

$propertyArray = array();

$i = 0;

while($row = mysql_fetch_assoc($result)) {

$propertyArray[$i]['id'] = $row['id'];

$propertyArray[$i]['Photo'] = $row['Photo'];

$propertyArray[$i]['Address'] = $row['Address'];

$propertyArray[$i]['Postcode'] = $row['Postcode'];

$i++;

}

 

}

 

// Display all property data...

foreach($propertyArray as $property) {

 

                        // Get lat and long data for each property postcode in the database and store them to variables

                        $distance_result=mysql_query("SELECT * FROM postcode_data WHERE postcode LIKE '".$property['Postcode']."' LIMIT 1");

while($row = mysql_fetch_assoc($distance_result)) {

$distance_longitude = $row['long_dec'];

$distance_latitude = $row['lat_dec'];

}

mysql_free_result($distance_result);

 

                        // Get distance using external PHP function

$property_distance = round(distance($distance_latitude, $distance_longitude,$latitude,$longitude, "M"),1);

 

                        // Only display properties which are in radius

if($property_distance < $distance) {  ?>

    <div id="property">

              <?php echo $property_distance; ?> miles<br />

              <img src="<?php echo $property['Photo']; ?>" />

              <p class="details"><?php echo $property['Address']; ?></p>

    </div>

 

<?php } } ?>

 

Very messy code - I also can't count the number of results in range! - HELP!

[F00Baron]

Here's a page with the distance calculation

http://jan.ucc.nau.edu/~cvm/latlon_formula.html

which I converted to SQL for a similar project:

$sql="SELECT properties.*,ACOS(COS(RADIANS(latitude)) *
COS(RADIANS(longitude)) * COS(RADIANS(32.85)) * COS(RADIANS($longitude)) +
COS(RADIANS(latitude)) * SIN(RADIANS(longitude)) * COS(RADIANS($latitude)) * 
SIN(RADIANS($longitude)) + SIN(RADIANS(latitude)) * SIN(RADIANS($latitude))) * 
3963.1 AS Distance FROM properties WHERE Distance <= '$distance'";

 

You could add latitude and longitude to your properties table, update it from the zips table, and use something like that.

 

[mctoys]

Hiya

 

Thanks so much it looks like exactly what I want although there is one mysql_error which comes up: Unknown column 'Distance' in 'where clause'. I'm not sure why this is as I can see you have brought that data out as Distance in the query. Any ideas?

 

Many Thanks

Dan