jasonc Posted November 30, 2010 Share Posted November 30, 2010 below is my testing script i have been working on but as you can see this will take a lot of resources on the server if i was to search say over 5000 members ?! i would like some help in finding a way to do a single query and not grabbing each users details and checking each and everyone of them one by one for the matches. is this possible? <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <? function distance($pcodeA,$pcodeB) { $db_code1 = explode(' ',$pcodeA); $db_code2 = explode(' ',$pcodeB); $postcode1 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code1[0]' LIMIT 1")); $postcode2 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code2[0]' LIMIT 1")); $p1_gridN = $postcode1['grid_n']; $p1_gridE = $postcode1['grid_e']; $p2_gridN = $postcode2['grid_n']; $p2_gridE = $postcode2['grid_e']; $distance_n=$p1_gridN-$p2_gridN; $distance_e=$p1_gridE-$p2_gridE; $hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e)); $distance = array("".round($hypot)."","".round(0.62*($hypot/1000),1).""); return $distance; } include('connection.php'); // details of the search to do. $searchGender = "Male"; $searchPostcode = "AB12"; $searchDistance = "2000"; // 5 miles // get all users details. $getUsers = mysql_query("SELECT * FROM user_accounts WHERE gender='$searchGender' ORDER BY account_id ASC"); $number = 0; // check if a user is within the 'searchDistance' of the '$user['postcode']', if so display their details. while($user = mysql_fetch_assoc($getUsers)) { $distance = distance($searchPostcode,$user['postcode']); // say $user['postcode'] = "AB12" echo("" . $user['postcode'] . " " . $searchDistance . " miles of their postcode.<br>"); if($distance[1] < $searchDistance && $distance[1] > 0 && $distance[0] > 0) { $number++; echo 'Fullname: ',$user['firstname'],' ',$user['middlename'],' ',$user['lastname'],' ',$user['gender'],"\n"; echo '<div id="distanceDiv'.$user['account_id'].'">'.$distance[1].":\n". '</div><br>'."\n\n"; } } ?> </body> </html> an extract of my mysql's CREATE TABLE IF NOT EXISTS `user_accounts` ( `account_id` int(100) NOT NULL auto_increment, `firstname` varchar(50) NOT NULL, `middlename` varchar(50) NOT NULL, `lastname` varchar(50) NOT NULL, `gender` varchar(6) NOT NULL, `postcode` varchar(10) NOT NULL, PRIMARY KEY (`account_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; INSERT INTO `user_accounts` (`account_id`, `firstname`, `middlename`, `lastname`, `gender`, `postcode`) VALUES (1, 'Mark', '', 'Smith', 'Male', 'SM1 1EA'), (1, 'Lisa', '', 'Smith', 'Female', 'SM2 1EA'), (1, 'Simon', '', 'Smith', 'Male', 'SM3 1EA'); CREATE TABLE IF NOT EXISTS `postcodes` ( `postcode_id` int(11) NOT NULL auto_increment, `postcode` tinytext NOT NULL, `grid_n` varchar(15) NOT NULL default '', `grid_e` varchar(15) NOT NULL default '', `latitude` varchar(15) NOT NULL default '', `longitude` varchar(15) NOT NULL default '', PRIMARY KEY (`postcode_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2823 ; INSERT INTO `postcodes` (`postcode_id`, `postcode`, `grid_n`, `grid_e`, `latitude`, `longitude`) VALUES (2295, 'SM1', '525900', '164600', '51.366', '-0.191'), (2296, 'SM2', '525600', '163100', '51.352', '-0.197'), (2297, 'SM3', '524400', '165000', '51.37', '-0.213'), (2298, 'SM4', '525300', '167500', '51.392', '-0.199'), (2299, 'SM5', '527600', '164800', '51.368', '-0.166'), (2300, 'SM6', '529300', '164000', '51.36', '-0.143'), (2301, 'SM7', '525400', '159800', '51.323', '-0.2'); Quote Link to comment https://forums.phpfreaks.com/topic/220278-how-to-calculate-distance-between-2-postcodes/ Share on other sites More sharing options...
fenway Posted December 3, 2010 Share Posted December 3, 2010 TLDR -- what exactly are you trying to do? Quote Link to comment https://forums.phpfreaks.com/topic/220278-how-to-calculate-distance-between-2-postcodes/#findComment-1142475 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.