spacepoet Posted February 22, 2011 Share Posted February 22, 2011 Hi: Not sure if this is where to ask this (maybe it's a mySQL Issue)?? I am working on a zip code locator. The field/column "zip" (which holds the zip codes) is set as a UNIQUE Key in the database. Problem is: I want to allow people to enter a new city if there is one they want to add. The only way I can do this is to remove the UNIQUE Key from the column, or I get an error. Some cities - like Malvern, PA and Frazer, PA - have the same zip code: 19355 This works fine, but the issue that now happens is the zip locator on the frontend will no longer find that zip code. Does anyone know what a solution might be? I can post the zip locator code, and maybe there's a chunk of code I can remove or alter to fix this? Not sure how to address this one. Quote Link to comment Share on other sites More sharing options...
AtomicRax Posted February 22, 2011 Share Posted February 22, 2011 Not sure if helpful, but I've used http://www.micahcarrick.com/php-zip-code-range-and-distance-calculation.html and what it does is just uses the more popular city name for the zip code instead of having multiple city names for the same zip... What you can look into maybe is just adding LIMIT 1 to the zip code locator on the front end... so then it would only list one city, when their searching by zip.. you could sort in alphabetical order or something like that and then LIMIT 1 for the one you want to select? Quote Link to comment Share on other sites More sharing options...
spacepoet Posted February 22, 2011 Author Share Posted February 22, 2011 Hi: I'm kinda stuck using what I have now ... I'll look into it, though. I just know this will become an issue, so trying to develop a solution while I'm still developing this system. Quote Link to comment Share on other sites More sharing options...
AtomicRax Posted February 22, 2011 Share Posted February 22, 2011 Well, I could *try* to help, but I'll need to see some code... Quote Link to comment Share on other sites More sharing options...
spacepoet Posted February 22, 2011 Author Share Posted February 22, 2011 OK, I appreciate this ... This is the entire front end code for the locator - is this what you need to see?? <?php // Create page variables $r = NULL; $z = NULL; $stores = NULL; $Errors = NULL; // Establish DB connection //$dbc = mysql_connect ('localhost', 'username', 'password'); //mysql_select_db ('store_locator', $dbc); include('include/myConn.php'); // Declare page functions function Dist ($lat_A, $long_A, $lat_B, $long_B) { $distance = sin(deg2rad($lat_A)) * sin(deg2rad($lat_B)) + cos(deg2rad($lat_A)) * cos(deg2rad($lat_B)) * cos(deg2rad($long_A - $long_B)); $distance = (rad2deg(acos($distance))) * 69.09; return $distance; } ### Handle form if submitted if (isset ($_POST['submitted'])) { // Validate Zip code field if (!empty ($_POST['zip']) && is_numeric ($_POST['zip'])) { $z = (int)$_POST['zip']; // Verify zip code exists $query = "SELECT lat, lon FROM zip_codes WHERE zip = '$z'"; $result = mysql_query ($query); if (mysql_num_rows ($result) == 1) { $zip = mysql_fetch_assoc ($result); } else { $Errors = '<p>The zip code you entered was not found!</p>'; } } // Validate radius field if (isset ($_POST['radius']) && is_numeric ($_POST['radius'])) { $r = (int)$_POST['radius']; } // Proceed if no errors were found if ($r && $z) { // Retrieve coordinates of the stores $stores = array(); $query = "SELECT name, address, town, state, postal, phone, hours, lat, lon FROM stores INNER JOIN zip_codes ON stores.postal = zip_codes.zip"; $result = mysql_query ($query); // Go through and check all stores while ($row = mysql_fetch_assoc ($result)) { // Separate closest stores $distance = Dist ($row['lat'], $row['lon'], $zip['lat'], $zip['lon']); // Check if store is in radius if ($distance <= $r) { $stores[] = array ( 'name' => $row['name'], 'address' => $row['address'], 'state' => $row['state'], 'town' => $row['town'], 'postal' => $row['postal'], 'phone' => $row['phone'], 'hours' => $row['hours'] ); } } } else { $Errors = ($Errors) ? $Errors : '<p>Errors were found please try again!</p>'; } } ?><html> <head> <title>Store Locator</title> </head> <body> <form action="" method="post"> <p>Enter your zip code below to find locations near you.</p> <?php echo ($Errors) ? $Errors : ''; ?> <div> <label>Zip:</label> <input name="zip" type="text" size="10" maxlength="5" /> </div> <!-- <div> <label>Search Area:</label> <select name="radius" id="radius"> <option value="5">5 mi.</option> <option value="10">10 mi.</option> <option value="15">15 mi.</option> <option value="20">20 mi.</option> </select> </div> //--> <div> <label>Search Area:</label> <input name="radius" id="radius" type="text" size="10" maxlength="5" /> </div> <div> <input type="hidden" name="submitted" value="submitted" /> <input type="submit" value="Submit" /> </div> </form> <?php if (isset ($stores)) { if (!empty ($stores)) { echo '<p><strong>' . count ($stores) . ' results were found.</strong></p>'; foreach ($stores as $value) { echo '<p><strong>' . $value['name'] . '</strong><br />'; echo $value['address'] . '<br />'; echo $value['town'] . ', ' . $value['state'] . ' ' . $value['postal']; echo ' <a target="_blank" href="http://maps.google.com/maps?q=', $value['address'], ' ', $value['town'], ', ', $value['state'], ' ', $value['postal'], '">Map this location</a><br />'; echo 'Phone: ' . $value['phone'] . '<br />'; echo 'Hours: ' . $value['hours']; echo '</p>'; } } else { echo '<p><strong>No results found</strong></p>'; } } ?> </body> </html> Thanks, I'm working on another issue, that I just posted about, if you have any insight into that as well. Quote Link to comment 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.