PHP MySQL 邮政编码到纬度/经度然后按距离查询排序不起作用

Question

我有以下两个查询，第一个查询从用户输入的上一页中获取(发布的)邮政编码，并在邮政编码数据库中找到相应的纬度和经度。第二个查询应该获取该数据并按距离对 100 英里范围内的任何内容进行排序。

它不起作用，尽管网页加载正常，但我没有返回任何结果，即使我尝试在数据库中添加邮政编码也是如此。谁能看出我做错了什么？

$colname_LOCATION = "-1";
if (isset($_POST['ZIPCODE'])) {
$colname_LOCATION = $_POST['ZIPCODE'];  //Zip code taken from customer form on previous    page
}
mysql_select_db($database_XMASTREE, $XMASTREE); // find lat and lon from fields    inZIP_FULL zipcode database
$query_LOCATION = sprintf("SELECT * FROM ZIP_FULL WHERE zip_code = %s",     GetSQLValueString($colname_LOCATION, "int")); 
$LOCATION = mysql_query($query_LOCATION, $XMASTREE) or die(mysql_error());
$row_LOCATION = mysql_fetch_assoc($LOCATION);
$totalRows_LOCATION = mysql_num_rows($LOCATION);

$lat = $row_LOCATION['latitude']; // code said to change $lat with a number so hopefully this will do it 
$lon = $row_LOCATION['longitude']; // code said to change $lon with a number so hopefully this will do it

mysql_select_db($database_XMASTREE, $XMASTREE);
$query_DIST = "SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat   * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60  * 1.1515) AS distance 
FROM MEMBERS 
HAVING distance <= 100
ORDER BY distance ASC";
$DIST = mysql_query($query_DIST, $XMASTREE) or die(mysql_error());
$row_DIST = mysql_fetch_assoc($DIST);
$totalRows_DIST = mysql_num_rows($DIST);

Answer 1

我修改了上周刚创建的代码，应该可以满足您的要求。您的 MySQL 代码一目了然。

$colname_LOCATION = "-1";
if (isset($_POST['ZIPCODE'])) {
$colname_LOCATION = $_POST['ZIPCODE'];  //Zip code taken from customer form on previous    page
}

try {
    $pdo = new PDO("$dbInfo[type]:host=$dbInfo[host];dbname=$dbInfo[dbname]", $dbInfo['username'], $dbInfo['password']);
    $query_LOCATION = "SELECT * FROM ZIP_FULL WHERE zip_code = :zip_code";
    $stmt->bindParam(':zip_code', $colname_LOCATION, PDO::PARAM_INT);
    $stmt->execute();
    $row_LOCATION = $stmt->fetch(PDO::FETCH_ASSOC);
    $lat = $row_LOCATION['latitude'];
    $lon = $row_LOCATION['longitude'];

    $bounding_distance = 10; //$bounding_distance is an easy way to narrow down the search, without MySQL doing all of the distance calcs  
    $sql = "SELECT *
            ,((ACOS(SIN(:find_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(:find_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((:find_long - `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.15078) AS `distance` 
            FROM `MEMBERS` 
            WHERE
                `lat` BETWEEN (:find_lat - :bounding_distance) AND (:find_lat + :bounding_distance)
                AND `lon` BETWEEN (:find_long - :bounding_distance) AND (:find_long + :bounding_distance)
                AND `distance` <= 100
            ORDER BY `distance` ASC;"; 
    $stmt = $pdo->prepare($sql);

    $stmt->bindValue(':find_lat', $lat);
    $stmt->bindValue(':find_long', $lon);
    $stmt->bindValue(':bounding_distance', $bounding_distance);

    $stmt->execute();
    $row_DIST = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $totalRows_DIST = count($row_DIST);
    $pdo = NULL;
}
catch(PDOException $e) {
    pdo_error($e,$sql);
}