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
– Félix Adriyel Gagnon-Grenier, Commented Mar 28, 2014 at 3:17
You're accessing the static property $db in non-static ways. That is not going to work — Phil
– Phil, Commented Mar 28, 2014 at 3:17
In the construct, call: parent::__construct(). Also see some example here on extending classes. — hjpotter92
– hjpotter92, Commented Mar 28, 2014 at 3:19
Please don't extend PDO. There is nothing you could add to make it any better than it already is — Phil
– Phil, Commented Mar 28, 2014 at 3:20

Your Common Sense · Accepted Answer · 2014-03-28 08:11:48Z

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, 2014 at 8:11

answered Mar 28, 2014 at 3:37

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Your Common Sense Over a year ago

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.

Félix Adriyel Gagnon-Grenier Over a year ago

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?

Your Common Sense Over a year ago

@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

Phil · Accepted Answer · 2014-03-28 03:28:15Z

1

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');

answered Mar 28, 2014 at 3:28

Phil

166k25 gold badges265 silver badges269 bronze badges

1 Comment

Félix Adriyel Gagnon-Grenier Over a year ago

this even leads me to think about having both in separate variables. I like that sound.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related