我有一个奇怪的问题发生了:
我有一个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/>";
在第一个中,您将第一个查询附加到第二个查询。并且在程序内的查询中也使用了分号。