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'); 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? 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
Archived
This topic is now archived and is closed to further replies.