Jump to content

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

spacepoet

By spacepoet
in PHP Coding Help

Recommended Posts

spacepoet Advanced Member

spacepoet

Posted
Posted

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 Prolific Member

Barand

Posted
Posted

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 Advanced Member

spacepoet

Posted
  • Author
Posted

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 ...

 

:(

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.