ZF：查询错误 SQLSTATE[HY093]

发布于 2024-10-21 23:10:31 字数 618 浏览 2 评论 0原文

我在这个函数上遇到错误：

public function getCountCategoryByUrl($url, $root = 0)
{
    $url = strtolower($url);


    $select = $this->select()
                   ->from($this->_name, array('count(*) as cnt'))
                   ->where("LOWER(catalog_url) LIKE :url " . ($root > 0 ? " AND `type` = :type" : ""));

    return $this->getAdapter()->fetchOne($select, array('url' => $url, 'type' => $root));
}

错误：

Message: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

我做错了什么？

原文

I have error on this function:

public function getCountCategoryByUrl($url, $root = 0)
{
    $url = strtolower($url);


    $select = $this->select()
                   ->from($this->_name, array('count(*) as cnt'))
                   ->where("LOWER(catalog_url) LIKE :url " . ($root > 0 ? " AND `type` = :type" : ""));

    return $this->getAdapter()->fetchOne($select, array('url' => $url, 'type' => $root));
}

Error:

Message: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

What I'm doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽梦忆笙歌 2024-10-28 23:10:32

当您有 $root <= 0 时就会出现问题。在这种情况下，您的 SQL 语句仅包含一个标记 (:url)，而您绑定两个变量（:url 和 :类型）。

您必须有条件地设置绑定参数：

$select = $this->select()
               ->from($this->_name, array('count(*) as cnt'))
               ->where("LOWER(catalog_url) LIKE :url ");
$params = array(':url' => $url);
if ($root > 0) {
    $select->where("`type` = :type");
    $params[':type'] = $root;
}

return $this->getAdapter()->fetchOne($select, $params);

编辑：我忽略了一些非常重要的事情。变量必须使用与 SQL 语句中定义的相同标记进行绑定。这意味着您必须使用 :url 和 :type 作为绑定变量（而不是 url 和 type）。

The problem arises when you have $root <= 0. In this case your SQL statement contains only one token (:url) while you're binding two variables (:url and :type).

You have to set the bound parameters conditionally:

$select = $this->select()
               ->from($this->_name, array('count(*) as cnt'))
               ->where("LOWER(catalog_url) LIKE :url ");
$params = array(':url' => $url);
if ($root > 0) {
    $select->where("`type` = :type");
    $params[':type'] = $root;
}

return $this->getAdapter()->fetchOne($select, $params);

EDIT: I've overlooked something very important. Variables must be bound with the same token as defined in the SQL statement. This means that you have to use :url and :type for the bound variables (not url and type).

回复收藏 0 原文