Page is a not externally linkable
nelsonm - 5:12 pm on Nov 29, 2010 (gmt 0)
thanks for responding,
Do you calculate the distance between the zip codes in a scripting language (PHP, Perl, ASP, etc) or with an expression in your SQL query?
The distance calculation comes from a php function that's called after each selection of a zipcode. I also went ahead and created a separate distance table that was linked to the id of the matching zipcode in the business table. However, i did not think i could do the calulcation as an expression in the sql query. I'll look into it.
The following is the section of php code that gets the required data to calculate the miles and insert the distance into the distance table. Then the distance table is accessed again to match and get records in the business table.
<?php
// truncate distance table.
$sql= "TRUNCATE TABLE distance";
$disResult= mysql_query($sql, $conn) or die("<br>** Unable to truncate distance database table <b>".mysql_error()."</b><br>$sql");
$disRows= mysql_affected_rows($conn);
if (!$disResult){
echo "<br>** Unable to truncate distance database table. disResults[$disResults], disRows[$disRows]";
exit;
}
// Get latitudinal and longitudinal data from selected zip codes in zipcode table to calculate distance between the base zip code and the selected zip codes.
while ($i < $chrRows) {
$chrZipCode= mysql_result($chrResult,$i,"ZipCode");
$sql= "SELECT * FROM zipcodes WHERE ZipCode = '". $chrZipCode ."'";
$zipResult= mysql_query($sql, $conn)or die("<br>** Unable to query local database table for base zip code data <b>". mysql_error() ."</b><br>$sql");
//echo "<br>** zipcodes SQL statment: $sql";
$zipResult_ar= mysql_fetch_assoc($zipResult);
$zipLatitude= $zipResult_ar['Latitude'];
$zipLongitude= $zipResult_ar['Longitude'];
$chrCID= mysql_result($chrResult,$i,"CID");
$chrMercyCode= mysql_result($chrResult,$i,"MercyCode");
// calculate the distance and mercycode restrictions.
$chrDistance= number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "M"), 2);
$mercyTest= $mercyMask & $chrMercyCode; if ($mercyTest == '00000000000000') $mercyTest = "";
//echo "<br>** [$i -- $chrRows], [$chrDistance -- $miles], mercyTest[$mercyTest] = mercyMask[$mercyMask] & chrMercyCode[$chrMercyCode]";
// store pointer to all charities table records that meet the distance and mercycode restrictions.
if (($chrDistance <= $miles) and ($mercyTest)) {
$sql= "INSERT INTO distance (Miles, CID) VALUES ('$chrDistance', '$chrCID')";
$disResult= mysql_query($sql, $conn)or die("<br>** Unable to insert local database table for distance data <b>". mysql_error() ."</b><br>$sql");
$disRows= mysql_affected_rows($conn);
if (!$disResult){
echo "<br>** distance table insert failed. disResults[$disResults], disRows[$disRows]";
exit;
}
}
$i++;
}
// select all records in the distance table and use the CID pointer to get matching business records in the charities table.
$sql= "SELECT * FROM distance ORDER BY Miles ASC";
$disResult= mysql_query($sql, $conn) or die("<br>** Unable to query distance database table <b>".mysql_error()."</b><br>$sql");
$disRows= mysql_num_rows($disResult);
//echo "<br>** distance table base query, $disRows records found.";
if ($disRows < 1){
echo "<br>** distance table base query failed.";
}else{
$i= 0;
$sql= "SELECT * FROM charities WHERE ";
// get all records from the charities table that match the distance CID and meet the merctcode restrictions.
while ($i < $disRows) {
$disCID[$i]= mysql_result($disResult,$i,"CID");
$sql = $sql."(CID = '".$disCID[$i]."')";
$i++;
if ($i < $disRows) $sql = $sql." OR ";
}
$sql= $sql." AND (ZipCode <> '')";
$chrResult= mysql_query($sql, $conn) or die("<br>** Unable to query charities database table for zip codes <b>".mysql_error()."</b><br>$sql");
$chrRows= mysql_num_rows($chrResult);
//echo "<br>** distance SQL statment: $sql";
}
?>
In any case, you did bring up one aspect i had not considered...
The distance table is the only table that is emptied and populated every time a user makes a zipcode query. Right now there is a maximum of about 200 records being inserted/truncated.
While this action is occurs in milliseconds, how do i insure that each web page user either; does not access the distance table while its being used, or every user gets their own instance of the table?
thanks,