PDO类不返回Count

Question

I wrote up a PDO abstraction class with some tutorials and code bits from StackOverflow to make my life bit easier, however PDO stills seems to be a pain in the ass and makes me wonder if I'm either stupid or if PDO has a more larger learning curve as compared to good old MySQL.

Anyways, what I'm trying to do is create a Statistics class to count few rows without writing major queries right and left. I'm trying to get a count for the following tables. Contacts + Companies + Users

But it's not working for some reason. Most of the time I hit 500 error. And looking at the code it seems correct for most part, unless I'm missing something.

So here is the Database abstraction class lib/Database.php

class Database{
    private $host      = DB_HOST;
    private $user      = DB_USER;
    private $pass      = DB_PASS;
    private $dbname    = DB_NAME;

    private $dbh;
    private $error;
    private $stmt;

    public function __construct(){
        // Set DSN
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        // Set options
        $options = array(
            PDO::ATTR_PERSISTENT    => true,
            PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );
        // Create a new PDO instanace
        try{
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        }
        // Catch any errors
        catch(PDOException $e){
            $this->error = $e->getMessage();
        }
    }

    # PDO Prepare
    public function query($query){
    $this->stmt = $this->dbh->prepare($query);
    }

    # PDO Count All
    public function countAll($value){
    $sql = "SELECT * FROM `$value`";

    $this->stmt = $this->dbh->prepare($sql);
    try { $this->stmt = $this->execute(); } catch(PDOException $e) { $this->error = $e->getMessage(); }
    return $this->stmt->rowCount();
    }

    # PDO Bind
    public function bind($param, $value, $type = null){
    if (is_null($type)) {
        switch (true) {
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
        }
    }
    $this->stmt->bindValue($param, $value, $type);
    }

    # PDO Execute
    public function execute(){
    return $this->stmt->execute();
    }

    # PDO Multiple Records
    public function resultset(){
    $this->execute();
    return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    # PDO Single Record
    public function single(){
    $this->execute();
    return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }

    # PDO Count
    public function rowCount(){
    return $this->stmt->rowCount();
    }

    # PDO Last Insert ID
    public function lastInsertId(){
    return $this->dbh->lastInsertId();
    }

    # PDO Transactions begin / end / cancel
    public function beginTransaction(){
    return $this->dbh->beginTransaction();
    }

    public function endTransaction(){
    return $this->dbh->commit();
    }

    public function cancelTransaction(){
    return $this->dbh->rollBack();
    }

    # PDO Debug Dump
    public function debugDumpParams(){
    return $this->stmt->debugDumpParams();
    }

}

And here is the Statistics class lib/Stats.class.php

class Stats{
      private $_db;

      public function __construct(Database $db){
         $this->_db = $db;
      }

      public function countContacts() {
          $this->_db->query('select count(*) from contacts');
          $this->_db->fetchColumn();
      }

      public function countCompanies() {
          $this->_db->query('select count(*) from companies');
          $this->_db->fetchColumn();
      }

      public function countUsers() {
          $this->_db->query('select count(*) from users');
          $this->_db->fetchColumn();
      }

      public function countInvoices() {
          $this->_db->query('select count(*) from invoices');
          $this->_db->fetchColumn();
      }
}

And here is how I make the call on say index.php

$database = new Database();
$stats = new Stats($database); 
echo $stats->countContacts();

The connection values are passed in the background as they are included in the header of the Template file.

Any suggestions on what im doing wrong?

Answer 1

For some reason you just forgot the query() function implementation, which is no more than a wrapper for PDO::prepare(). So it makes no sense to call it the way you are doing it.

add the following method to your class

public function run($query, $params = NULL){
    $stmt = $this->dbh->prepare($query);
    $stmt->execute($params);
    return $stmt;
}

and then rewrite your stats collectors:

  public function countUsers() {
      return $this->_db->run('select count(*) from users')->fetchColumn();
  }

Note that all other functions in your wrapper are either harmful or useless. Please read my article, Your first database wrapper's childhood diseases to find why