PDO query class

Question

我正在修改一个"应该"允许我轻松执行fetchall查询并在foreach语句中显示结果的类。我认为一切都正常，因为我没有任何错误。至于前臂，这一定是问题所在吧？我将如何预测从$connect->query()中获得的结果？我是在函数中使用任何数据库OOP框架的新手，所以我可能完全走错了方向。

 <?
error_reporting(1);
class dbconnect {
private $host;
private $database;
private $username;
private $password;
private $pdo;
private $error;
    public function __construct() {
        $this->host     = "localhost";              // Host
        $this->database = "images";                 // Database Name
        $this->username = "*";              // Username
        $this->password = "*";  // Password
        $options  = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
        );
        try {
            $this->pdo = new PDO("mysql:host={$this->host};dbname={$this->dbname};charset=utf8", $this->username, $this->password, $options);
        }
        catch(PDOException $e) {
            $this->error = $e->getMessage();
        }
    }
    public function query($query) {
        try {
            $stmt = $this->pdo->prepare($query);
            $stmt->execute(); 
        } catch(PDOException $ex) { 
            die("Failed to run query: " . $ex->getMessage()); 
        } 
        $rows = $stmt->fetchAll();
        return $rows;
    }
}
$connect = new dbconnect;
$rows = $connect->query("select * from photos");
foreach($rows as $row):
    print $row['id'];
endforeach;

?>

Answer 1

您在query内部声明的$rows变量对外部是不可访问的，它是该函数的本地变量。很可能，您只是想将这些结果return发送给调用者：

$rows = $stmt->fetchAll();
return $rows; // return value from function...

并让调用者在其自己的变量中捕获该返回值

$rows = $connect->query("select * from images"); // ... is received by caller
foreach($rows as $row):

也可以看看dougjore的答案，你在query方法中混合了$this->stmt和$stmt。

Answer 2

非常确定您从未真正执行过查询：

        $this->stmt = $this->pdo->prepare($query);
        $stmt->execute(); 

我相信（我可能错了，我自己对PDO还很陌生，还没有为它建立一个类），你需要说$this->stmt->execute（）；

Answer 3

您可以进行

//PDO:：FETCH_ASSOC:返回按结果集中返回的列名索引的数组

$this->stmt = $this->pdo->prepare($query);
$this->stmt->execute();
while ($result = $this->stmt->fetch(PDO::FETCH_ASSOC))
{
  //do something with the result
}

请在此处查看获取PDO查询结果的更多选项：http://php.net/manual/en/pdostatement.fetch.php

Answer 4

$connect = new dbconnect;
$sql="select * from photos";
$stmt=$connect->pdo->prepare($sql); 
$stmt->execute();
$result=$stmt->fetch(PDO::FETCH_ASSOC);
foreach($result as $key => $value) {
   echo $key . "-" . $value . "<br/>";
}