php - 无法使用 PDO 绑定(bind)表名和列名。 'PDOException' 消息 'SQLSTATE[42000]

Question

所以我是 php 的新手，并且已经完成了 phpacademys OOP 注册系统教程。我无法完成以下查询。

/**
 * Get an array of listings (all or by category)
 * @param null $category
 * @return mixed
 */
public function getListings($category = null) {
    if(!$category) {
        $sql = "SELECT * FROM ? WHERE ? = ?";
        $this->_db->query($sql, ['listings', 'active', 1]);
    } else {
        $sql = "SELECT * FROM ? WHERE ? = ? AND ? = ?;";
        $this->_db->query($sql, ['listings', 'active', 1, 'category', $category]);
    }

        return $this->_db->results();
}

这是我在数据库类中的查询方法

    /**
 * Perform a query
 * @param $sql
 * @param array $params
 * @return $this
 */
public function query($sql, $params = array()){
    $this->_error = false;
    echo $sql;
    if($this->_query = $this->_pdo->prepare($sql)){
        $x = 1;
        if(count($params)){
            foreach($params as $param){
                    $this->_query->bindValue($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;
}

这是我调用 getListings 函数的页面上的一个错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''listings' WHERE 'active' = '1' AND 'category' = 'computers'' at line 1' in /var/www/html/classes/DB.php:57 Stack trace: #0 /var/www/html/classes/DB.php(57): PDOStatement->execute() #1 /var/www/html/classes/Listing.php(161): DB->query('SELECT * FROM ?...', Array) #2 /var/www/html/browse.php(14): Listing->getListings('computers') #3 {main} thrown in /var/www/html/classes/DB.php on line 57

Answer 1

您不能在 PDO 查询中绑定(bind)(由参数替换)表名和列。

$sql = "SELECT * FROM ? WHERE ? = ?";
        $this->_db->query($sql, ['listings', 'active', 1]);

必须通过:

$sql = "SELECT * FROM listings WHERE active = ?";
        $this->_db->query($sql, [1]);

和:

    $sql = "SELECT * FROM ? WHERE ? = ? AND ? = ?;";
    $this->_db->query($sql, ['listings', 'active', 1, 'category', $category]);

必须通过:

    $sql = "SELECT * FROM listings WHERE active = ? AND category = ?;";
    $this->_db->query($sql, [1, $category]);

阅读更多:

http://us3.php.net/manual/en/book.pdo.php#69304