SQL查询工作在phpMyAdmin,但不是在php页面


SQL Query works in phpMyAdmin but not in php page

我有一个奇怪的问题发生了:

我有一个php页面,编译和执行MySQL查询到我的web数据库。目标是使用自定义的非mbr空间关系函数尝试确定一个点是否在多边形内。

查询返回语法错误结果1064。

下面是显示在我的页面上的回显查询:

SET @point = 'POINT(-63.610719000 44.669318000)'; SELECT * FROM `Zones` WHERE GISWithin(GeomFromText(@point), `zonePoly`) AND `zoneName` = 'trailerShareBoundary';

如果我复制并粘贴这个查询字符串到phpMyAdmin,它就像一个魅力。

但是,当查询来自我的php页面时,返回以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM `Zones` WHERE GISWithin(GeomFromText(@point), `zonePoly`) AND `zon' at line 1(1064)

下面是实现这一切的php代码:

    if(!is_null($lng) && !is_null($lat)){
    //Create a mySQL variable to store a MySQL POINT object
    $query= "SET @point = 'POINT(".$lng." ".$lat.")'; ";
    //Test if the POINT variable is within the trailerShareBoundary variable using
    //custom MySQL function
    $query.= "SELECT * FROM `Zones` WHERE GISWithin(GeomFromText(@point), `zonePoly`) AND `zoneName` = 'trailerShareBoundary';";
    echo "<br/>".$query."<br/>";
    $result=mysqli_query($connection,$query);
    var_dump($result);
    echo '<br/>';
    if(!$result==false){
        $instructions = "<p>LOCK REQUEST VALID</p>";
    }else{
        $instructions = "<p>LOCK REQUEST INVALID</p>";
        echo mysqli_error($connection) . "(" . mysqli_errno($connection) . ")";
    }
    echo $instructions;
}

我已经想出了一个解决办法:

如果我首先查询数据库以创建@point变量,然后创建一个单独的查询来使用SELECT语句查询数据库,它就神奇地工作了!我的意思是:

如此:

    //Create a mySQL variable to store a MySQL POINT object
    $query= "SET @point = 'POINT(".$lng." ".$lat.")'; ";
    $result=mysqli_query($connection,$query);
    echo "<br/>".$query."<br/>";
    //Test if the POINT variable is within the trailerShareBoundary variable using
    //custom MySQL function
    $query= "SELECT * FROM `Zones` WHERE GISWithin(GeomFromText(@point), `zonePoly`) AND `zoneName` = 'trailerShareBoundary';";
    $result=mysqli_query($connection,$query);
    echo "<br/>".$query."<br/>";
那么,谁能告诉我发生了什么事?

mysqli_query不允许多个查询,使用mysqli_multi_query

这个应该可以工作:

//Create a mySQL variable to store a MySQL POINT object
$query = "SET @point = 'POINT(".$lng." ".$lat.")'; ";
$query .= "SELECT * FROM `Zones` WHERE GISWithin(GeomFromText(@point), `zonePoly`) AND `zoneName` = 'trailerShareBoundary';";
$result=mysqli_multi_query($connection,$query);
echo "<br/>".$query."<br/>";

在第一个中,您将第一个查询附加到第二个查询。并且在程序内的查询中也使用了分号。