PHP / Mysql - 使用临时表时的 ORDER BY

Question

我一直在尝试使用 ORDER BY 和 LIMIT 使其工作，但它会输出输入的所有内容。 ORDER BY 和 LIMIT 似乎不起作用：

$lat1 = 37.349418;
$lon1 = -121.896286;
$distance = 25;


$q = "SELECT * FROM cityname WHERE feature_class = 'A' OR feature_class = 'P'";
$r = mysql_query($q) or die(mysql_error());
while ($row = mysql_fetch_array($r)) {
$lat = trim($row["latitude"]);
$lon = trim($row["longitude"]);
$name = $row["name"];
$pop = $row["population"];
$miles = distance($lat, $lon, $lat1, $lon1, "m");
$milesrounded = round($miles, 2);
if ($miles < $distance) {
    if ($pop > 0) {
        $q2 = "CREATE TEMPORARY TABLE IF NOT EXISTS `templocation4` (
        `cityname` varchar(75) NOT NULL,
        `distance` double NOT NULL
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1";
        $r2 = mysql_query($q2) or die(mysql_error());
        $q1 = "INSERT INTO templocation4 (cityname, distance) VALUES ('$name', '$milesrounded')";
        $r1 = mysql_query($q1) or die(mysql_error());
        $q3 = "SELECT MIN(distance) FROM templocation4 GROUP BY distance DESC LIMIT 10";
        $r3 = mysql_query($q3) or die(mysql_error());
        while ($row1 = mysql_fetch_array($r3)) {
            echo $row1["cityname"];
            echo " ";
            echo $row1["distance"];
            echo "<br>";
        }
        $q5 = "DROP TABLE templocation4";
        $r5 = mysql_query($q5) or die(mysql_error());
            }
        }
}

表 cityname 有 > 250K 个条目，我试图根据用户输入的纬度/经度将其排序到最接近的城市名称。

谢谢。

Answer 1

这里有一个错误：

$q3 = "SELECT MIN(distance) FROM templocation4 GROUP BY distance DESC LIMIT 10";
$r3 = mysql_query($q3) or die(mysql_error());

while ($row1 = mysql_fetch_array($r3)) {
    echo $row1["cityname"];   // Where is cityname defined???
    echo " ";
    echo $row1["distance"];   // Where is distance defined???
    echo "<br>";
}

$r3 只有一个结果列（您没有给出名称）。您确定使用了正确的变量吗，因为您的代码不可能像您发布的那样工作。

另外你的变量命名真的很糟糕。使用有意义的名称而不是 $q1、$q2 等...

Answer 2

请再次与 Logic 确认。 “GROUP BY distance”将从关注记录列表中返回单个记录。例如，如果 10 英里距离内有 100 个用户，您的查询将获取 10 英里内找到的第一个记录。这不会返回所有 100 条记录。

    $q1 = "INSERT INTO templocation4 (cityname, distance) VALUES ('$name', '$milesrounded')";
    $r1 = mysql_query($q1) or die(mysql_error());
    $q3 = "SELECT MIN(distance) FROM templocation4 GROUP BY distance DESC LIMIT 10";
    $r3 = mysql_query($q3) or die(mysql_error());

需要根据您的需求和逻辑重新设计。

Answer 3

//determine distance function
function distance($lat, $lon, $lat1, $lon1, $unit) {

    $theta = $lon - $lon1;
    $dist = sin(deg2rad($lat)) * sin(deg2rad($lat1)) +  cos(deg2rad($lat)) * cos(deg2rad($lat1)) * cos(deg2rad($theta)); 
    $dist = acos($dist);
    $dist = rad2deg($dist);
    $miles = $dist * 60 * 1.1515;
    $unit = strtoupper($unit);

    if ($unit == "K") {
        return ($miles * 1.609344);
    } else if ($unit == "N") {
        return ($miles * 0.8684);
    } else {
        return $miles;
    }
}

//sample latitude for testing purposes
$lat1 = 37.349418;
//sample longitude for testing purposes
$lon1 = -121.896286;
//sample distance for testing purposes
$distance = 25;

//query to select only a or p feature class
$query = "SELECT * FROM cityname WHERE feature_class = 'A' OR feature_class = 'P'";
$result = mysql_query($query) or die(mysql_error());
//create the temporary table - if it does not exist
$createtemporarytablequery = "CREATE TEMPORARY TABLE IF NOT EXISTS `templocation4` (
`cityname` varchar(75) NOT NULL,
`distance` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1";
$resultofcreatetemporarytablequery = mysql_query($createtemporarytablequery) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    //gets latitude of city in database
    $lat = trim($row["latitude"]);
    //gets longitude of city in database
    $lon = trim($row["longitude"]);
    //gets cityname
    $name = $row["name"];
    //gets population of aforementioned cityname
    $pop = $row["population"];
    //determines distance from sample latitude and longitude from the latitude and longitude of cities in the cityname database
    $miles = distance($lat, $lon, $lat1, $lon1, "m");
    //round the miles to the 2nd decimal place
    $milesrounded = round($miles, 2);
    //determine if the city meets the request
    if ($miles < $distance) {
        //make sure its a populated city
            if ($pop > 0) {
                //insert stuff into temporary table
                $insertstuffintotemporarytable = "INSERT INTO templocation4 (cityname, distance) VALUES ('$name', '$milesrounded')";
                $resultofinsertstuffintotemporarytable = mysql_query($insertstuffintotemporarytable) or die(mysql_error());
            }
    }   
}   
//retrieve the closest 10 cities from the temporary table
$retrieve10closestcities = "SELECT * FROM templocation4 GROUP BY distance ASC LIMIT 10";
$resultofretrieving10closestcities = mysql_query($retrieve10closestcities) or die(mysql_error());
//determine how many results there are
$numrows = mysql_num_rows($resultofretrieving10closestcities);
//are there more than 0 results?
if ($numrows > 0) {
    //loops through array
    while ($row1 = mysql_fetch_array($resultofretrieving10closestcities)) {
        echo $row1["cityname"];
        echo " ";
        echo $row1["distance"];
        echo "<br>";
    }
} else {
    //echos no results found
    echo "No results found";
}
//drop temporary table
$droptable = "DROP TABLE templocation4";
$resultofdroptable = mysql_query($droptable) or die(mysql_error());

答案是：

San Jose 0.7
Buena Vista 2.24
Burbank 2.65
Santa Clara 3.25
Fruitdale 3.33
Alum Rock 3.97
East Foothills 4.85
Campbell 5.21
Seven Trees 5.41
Milpitas 5.48