Using pdo query() and custom getValue() on same da

问题:

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.

回答1:

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

回答2:

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