jakebur01 Posted April 14, 2007 Share Posted April 14, 2007 Ok, I have tryed different things with this and nothing i've come up with has worked. What I have is a PHP Class and MySQL table to find the distance between zip codes and * find all zip codes within a given mileage or kilometer range. I have a table named zip_code with all 40000 zip codes in it. The program works great when calculating a distance between two zips, and returning zips within a given range. What I am trying to do is add onto the code to have it select from a table I have created called "smithssc" the dealers within range. I have 5 colums in the table: Dealer, Address, City, State, and Zip. I was trying to add something like this onto it. I am not connecting my dots right. $result = mysql_query("SELECT * FROM smithssc WHERE Zip = '$key'", $db); echo "<table>"; while($myrow = mysql_fetch_array ($result)) { echo"<tr><td>"; echo$myrow["Dealer"]; echo"<td>"; echo$myrow["Address"]; echo"<td>"; echo$myrow["City"]; echo"<td>"; echo$myrow["State"]; echo"<td>"; echo$myrow["Zip"]; } echo "</table>"; Using this will not pull anything up with that variable $key. Here is the code: demo.php <!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Zip Code Range and Distance Calculation Demo</title> <style type="text/css" lang="en"> BODY, P { font-family: sans-serif; font-size: 9pt; } H3 { font-family: sans-serif; font-size: 14pt; } </style> </head> <body> <?php /* DEMO for using the zipcode PHP class. By: Micah Carrick Questions? Comments? Suggestions? [email protected] */ require_once('zipcode.class.php'); // zip code class // Open up a connection to the database. The sql required to create the MySQL // tables and populate them with the data is in the /sql subfolder. You can // upload those sql files using phpMyAdmin or a MySQL prompt. You will have to // modify the below information to your database information. $db = mysql_connect('localhost','myuser','mypass') or die(mysql_error()); mysql_select_db('mydata') or die(mysql_error()); // Below is an example of how to calculate the distance between two zip codes. echo '<h3>A sample calculating the distance between 2 zip codes: 93001 and 60618</h3>'; $z = new zipcode_class; $miles = $z->get_distance(97214, 98501); if ($miles === false) echo 'Error: '.$z->last_error; else echo "Zip code <b>97214</b> is <b>$miles</b> miles away from <b>98501</b>.<br />"; // Below is an example of how to return an array with all the zip codes withing // a range of a given zip code along with how far away they are. The array's // keys are assigned to the zip code and their value is the distance from the // given zip code. echo '<h3>A sample getting all the zip codes withing a range: 90 miles from 97214</h3>'; $zips = $z->get_zips_in_range('97214', 90, _ZIPS_SORT_BY_DISTANCE_ASC, true); if ($zips === false) echo 'Error: '.$z->last_error; else { foreach ($zips as $key => $value) { echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>97214</b>.<br />"; } // One thing you may want to do with this is create SQL from it. For example, // iterate through the array to create SQL that is something like: // WHERE zip_code IN ('93001 93002 93004') // and then use that condition in your query to find all pizza joints or // whatever you're using it for. Make sense? Hope so. echo "<br /><i>get_zips_in_range() executed in <b>".$z->last_time."</b> seconds.</i><br />"; } // And one more example of using the class to simply get the information about // a zip code. You can then do whatever you want with it. The array returned // from the function has the database field names as the keys. I just do a // couple string converstions to make them more readable. echo '<h3>A sample getting details about a zip code: 97214</h3>'; $details = $z->get_zip_details('97214'); if ($details === false) echo 'Error: '.$z->last_error; else { foreach ($details as $key => $value) { $key = str_replace('_',' ',$key); $key = ucwords($key); echo "$key: $value<br />"; } } ?> </body> </html> zipcode.class.php <?php /******************************************************************************* * ZIP Code and Distance Claculation Class ******************************************************************************* * Author: Micah Carrick * Email: [email protected] * Website: http://www.micahcarrick.com * * File: zipcode.class.php * Version: 1.2.0 * Copyright: (c) 2005 - Micah Carrick * You are free to use, distribute, and modify this software * under the terms of the GNU General Public License. See the * included license.txt file. * ******************************************************************************* * VERION HISTORY: * v1.2.0 [Oct 22, 2006] - Using a completely new database based on user contributions which resolves many data bugs. - Added sorting to get_zips_in_range() - Added ability to include/exclude the base zip from get_zips_in_range() * v1.1.0 [Apr 30, 2005] - Added Jeff Bearer's code to make it MUCH faster! * v1.0.1 [Apr 22, 2005] - Fixed a typo * v1.0.0 [Apr 12, 2005] - Initial Version * ******************************************************************************* * DESCRIPTION: * A PHP Class and MySQL table to find the distance between zip codes and * find all zip codes within a given mileage or kilometer range. * ******************************************************************************* */ // constants for setting the $units data member define('_UNIT_MILES', 'm'); define('_UNIT_KILOMETERS', 'k'); // constants for passing $sort to get_zips_in_range() define('_ZIPS_SORT_BY_DISTANCE_ASC', 1); define('_ZIPS_SORT_BY_DISTANCE_DESC', 2); define('_ZIPS_SORT_BY_ZIP_ASC', 3); define('_ZIPS_SORT_BY_ZIP_DESC', 4); // constant for miles to kilometers conversion define('_M2KM_FACTOR', 1.609344); class zipcode_class { var $last_error = ""; // last error message set by this class var $last_time = 0; // last function execution time (debug info) var $units = _UNIT_MILES; // miles or kilometers var $decimals = 2; // decimal places for returned distance function get_distance($zip1, $zip2) { // returns the distance between to zip codes. If there is an error, the // function will return false and set the $last_error variable. $this->chronometer(); // start the clock if ($zip1 == $zip2) return 0; // same zip code means 0 miles between. // get details from database about each zip and exit if there is an error $details1 = $this->get_zip_point($zip1); $details2 = $this->get_zip_point($zip2); if ($details1 == false) { $this->last_error = "No details found for zip code: $zip1"; return false; } if ($details2 == false) { $this->last_error = "No details found for zip code: $zip2"; return false; } // calculate the distance between the two points based on the lattitude // and longitude pulled out of the database. $miles = $this->calculate_mileage($details1[0], $details2[0], $details1[1], $details2[1]); $this->last_time = $this->chronometer(); if ($this->units == _UNIT_KILOMETERS) return round($miles * _M2KM_FACTOR, $this->decimals); else return round($miles, $this->decimals); // must be miles } function get_zip_details($zip) { // This function pulls the details from the database for a // given zip code. $sql = "SELECT lat AS lattitude, lon AS longitude, city, county, state_prefix, state_name, area_code, time_zone FROM zip_code WHERE zip_code='$zip'"; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } else { $row = mysql_fetch_array($r, MYSQL_ASSOC); mysql_free_result($r); return $row; } } function get_zip_point($zip) { // This function pulls just the lattitude and longitude from the // database for a given zip code. $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'"; $r = mysql_query($sql); if (!$r) { $this->last_error = mysql_error(); return false; } else { $row = mysql_fetch_array($r); mysql_free_result($r); return $row; } } function calculate_mileage($lat1, $lat2, $lon1, $lon2) { // used internally, this function actually performs that calculation to // determine the mileage between 2 points defined by lattitude and // longitude coordinates. This calculation is based on the code found // at http://www.cryptnet.net/fsp/zipdy/ // Convert lattitude/longitude (degrees) to radians for calculations $lat1 = deg2rad($lat1); $lon1 = deg2rad($lon1); $lat2 = deg2rad($lat2); $lon2 = deg2rad($lon2); // Find the deltas $delta_lat = $lat2 - $lat1; $delta_lon = $lon2 - $lon1; // Find the Great Circle distance $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2); $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp)); return $distance; } function get_zips_in_range($zip, $range, $sort=1, $include_base) { // returns an array of the zip codes within $range of $zip. Returns // an array with keys as zip codes and values as the distance from // the zipcode defined in $zip. $this->chronometer(); // start the clock $details = $this->get_zip_point($zip); // base zip details if ($details == false) return false; // This portion of the routine calculates the minimum and maximum lat and // long within a given range. This portion of the code was written // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases // the time it takes to execute a query. My demo took 3.2 seconds in // v1.0.0 and now executes in 0.4 seconds! Greate job Jeff! // Find Max - Min Lat / Long for Radius and zero point and query // only zips in that range. $lat_range = $range/69.172; $lon_range = abs($range/(cos($details[0]) * 69.172)); $min_lat = number_format($details[0] - $lat_range, "4", ".", ""); $max_lat = number_format($details[0] + $lat_range, "4", ".", ""); $min_lon = number_format($details[1] - $lon_range, "4", ".", ""); $max_lon = number_format($details[1] + $lon_range, "4", ".", ""); $return = array(); // declared here for scope $sql = "SELECT zip_code, lat, lon FROM zip_code "; if (!$include_base) $sql .= "WHERE zip_code <> '$zip' AND "; else $sql .= "WHERE "; $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon'"; $r = mysql_query($sql); if (!$r) { // sql error $this->last_error = mysql_error(); return false; } else { while ($row = mysql_fetch_row($r)) { // loop through all 40 some thousand zip codes and determine whether // or not it's within the specified range. $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]); if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR; if ($dist <= $range) { $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals); } } mysql_free_result($r); } // sort array switch($sort) { case _ZIPS_SORT_BY_DISTANCE_ASC: asort($return); break; case _ZIPS_SORT_BY_DISTANCE_DESC: arsort($return); break; case _ZIPS_SORT_BY_ZIP_ASC: ksort($return); break; case _ZIPS_SORT_BY_ZIP_DESC: krsort($return); break; } $this->last_time = $this->chronometer(); if (empty($return)) return false; return $return; } function chronometer() { // chronometer function taken from the php manual. This is used primarily // for debugging and anlyzing the functions while developing this class. $now = microtime(TRUE); // float, in _seconds_ $now = $now + time(); $malt = 1; $round = 7; if ($this->last_time > 0) { /* Stop the chronometer : return the amount of time since it was started, in ms with a precision of 3 decimal places, and reset the start time. We could factor the multiplication by 1000 (which converts seconds into milliseconds) to save memory, but considering that floats can reach e+308 but only carry 14 decimals, this is certainly more precise */ $retElapsed = round($now * $malt - $this->last_time * $malt, $round); $this->last_time = $now; return $retElapsed; } else { // Start the chronometer : save the starting time $this->last_time = $now; return 0; } } } Link to comment https://forums.phpfreaks.com/topic/46997-need-help-adding-on/ Share on other sites More sharing options...
youneek Posted April 15, 2007 Share Posted April 15, 2007 Have you tried the below to try and get an error? $result = mysql_query($query) or die(mysql_error()); Link to comment https://forums.phpfreaks.com/topic/46997-need-help-adding-on/#findComment-229465 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.