PDO识别缺少的绑定变量 [英] PDO identify missing bound variables

问题描述

使用PDO时比较乏味的一个是，它说缺少一些变量

One of the more tedious to work with PDO is that it says that some variables are missing

PDOStatement :: execute():SQLSTATE [HY093]:无效的参数编号:绑定变量的数量与令牌的数量不匹配

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

...但是不告诉哪个.有什么解决方案可以识别它们?例如

... but does not tell which ones. Is there any solution to identify them? Eg

$sql = "SELECT id, name WHERE id = :id AND name like  :search_tem";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', '1');
$stmt->execute(); // throws exception - "**search_term missing"

很明显每个人都需要这样的东西.但是我无法找到一个简单的解决方案.

It's so obvious everyone needs something like this. But I can't a simple solution.

推荐答案

经过几天的搜索，对该主题进行了测试，发现我找到了该解决方案-按照数据库中bind方法的代码进行操作>班级:

After some days searching about this subject, testing some stuff I found this solution - follow the code from bind method from my Database class:

// [Method from Class Database]

/**
 * @param PDOStatement stmt
 * @param array data
 * @throws Exception if some variables are missing when interpolate query
 * @example $db->bind($stmt, $_POST)
 */
function bind(&$stmt, $data) {
    $sql = $stmt->queryString;
    // Sort $data keys to prevent erroneus bind parameters
    ksort($data);
    $data = array_reverse($data);
    foreach ($data as $_key => $_val) {
        if (preg_match("/:$_key/", $sql)) {
            $stmt->bindParam(":$_key", $data[$_key]);
            $sql = preg_replace("/:$_key/", $this->dbh->quote($data[$_key]), $sql, 1);
        }
    }

    //  if ($this->debug) {
    //      $this->fb->info($sql, 'SQL');
    //      $this->fb->info($stmt, 'Statment');
    //      $this->fb->info($data, 'Raw Data');
    //  }

    if (strpos($sql, ":")) {
        $matches = array();
        $this->dbg = preg_match_all('/:[A-Za-z0-9_]*/', $sql, $matches);
        throw new Exception('PDO Missing variables: ' . implode(', ', $matches[0]));
    }
}

此代码需要修订，如果有人发现错误或有改进之处，请分享！

This code needs revision, if someone finds a bug or have improved something please share!

这篇关于PDO识别缺少的绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！