2 Part Question | Select Zipcode Radius & Transfer To Different Page

spacepoet · December 6, 2012

Hello:

I am trying to figure out how to select all the cities/zipcodes in a radius - say 20 miles - with a zipcode set to a certain one.

Say it is 19128 - a Philadelphia zipcode.

For now, I just want to use a SELECT Statement, not allowing a user to enter the zipcode and select the mile radius himself or herself.

This is my Zipcode database set-up"

CREATE TABLE `zip_codes` (
`zip_id` int(11) NOT NULL auto_increment,
`zip` varchar(5) NOT NULL default '',
`lat` varchar(10) NOT NULL default '',
`lon` varchar(10) NOT NULL default '',
`city` varchar(50) default NULL,
`full_state` varchar(50) default NULL,
`abbr_state` char(2) NOT NULL default '',
PRIMARY KEY (`zip_id`),
UNIQUE KEY `zip_id` (`zip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41833 ;

--
-- Dumping data for table `zip_codes`
--

INSERT INTO `zip_codes` VALUES(1, '00501', '40.92233', '-72.63708', 'HOLTSVILLE', 'NEW YORK', 'NY');

ETC....

I have it in my sitemap.php file (which using mode re-write becomes sitemap.html):

<?

echo "<ul class='site-map-ul'>";

$query2 = "SELECT zip_id,lat,lon,city,abbr_state,zip FROM zip_codes WHERE abbr_state ='PA'";
$result2 = mysql_query($query2);
$num_rows2 = mysql_num_rows($result2);

for($j=0;$j<$num_rows2;$j++)
{
$row2 = mysql_fetch_array($result2);
$zip_id = $row2['zip_id'];
$city = $row2['city'];
$abbr_state = $row2['abbr_state'];
$zip = $row2['zip'];

$city = ucwords(strtolower($city));

echo "<li class='site-map-ul-li'><a href='$zip_id.$city.$abbr_state.$zip.php?zip_id=$zip_id&city=$city&abbr_state=$abbr_state&zip=$zip' title='".$city." ".$abbr_state.", ".$zip."'>".$city." ".$abbr_state.", ".$zip."</a></li>";

}

echo "</ul>";

?>

It is currently getting just records for the state of PA, but I want to use a zipcode ..

How do I do this .. ??

Then, I would like to build pages based on the records like:

<a href="232432.Philadelphia.PA.19128.html" title="Philadelphia PA,19128">Philadelphia PA,19128</a>

which it does, but I have having difficulty trying to get it to transfer to the "proper" site page, myPage.php, which I write into pages based on the page id and myPageURL. I am doing this with an .htaccess file.

myPage.php:

<?php

include("include/db.php");

$con = mysql_connect($db_host, $db_user, $db_password);

mysql_select_db($db_table);

$zip_id = $_REQUEST['zip_id'];
$city = $_REQUEST['city'];
$abbr_state = $_REQUEST['abbr_state'];
$zip = $_REQUEST['zip'];

$id = $_GET['id'];

$myPageURL = "";
$myTitle = "";
$myDesc = "";
$myHeader = "";
$mySubHeader = "";
$myPageContent = "";
$myReadMore = "";
$myPageContentMore = "";
$myBannerContent = "";
$photo = "";


if(isset($_GET)) {
if($id!="") {
$query = "SELECT id, myTitle, myDesc, myHeader, mySubHeader, myPageContent, myReadMore, myPageContentMore, myBannerContent, photo_filename FROM myPageData WHERE id = $id";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
if($num_rows>0) {
$row = mysql_fetch_array($result);
$continue = true;
$myTitle = $row['myTitle'];
$myDesc = $row['myDesc'];
$myHeader = $row['myHeader'];
$mySubHeader = $row['mySubHeader'];
$myPageContent = $row['myPageContent'];
$myReadMore = $row['myReadMore'];
$myPageContentMore = $row['myPageContentMore'];
$myBannerContent = $row['myBannerContent'];
$photo = $row['photo_filename'];
}
}
}

<html>

<meta name="description" content="<? echo $myDesc; ?>" />


<title><? echo $myTitle; ?></title>

...

</html>

.htaccess

# Re-writes from myPage.php?id=$id&myPageURL=$myPageURL to 1.This-Is-A-Page.html
RewriteRule ^([0-9]+).([a-zA-Z0-9\-]+)\.html$ myPage.php?id=$1&myPageURL=$2 [L]

I was trying something like this:

RewriteRule ^([0-9]+).([a-zA-Z0-9\-]+).([a-zA-Z0-9\-]+).([0-9]+)\.html?$ sitemap.php?zip_id=$1&city=$2&abbr_state=$3&zip=$4 [NC,L]

RewriteRule ^([0-9]+).([a-zA-Z0-9\-]+).([0-9]+).([a-zA-Z0-9\-]+).([a-zA-Z0-9\-]+).([0-9]+)\.html$ MyPage.php?id=$1&myPageURL=$2&zip_id=$3&city=$4&abbr_state=$5&zip=$6 [NC,L]

But that is not working at all ...

Anyone have any ideas on one or both of these issues .. ??

I am sure I will want to refine this as I progress but for now I would be very happy to get it working as I listed. My idea is to eventually add a new field to the "zip_codes" table called "search_term", and assign unique terms to all zipcodes in the radius:

Select all for first term:

<a href="Music.Lessons.232432.Philadelphia.PA.19128.html" title="Music Lessons Philadelphia PA,19128">Music Lessons Philadelphia PA,19128</a>
<a href="Music.Lessons.232433.Philadelphia.PA.19127.html" title="Music Lessons Philadelphia PA,19127">Music Lessons Philadelphia PA,19127</a>
Etc..

Then do a new term:

<a href="Guitar.Lessons.232432.Philadelphia.PA.19128.html" title="Guitar Lessons Philadelphia PA,19128">Guitar Lessons Philadelphia PA,19128</a>
<a href="Guitar.Lessons.232433.Philadelphia.PA.19127.html" title="Guitar Lessons Philadelphia PA,19127">Guitar Lessons Philadelphia PA,19127</a>
Etc..

Any ideas .. ??

Thanks, as always!

Barand · December 6, 2012

This link should assist in searching for other locations in the same area

http://www.meridianworlddata.com/Distance-Calculation.asp

spacepoet · December 6, 2012

Thank you for the tip .. I will look at it .. I have found many examples but wanted to see if anyone on here may have done this before and had some PHP code examples ..

Barand · December 7, 2012

I could have given more help but

--

-- Dumping data for table `zip_codes`

--

INSERT INTO `zip_codes` VALUES(1, '00501', '40.92233', '-72.63708', 'HOLTSVILLE', 'NEW YORK', 'NY');

ETC....

didn't give much to work with.

spacepoet · December 7, 2012

I am trying this:

<?

echo "<ul class='site-map-ul'>";

$radius = 20;
$zip = "19128";
$query2 = "SELECT zip_id,lat,lon,city,abbr_state,zip FROM zip_codes WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius)";

$result2 = mysql_query($query2);
$num_rows2 = mysql_num_rows($result2);

for($j=0;$j<$num_rows2;$j++)
{
$row2 = mysql_fetch_array($result2);
$zip_id = $row2['zip_id'];
$lat = $row2['lat'];
$lon = $row2['lon'];
$city = $row2['city'];
$abbr_state = $row2['abbr_state'];
$zip = $row2['zip'];

$city = ucwords(strtolower($city));

echo "<li class='site-map-ul-li'><a href='$zip_id.$city.$abbr_state.$zip.php?zip_id=$zip_id&city=$city&abbr_state=$abbr_state&zip=$zip' title='".$city." ".$abbr_state.", ".$zip."'>".$city." ".$abbr_state.", ".$zip."</a></li>";

}

echo "</ul>";

?>

But not working ..

Getting the error:

[b]Warning[/b]: mysql_num_rows() expects parameter 1 to be resource, boolean given in [b]/home/content/55/9880955/html/sitemap.php[/b] on line [b]70[/b]

Line 70:

$num_rows2 = mysql_num_rows($result2);

I assumed it would see the zipcode is 19128, and find all zipcodes within a 20 mile radius of it ..

Apparently not ...

Edited December 7, 2012 by spacepoet

Barand · December 7, 2012

Check what mysql_error() gives after running the query

spacepoet · December 7, 2012

Probably a very dumb question on my part, but how do I do that ..

Barand · December 7, 2012

echo is good

$result2 = mysql_query($query2);
if (!result2) echo mysql_error();

Sign In

2 Part Question | Select Zipcode Radius & Transfer To Different Page

Recommended Posts

spacepoet

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

spacepoet

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information