0111010001110000 0111010001110000 - 1 month ago 13
PHP Question

PDO Class not returning Count

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

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