Using pdo query() and custom getValue() on same database object?

Question

class connector {
  private static $db;
  function __construct() {
    $this->db = null;
    $this->connect();
  }
  private function connect() {
    $this->db = new PDO('mysql:host=localhost;dbname=database;charset=utf8','user','pass');
  }
  public static function getDB() {
    if (!isset(self::$db)) {
      self::$db = new connector();
    }
    return self::$db;
  }

EDITED

  // this was my original function, I had reduced it for less space. my deepest regrets, I won't do it again.
  public function getValue($sql,$val='') {
    if ($val != '') {
      $data = $this->db->prepare($sql);
      $data->execute($val);
      return $data->fetch(PDO::FETCH_COLUMN);
    }
    else {
      return $this->db->query($sql)->fetch(PDO::FETCH_COLUMN);
    }

using this class I can easily use the pdo database object from anywhere, with custom functions.

$db = connector::getDB();

// var_dump()
db:object(connector)#1 (1) {
  ["db":"connector":private]=>
  object(PDO)#2 (0) {
  }
}

$db->getValue('SELECT foo FROM bar'); //succeeds

$db->query('SELECT foo FROM bar'));
// throws
// Fatal error:  Call to undefined method connector::query()

if I return the actual object param instead of the whole object:

return self::$db->db;
db:object(PDO)#2 (0) {
}

the queries exchange roles

$db->getValue('SELECT foo FROM bar');
// throws
// Fatal error:  Call to undefined method connector::getValue()

$db->query('SELECT foo FROM bar')); //succeeds

How can I have both with the same object, effectively using $db->query() and $db->getValue() in the same script.

hrmm.. I think `PDO` is the built-in php data object class.. it throws `SQLSTATE[00000]: No error: PDO constructor was not called` when I `extends PDO` — Félix Adriyel Gagnon-Grenier, Mar 28 '14 at 03:17
You're accessing the static property `$db` in non-static ways. That is not going to work — Phil, Mar 28 '14 at 03:17
In the construct, call: `parent::__construct()`. Also see some [example here](http://www.php.net/manual/en/keyword.extends.php#107451) on extending classes. — hjpotter92, Mar 28 '14 at 03:19
Please don't extend `PDO`. There is nothing you could add to make it any better than it already is — Phil, Mar 28 '14 at 03:20

Your Common Sense · Accepted Answer · 2014-03-28T08:11:48.667

2

First of all, your getValue() method lacks one essential feature - support for prepared statements. Without ability to run dynamical queries it's value is close to nothing.

Second, if you are using a singleton anyway - then there is no need for the extra method to get an instance - nowadays singleton can do it just by itself.

What about this one? I wrote it to make PDO usage less wordy, utilizing neat method chaining. DB static class is already a PDO instance, without the need to instantiate it with extra call. As a result, you can run any PDO command just anywhere:

DB::query('SELECT foo FROM bar LIMIT 1')->fetchColumn();

It takes little more space than yours but at least it is giving you access to full PDO syntax, including support for prepared statements

DB::prepare('SELECT foo FROM bar WHERE id=?')->execite([$id])->fetchColumn();

as well as all other fetch methods

DB::query('SELECT foo FROM bar')->fetchAll(PDO::FETCH_COLUMN);

edited Mar 28 '14 at 08:11

answered Mar 28 '14 at 03:37

Your Common Sense

156,878
40
214
345

1

I have just found one potential bug, so, please re-download the code, if you decided to use it. Removed one non-essential feature, not related to the topic in question. – Your Common Sense Mar 28 '14 at 04:10
Is there something I really do not understand, or does the class myPdoStatement prevents the use of bindValue or bindParam? since `$data` defaults to an empty array, using `execute()` without argument after binding a value executes the statement with an empty array, throwing an error? – Félix Adriyel Gagnon-Grenier May 08 '14 at 19:04
@FélixGagnon-Grenier it doesn't prevent anything nor adds any functionality. Everything you can do is exactly the same what you can do with original PDO. Yes, you can *avoid* manual binding with PDO, passing array with values directly to execute - PDO will bind them internally. No, empty array doesn't raise an error – Your Common Sense May 08 '14 at 19:14

score 1 · Answer 2 · answered Mar 28 '14 at 03:28

Your re-use of the variable $db for both the static instance of the connector singleton and the private PDO member variable is confusing. Without shying away from your singleton approach, maybe try using some better variable names, eg

class connector {
    private static $instance = null;
    private $db;

    private function __construct() {
        $this->db = new PDO('mysql:host=localhost;dbname=database;charset=utf8','user','pass', [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]);
    }

    private function __clone() {} // because singletons should not be cloneable

    public static function getInstance() {
        if (self::$instance == null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * @return PDO
     */
    public function getDb() {
        return $this->db;
    }

    public function getValue($sql) {
        // as is
    }
}

Now, if you must run PDO::query, you can do so via the getDb method...

$db = connector::getInstance();
$db->getValue('SELECT foo FROM bar');
$db->getDb()->query('SELECT foo FROM bar');

this even leads me to think about having both in separate variables. I like that sound. — Félix Adriyel Gagnon-Grenier, Mar 28 '14 at 03:34

Using pdo query() and custom getValue() on same database object?

2 Answers2

Linked