i'm attempting to get a 'dealer' locator working based on a user entered zip code and distance constraint.
I have two tables, zipdata (which has zip data) and epdb (which has dealer name, addrss, zip, etc).
Right now my query seems to be grabbing the first lat/long in the zipdata table and using that, rather than finding the correct lat/long for each dealer, and continuing the calculations. I'm a bit confused on how to acheive this.
If anyone could give me some hints/guide me a bit it'd be very much appreciated.
I've based a lot of this on a post I found while searching this forum: http://www.phpbuilder.com/board/showthread.php?threadid=10290629&highlight=zip+code+locator which has a lot of useful information, but I need additional help.
<?php
include 'dbconnect.php';
$process = $_SERVER['PHP_SELF'];
if(!isset($_POST['submit'])) {
echo ("
<form action='$process' method='post' name='enterZip'>
<table border=0>
<tr>
<td colspan='2'>Enter Zip Code and select mileage constraint.</td>
</tr>
<tr>
<td align='right'>Zip Code:</td>
<td><input type='text' name='ZipCode1' value='95641' size=4 maxlength=5></td>
</tr>
<tr>
<td align='right'>Mileage:</td>
<td><select name='mileage'>
<option value='25' selected>25 miles</option>
<option value='50'>50 miles</option>
<option value='75'>75 miles</option>
<option value='100'>100 miles</option>
<option value='10000' selected>more than 100 miles</option>
</select></td>
</tr>
<tr>
<td> </td>
<td><input type='submit' name='submit' value='Submit'></td>
</tr>
</table>
");
}
else {
// retreive from zipdata table the latitude/longitude for provided zip code (ZipCode1)
$ZipCodeSupplied1 = $_POST['ZipCode1'];
$query1 = ("
SELECT ZipDataID,
ZipCode,
Latitude,
Longitude
FROM zipdata
WHERE ZipCode LIKE $ZipCodeSupplied1;
");
$resultQuery1 = mysql_query($query1) or die (mysql_error());
if ($row = mysql_fetch_array($resultQuery1)) {
$Latitude = $row["Latitude"];
$Longitude = $row["Longitude"];
$mileage = $_POST['mileage'];
echo ("<p>Query Information:<br>Zip Code: $ZipCodeSupplied1<br>Lat: $Latitude, Long: $Longitude<br>Mileage Constraint: $mileage"); // query 1 info
$LatitudeR = deg2rad($Latitude);
$LongitudeR = deg2rad($Longitude);
// echo ("<p>Radian Lat: $LatitudeR and Long: $LongitudeR"); // test only - for radian values
}
$StZipCode = substr($ZipCodeSupplied1,0,3);
$query2 = ("
SELECT epdb.CN,
epdb.epdbID,
epdb.Branch,
epdb.CompanyName,
epdb.FirstName,
epdb.LastName,
epdb.Email,
epdb.Address,
epdb.City,
epdb.State,
epdb.Zip,
epdb.AreaPhone,
epdb.Phone,
epdb.Phone1,
epdb.AreaFax,
epdb.Fax,
epdb.Fax1,
zipdata.Latitude AS Latitude,
zipdata.Longitude AS Longitude,
((ACOS((SIN($LatitudeR) * SIN(Latitude/57.2958)) + (COS($LatitudeR) * COS(Latitude /57.2958) * COS(Longitude/57.2958 - $LongitudeR)))) * 3963) AS Distance
FROM zipdata, epdb
WHERE epdb.Zip LIKE '$StZipCode%' AND ((ACOS((SIN($LatitudeR) * SIN(Latitude /57.2958)) + (COS($LatitudeR) * COS(Latitude /57.2958) * COS(Longitude/57.2958 - $LongitudeR)))) * 3963) < $mileage
GROUP BY epdb.CN
ORDER BY Distance ASC LIMIT 5;
");
$resultQuery2 = mysql_query($query2) or die (mysql_error());
echo ("
<table border=0>
<tr>
<td><hr></td>
</tr>
");
while ($row = mysql_fetch_array($resultQuery2)) {
$pID = $row["epdbID"];
$Branch = $row["Branch"];
$CN = $row["CN"];
$CompanyName = $row["CompanyName"];
$FirstName = $row["FirstName"];
$LastName = $row["LastName"];
$Email = $row["Email"];
$Address = $row["Address"];
$City = $row["City"];
$State = $row["State"];
$ZipCode = $row["Zip"];
$AreaPhone = $row["AreaPhone"];
$Phone = $row["Phone"];
$Phone1 = $row["Phone1"];
$AreaFax = $row["AreaFax"];
$Fax = $row["Fax"];
$Fax1 = $row["Fax1"];
$Distance = $row["Distance"];
$Latitude2 = $row["Latitude"]; // for testing
$Longitude2 = $row["Longitude"]; // for testing
$DistanceR = substr($Distance,0,4);
echo ("
<tr>
<td><b>$CompanyName (#$CN)</b>
<br>$FirstName $LastName
<br>$Address, $City, $State $ZipCode ($StZipCode)
<br>($AreaPhone) $Phone-$Phone1 PHONE, ($AreaFax) $Fax-$Fax1 FAX
<br><a href='mailto:$Email'>$Email</a> E-mail
<br>Mileage: $DistanceR (Lat: $Latitude2, Long: $Longitude2 )</td>
</tr>
<tr>
<td><hr></td>
</tr>
");
}
echo ("
</table>
");
}
?>