duanmorong9597
duanmorong9597
采纳率100%
2015-06-23 01:58 阅读 26
已采纳

如何在PHP PDO中基于经度,纬度和半径从MYSQL中提取数据?

I'm developing an application where it needs to show results based on the person longitude, latitude and user selected radius, then return the result in Json array format json_encode();

I have 3 files in my project as sdbconnection.php, manipulate.php and pull.php. Below I have explained how exactly each file works.

sdbconnection.php is my server and database connection.

//MySQL Database Connection Credential
class connection {
    public static $host = "XXXXX";
    public static $user = "XXXXXXX";
    public static $password = "XXXXXXX";
    public static $database = "XXXXXXX";
    public static $port = "XXXXX";
}

manipulate.php is how I exccute my SQL query and return the result. I have tried to figure out how to send requests from google tutorial, but i believe that I'm missing something since they are not using the PDO connection.

public function pullResults($longitude, $latitude, $radius){
    $retunValue = array();
    $sql = "SELECT title, latitude, longitude, ( 3959 * acos( cos( radians(:lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:lng) ) + sin( radians(:lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM posts HAVING distance < :radius ORDER BY distance LIMIT 0 , 20";

    $result = $this->connection->query($sql);
        if ($result != null && (mysqli_num_rows($result) >= 1)) {
            $row =  $result->fetch_array(MYSQLI_ASSOC);
            if (!empty($row)) {
                $retunValue = $row;
            }
        }

    return $retunValue;
}

pull.php is where users request to execute their respond and when i try it in browser, I get Blank page.

//Database Connection and Query Manipulation.
require("handles/sdbconnection.php");
require("handles/manipulate.php");

// Get parameters from URL
$latitude = $_GET["latitude"];
$longitude= $_GET["longitude"];
$radius = $_GET["radius"];
$category = htmlentities($_GET["category"]);

//Connect to database
$databaseAccess = new manipulate();
$databaseAccess->openConnection();

//Get the Result
$databaseAccess->pullResults($longitude, $latitude, $radius);

//Close Connection After Data Pulled
$databaseAccess->closeConnection();

Also, if you see, I'm trying to be more specific in my MYSQL query by retrieving data by providing multiple categories ($category = htmlentities($_GET["category"]);), I really appreciate if you show me how to do that as well.

Procedure: enter image description here

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    douxiao0400 douxiao0400 2015-06-23 17:14

    "PDO" and "mysqli_*" are two separate APIs. Do not mix them.

    pullResults has return $retunValue;, yet the call to it throws away the return value.

    $string = json_encode($retunValue);
    

    will product a string, that you can echo into the result.

    Is the "platform" expecting HTML output? Or just JSON text? If the latter, you need this as the first statement that emits output:

    header('Content-Type: application/json');
    

    Bottom line: This might be all you need:

    header('Content-Type: application/json');
    echo json_encode($retunValue);
    

    (I have not checked for other issues.)

    点赞 评论 复制链接分享

相关推荐