PHP代码按距离排列记录

I tried the following method which worked for my old database structure

我尝试了以下方法,该方法适用于我的旧数据库结构

$lat = '-27.560263394765027';
$long = '153.08231055736542'; 
 $query = "SELECT * , ((
    ACOS( SIN( ".$lat." * PI( ) /180 ) * SIN( lat * PI( ) /180 ) + COS( ".$lat." * PI( ) /180 ) * COS( lat * PI( ) /180 ) * COS( (
     ".$long." - lon) * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) AS `distance` FROM `km_stores` HAVING `distance` <=100 ORDER BY distance LIMIT 0 , 10";

And, I am trying to apply the same principle and find the nearest 30 stores within maximum 100km radius and this time my database structure is entirely different and I need to run set of sql queries to get the required output. My queries are

并且,我正在尝试应用相同的原则,并在最大100km半径内找到最近的30个商店,这次我的数据库结构完全不同,我需要运行一组sql查询来获得所需的输出。我的疑问是

$select_resname = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-name' LIMIT 0 , 30");
$select_resdesc = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-description' LIMIT 0 , 30");
$select_reslat = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-latitude' LIMIT 0 , 30");
$select_reslong = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-longitude' LIMIT 0 , 30");
while(($fetch_resname = mysql_fetch_array($select_resname)) && ($fetch_resdesc = mysql_fetch_array($select_resdesc)) && ($fetch_reslat = mysql_fetch_array($select_reslat)) && ($fetch_reslong = mysql_fetch_array($select_reslong))){
    // Print the output
}

In this case how can I arrange records as per GEO location?

在这种情况下,我如何根据GEO位置安排记录?

1 个解决方案

#1

$select_resname = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-name' LIMIT 0 , 30");
$select_resdesc = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-description' LIMIT 0 , 30");
$select_reslat = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-latitude' LIMIT 0 , 30");
$select_reslong = mysql_query("SELECT * FROM  `wp_cf7dbplugin_submits` WHERE  `form_name` =  'Add Store' AND  `field_name` =  'store-longitude' LIMIT 0 , 30");

$resultAreas          = array();
while(($fetch_resname = mysql_fetch_array($select_resname)) && ($fetch_resdesc = mysql_fetch_array($select_resdesc)) && ($fetch_reslat = mysql_fetch_array($select_reslat)) && ($fetch_reslong = mysql_fetch_array($select_reslong))){
    $areaResult1      = @$fetch_resname["area_field"];
    $areaResult2      = @$fetch_resdesc["area_field"];
    $areaResult3      = @$fetch_reslat["area_field"];
    $areaResult4      = @$fetch_reslong["area_field"];

    if($areaResult1!="")
    {
        $resultAreas[]= $areaResult1;
    }
    if($areaResult2!="")
    {
        $resultAreas[]= $areaResult2;
    }
    if($areaResult3!="")
    {
        $resultAreas[]= $areaResult3;
    }
    if($areaResult4!="")
    {
        $resultAreas[]= $areaResult4;
    }
}
sort($resultAreas);
print_r($resultAreas); // prints you the sorted result