MySQL Forums
Forum List  »  PHP

Displaying distance between 2 postcodes for each search query
Posted by: steve ralph
Date: July 18, 2005 08:17PM

Hi there
I am trying to display the distance between two postcodes and display them in a table of search results.
I have one table with uk poscodes and grid north/grid east
and the other stores the postcode and grid n /grid e of the data to display

//This require is on main search page:-
//require_once('php_funcs/searchresult_funcs.php');
//calc_postcode_seperation();


function calc_postcode_seperation(){//($pcodeA,$pcodeB)
global $pcd, $postcodeb, $dist, $mi, $field, $m2, $miles, $hypot, $postcd, $val_pcode_len, $mk, $num_records, $display, $ad_id ;

$val_pcode_len = (strlen($pcd));//Check the postcode format ie: n1 or ex280dv
switch ($val_pcode_len) {
case "2":
$postcd = substr($pcd,0,2);
break;
case "3":
$postcd = substr($pcd,0,3);
break;
case "6":
$postcd = substr($pcd,0,3);
break;
default:
$postcd = substr($pcd,0,4);
}

// PCODE A
$pca_result=mysql_query("SELECT * FROM postcodes WHERE Pcode = '$postcd'");
$row=mysql_fetch_array($pca_result);
$gridn[0] = $row[2];
$gride[0] = $row[3];

// PCODE B
$pcode_result=mysql_query("SELECT * FROM tblusers, adverts WHERE adverts.make = '$field' AND adverts.model = '$m2'");
$row=mysql_fetch_array($pcode_result);
//$num_records = @mysql_num_rows($query_result);
$ad_id = $row[0];
$mk = $row[1];
$gridn[1] = $row[5];
$gride[1] = $row[6];

// TAKE GRID REFS FROM EACH OTHER.
$distance_n=$gridn[0]-$gridn[1];
$distance_e=$gride[0]-$gride[1];

// CALCULATE THE DISTANCE BETWEEN THE TWO POINTS
$hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e));
$miles=round($hypot/1000/1.6093,0);

return;

//MAIN PAGE
// Check for a postcode.
if (eregi ("^[[:alpha:]]{1,2}[[:digit:]]{1,3}[[:alpha:]]{0,2}$", stripslashes(trim($_GET['postcode'])))) {
$pcd = escape_data($_GET['postcode']);
} else {
$pcd = FALSE;
echo '<p><font color="red">Please enter your postcode!</font></p>';
}

if ($pcd, $fild) {

require_once('php_funcs/searchresult_funcs.php');
calc_postcode_seperation();
}
if ($miles >= 0 && $miles >= $mi)
{

$query = "SELECT CONCAT(make) AS mk, (image_name) AS im, (ad_id) AS uid, DATE_FORMAT(registration_date, '%M %d, %Y') AS dr FROM users, ads, uplds WHERE ads.make = '$fild' AND adverts.ad_ID = users.user_ID AND ads.pic_id = uplds.im_id LIMIT 10";
$result = @mysql_query ($query); // Run the query
$num = mysql_num_rows ($result); // How many users are there?

// Table header.
echo '<table align="center" cellspacing="4" cellpadding="4" width="625">
<tr class="bodytxt" ><td align="center" width="60"><b>Image</b></td>
<td align="center" width="520"><b>Ads</b></td><td align="center" width="45">
<b>Date</b></td></tr>';
echo '</table>';

Fetch and print all the records.

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

echo '<table align="center" cellspacing="4" cellpadding="4" width="625" background="pics/bg.jpg">';
echo '<tr><td align="center" width="60">',
'<img src="phpthumb.php?src=photos/',stripslashes($row[1]),'&w=75"',
'</td><td align="left" width="520">', '<br>','<a href="ad.php?adid=',$row[3],">', stripslashes($row[0]), '</a>',' ', stripslashes($row[2]), ' ', 'You are aproximately ', $miles, ' miles from this ad.</font>',

'</td><td align="center" width="45">', $row[4],'<br><br>', '</td></tr>
';
}

echo '</table>'; // Close the table.



// the function works ok on 1 result but if there are several results it only shows the mileage for one ad. How can I show the different distances? Also in the form the searcher can select a distance to search. Is there a way I can build this into the query.
eg. SELECT * FROM ads WHERE $miles >= 0 && $miles >= $mi;
when all I have is the grid n /grid e's in the db

or would it be better to use a temparary db to store the miles and ids of the ads and then select from there.

or can I use a for or while loop.

Yours Thankfully
Steve

Options: ReplyQuote


Subject
Written By
Posted
Displaying distance between 2 postcodes for each search query
July 18, 2005 08:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.