homepage Welcome to WebmasterWorld Guest from 54.205.241.107
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Opinion on whether to create addition table or update existing table.
nelsonm

5+ Year Member



 
Msg#: 4232877 posted 11:59 pm on Nov 20, 2010 (gmt 0)

I have a US zipcode database table that's used to select zipcodes in a business table that are within a 20 mile radius of the base zipcode that the user enters.

The data is used to calculate the distance between the base zipcode and all the selected business table zipcodes, is in US zipcode table.

I want to be able to sort on business table zipcode and distance before displaying, unfortunately, the distance is not stored in any table because it has to be calculated.

So... my question is... is it more correct to create a separate distance table to store and point the calculated distance (from the base zipcode to the selected zipcode) to the proper record in the business table. Then join the two tables for sorting before displaying.

or

Is it better to just add a distance field to the business table then update each business table record with the calculated distance. Then i can sort the business table zipcode and distance fields before displaying.

Either way, the distance field in either the distance or business table will have to cleared every time the user enters a new base zipcode.

What do you think?

 

lammert

WebmasterWorld Senior Member lammert us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4232877 posted 10:05 am on Nov 29, 2010 (gmt 0)

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? If it is an expression in your SQL query, you should be able to sort on the distance in one step, without either creating or updating a distance field in the database.

Updating the distance parameter in the business table may be dangerous, if two visitors enter a search query for a specific zip code at almost the same time. The two queries might overwrite each others distance values, returning wrong distance results to one or both visitors.

nelsonm

5+ Year Member



 
Msg#: 4232877 posted 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,

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved