带有匿名连接的 PHP mysqli 准备语句

Question

我目前对准备一条语句感到沮丧 - 罪魁祸首是没有正确地准备它，而是与我当前的函数保持一致，以对具有可变数量参数的查询进行分类。它适用于所有情况，但是这个查询给了我一个 mysql 无法解决的问题(在一个查询中):

SELECT studios.name,
        studios.phone,
        locations.zip_code,
        locations.location_name,
        addresses.street_name,
        addresses.stree_nr,
        persons.first_name,
        persons.last_name,
        p.distance_unit
            * DEGREES(ACOS(COS(RADIANS(p.latpoint))
            * COS(RADIANS(addresses.geo_lat))
            * COS(RADIANS(p.longpoint) - RADIANS(addresses.geo_long ))
            + SIN(RADIANS(p.latpoint))
            * SIN(RADIANS(addresses.geo_lat)))) AS distance
FROM    studios
JOIN    (   /* these are the query parameters */
          SELECT  ? AS latpoint, ? AS longpoint,
                  ? AS radius,   111.045 AS distance_unit
        ) AS p ON 1=1
CROSS JOIN  addresses
ON          studios.address = addresses.id
CROSS JOIN  locations
ON          addresses.location = locations.id
CROSS JOIN  persons
ON          studios.owner = persons.id
CROSS JOIN  studio_types
ON          studios.studio_type = studio_types.id
WHERE       addresses.geo_lat
    BETWEEN p.latpoint  - (p.radius / p.distance_unit)
        AND p.latpoint  + (p.radius / p.distance_unit)
        AND addresses.geo_long
    BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
        AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY    distance
LIMIT 25

查询本身功能齐全——这里是它的要点:

SELECT *
FROM    studios
JOIN    ( SELECT  ? AS latpoint, ? AS longpoint,
                  ? AS radius,   111.045 AS distance_unit
        ) AS p ON 1=1

现在绑定(bind)它非常简单:

$query = $this->conn->prepare($query);
if(!$query) throw new Exception($this->conn->error);
$query->bind_params("ddd", $val1, $val2, $val2);
$query->execute();
$query->get_result();

通过准备发生的错误已经发生在第一行:Unknown column 'p.latpoint' in 'field list'

现在这是一个大问题。解决这个问题可能会使用 SET @variable = ? 准备语句 - 但这不是首选情况，因为它会破坏我当前的请求调度方案。

由于准备时未定义的连接，是否存在任何疏忽或者这根本不可能？

编辑:第三种方法将涉及替换连接和放置值 - 这会使绑定(bind)参数的数量非常困惑 - 以后很难维护。

Answer 1

所以，我终于为那些像我一样寻求相同答案的人来了，我必须扩展我留下的地方:

想象一个类为您完成所有繁重的工作，最大限度地减少问题，为您和数据库之间提供一个简单的接口(interface)。有一种方法采用语句字符串、类型绑定(bind)，如下所示:

protected function __dispatch($query, $types, $arguments) {
    error_reporting(0);
    $query = $this->conn->prepare($query);
    if(!$query) throw new Exception($this->conn->error);
    if($types !== "") call_user_func_array(array(&$query, 'bind_param'),
                                           array_merge(array($types), $arguments));
    $query->execute();
    return $query->get_result();
}

由于占位符不可能有任何标识符(感谢 Jon)，但是我可以很好地绑定(bind) SQL 变量。然后我要执行这样的事情:

SELECT *
FROM    studios
JOIN    ( SELECT  @lat AS latpoint, @lon AS longpoint,
                  @dist AS radius,   111.045 AS distance_unit
        ) AS p ON 1=1

这提供了在之前绑定(bind)变量的可能性 - 然后在语句中使用它，就像这样:

public function search_in_range($lat, $long, $distance) {
    $this->__dispatch("SET @lat = ?", "d", array(&$lat));
    $this->__dispatch("SET @lon = ?", "d", array(&$long));
    $this->__dispatch("SET @dist = ?", "d", array(&$distance));
    // And finally:
    return $this->__dispatch($query, $types, [])->fetch_all();
} 

这样做的技巧是创建一个非常简单的连接匿名数据集，并在稍后的更复杂的语句中使用它。