pdo的select操作怎么利用prepare语句返回结果数组

Question

• db类

//php代码
    class Db
{
    private $pdo;
    private $pdoS;
    private static $instance;
    private $params = array();   //保存要插入的值
    private $statement = '';     //保存sql语句
    
    private function __construct($host,$username,$password,$dbname)
    {
        $dsn = 'mysql:host='.$host.';dbname='.$dbname;
        $this->pdo = new \PDO($dsn, $username, $password);
    }
    
    public static function getInstance($host,$username,$password,$dbname)
    {
        if(!self::$instance instanceof self)
        {
            self::$instance = new self($host,$username,$password,$dbname);
        }
        return self::$instance;
    }
    public function select(array $select, $table)
    {
        $this->statement .= ' select ';
        $keys = '';
        foreach ($select as $val)
        {
            $this->params[] = $val;
            $keys .= $keys===''? '?': ',?';
        }
        $this->statement .= ($keys.' from '.$table);
        return $this;
    }
    public function getResult($type=0)
    {
        if(empty($this->params))
        {
            return false;
        }
        $params = $this->params;
        $pdoS = $this->pdo->prepare($this->statement);
        for($i = 0; $i < count($params); $i++)
        {
            $pdoS->bindParam($i+1, $params[$i]);
        }
        $typeArr = array(
            0 => \PDO::FETCH_ASSOC,
            1 => \PDO::FETCH_NUM,
            2 => \PDO::FETCH_BOTH,
        );
        $this->pdoS = $pdoS;
        $pdoS->execute();
        return $pdoS->fetchAll();
    }
    
    //...
}

调用db类代码

    $config = new getConfig();
    $db_config = $config['database'][$db];
    $db = Db::getInstance($db_config['HOST'], $db_config['USERNAME'], $db_config['PASSWORD'], $db_config['NAME']);
    var_dump($res = $db->select(array('*'), 'admin')->getResult());

显示结果

array (size=2)
  0 => 
    array (size=2)
      '*' => string '*' (length=1)
      0 => string '*' (length=1)
  1 => 
    array (size=2)
      '*' => string '*' (length=1)
      0 => string '*' (length=1)

$pdoStatement->execute()返回值是true说明select语句执行成功了,但是怎么才能获得结果数组呢?

Answer 1

执行$db->select(array('*'), 'admin')之后，$params = ['*']，$statement = "select ? from admin;

你prepare的语句是select ? from admin，注意?的位置。

然后在bindParam(1, $params[0])，实际执行的语句就变成：select '*' from admin，就是select一个字符串'*'。

select '*' from admin和select * from admin是不同的。

Answer 2

return $pdoS->fetchAll($typeArr[$type]);

Answer 3

是不是因为类里面的select方法的array参数没有加括号？

Answer 4

<?php
$db = new PDO();
$sql = "SELECT * FROM `post` WHERE `id` = ?";
$stmt = $db->prepare($sql);
$stmt->execute(array($id));
return $stmt->fetchAll(PDO::FETCH_ASSOC);

execute里数组的参数顺序要跟$sql里的?占位符顺序一一对应.