Form Submit

[timmah1]

Can anybody see as to why, if any, this form takes forever to execute?

<?php
if(isset($_POST['submitted'])){
    $z = $_POST['zipcode'];
    $r = $_POST['radius'];
    
    $sql = mysql_query("SELECT DISTINCT m.LocAddZip, m.MktName,m.LocAddSt,m.LocAddCity,m.LocAddState,m.x1,m.y1,z1.lat,z2.long
    FROM mrk m, zip z1, zip z2
    WHERE m.LocAddZip = z1.zipcode
    AND z2.zipcode = $z
    AND ( 3963 * acos( truncate( sin( z2.lat / 57.2958 ) * sin( z1.lat / 57.2958 ) + cos( z2.lat / 57.2958 ) * cos( z1.lat / 57.2958 ) * cos( z1.long / 57.2958 - z2.long / 57.2958 ) , 8 ) ) ) <= $r ")
    
    or die(mysql_error());
        while($row = mysql_fetch_array( $sql )) {
            $store = $row['MktName']."<br />";
            $store .= $row['LocAddSt']."<br />";
            $store .= $row['LocAddCity'].", ".$row['LocAddState']." ".$row['LocAddZip'];
            $lat1 = $row['lat'];
            $lon1 = $row['long'];
            $lat2 = $row['y1'];
            $lon2 = $row['x1'];
            $dis = distance($lat1, $lon1, $lat2, $lon2);
     
            echo "<p>".$store."</p>";        
            echo ceil($dis) . " mile(s) away";
            echo "<hr/>";
        }
}
?>

 

I get a timeout error sometimes, and sometimes I don't.

Is it the form, or would it be on the server?

 

Thanks in advance

[Psycho]

My first guess would be your WHERE clause. That's an awful lot of complex mathematics that you are asking the Database server to do just to determine if the record should be in the result set. Try removing that last condition and see if the performance improves. If so, then you know that is the cause.

 

If that is the cause, you could try indexing the fields used in those calculations. I know that indexing improves performance for fields used in lookups and joins, but not sure if it would help when the value is used in match functions or not.

[timmah1]

Thank you Psycho, indexing made a WORLD of difference!

 

Now, not sure if you know this or not, but the calculations for the distance are off.

 

I'm using

function distance($lat1, $lon1, $lat2, $lon2)
{
$lat1 = floatval($lat1);
$lon1 = floatval($lon1);
$lat2 = floatval($lat2);
$lon2 = floatval($lon2);

// Formula for calculating distances
// from latitude and longitude.
$dist = acos(sin(deg2rad($lat1))
* sin(deg2rad($lat2))
+ cos(deg2rad($lat1))
* cos(deg2rad($lat2))
* cos(deg2rad($lon1 - $lon2)));


$dist = rad2deg($dist);
$miles = (float) $dist * 69.1;

// To get kilometers, multiply miles by 1.61
$km = (float) $miles * 1.61;

// This is all displaying functionality
$display = sprintf("%0.2f",$miles);

//$display = sprintf("%0.2f",$miles).' miles' ;
//$display .= ' ('.sprintf("%0.2f",$km).' kilometers)' ;

return $display ;
}

 

Then I'm calling it like:

$lat1 = $row['lat'];
            $lon1 = $row['long'];
            $lat2 = $row['y1'];
            $lon2 = $row['x1'];
            $dis = distance($lat1, $lon1, $lat2, $lon2);

 

Do you, or anybody else, notice what the problem might be with the distance?

 

Thanks

 

 

[Psycho]

I recently posted in a thread here with a link to a page that provided those calculations. Was that your thread?

 

I am definitely no expert in calculating distances using latitude and longitude, so I suggest looking for the appropriate resources. Not really a PHP question though.