php - 警告:PDOStatement::execute(): SQLSTATE[HY093]

Question

我在向数据库中插入新数据时遇到了这个错误

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /opt/lampp/htdocs/projectclasses/DB.php on line 47

方法代码:

public function query($sql, $params = array())
{
    $this->_error = false;
    if($this->_query = $this->_pdo->prepare($sql))
    {
        if(count($params)) 
        {               
            foreach($params as $param)
            {
                $x = 1;
                $this->_query->bindParam($x, $param);                   
                $x++;
            }

        }
        if($this->_query->execute())
        {
            $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
            $this->_count = $this->_query->rowCount();
        }
        else
        {
            $this->_error = true;
        }
    }
    return $this;
}

public function insert($table, $fields = array())
{
    if(count($fields))
    {
        $keys = array_keys($fields);
        $values = '';
        $x = 1;
        foreach($fields as $field)
        {
            $values .= "?";
            if($x < count($fields))
            {
                $values .= ", ";
            }
            $x++;
        }

        $sql = "INSERT INTO users (`". implode('` ,`', $keys) ."`) VALUES (".$values.")"; 
        if(!$this->query($sql, $fields)->error())
        {
            return true;
        }
    }
    return false;
}

插入代码:

$user = DB::getInstance()->insert('users', array(
    'username' => 'Marco', 
    'password' => '123456', 
    'salt' => 'salt'
));

Answer 1

Execute 可以接受一组参数，而不是单独绑定(bind)它们。

所以，首先，删除这个:

    if(count($params)) 
    {               
        foreach($params as $param)
        {
            $x = 1;
            $this->_query->bindParam($x, $param);                   
            $x++;
        }
    }

您可能需要使用 array_values 使其成为像这样的真实数组(如果您的数组有键，那么它将尝试使用键进行绑定(bind)):

if($this->_query->execute(array_values($params)))

这是 PHP 文档中的示例:

<?php
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));
?>

来源: http://php.net/manual/en/pdostatement.execute.php