How do I parse object data from MySQL database usi

Question

I have run into an issue while trying to return object data from my database. I followed this tutorial to build a DB wrapper and have been trying to extend the functionality of it to suit my needs. My problem is that when I use the DB class "get" method to return data, it also returns an instance of the PDO object itself. I am wondering how I parse this data so that I only return objects from the database, and not the database object itself. I want to be able to display different database values for each object eventually.

Here is all of my relevant code:

class DB {

private static $_instance = null; //stores instance of database
private $_pdo, 
        $_query, 
        $_error = false, 
        $_results, 
        $_count = 0;


private function __construct() {
    try {
        $this->_pdo = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db'), Config::get('mysql/username'),  Config::get('mysql/password'));
    } catch(PDOException $e){
        die($e->getMessage());
    }
}


public static function getInstance() {
    if(!isset(self::$_instance)){
        self::$_instance = new DB();
    }
    return self::$_instance;
}

public function query($sql, $params = array()){
    $this->_error = false;
    if($this->_query = $this->_pdo->prepare($sql)) {
        $x = 1;
        if(count($params)) {
            foreach($params as $param) {
                $this->_query->bindValue($x, $param);
                $x++;
            }
        }
        if($this->_query->execute()) {
            $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
            $this->_count = $this->_query->rowCount();
        } else {
            $this->_error = true;
        }
    }
    return $this; 
}


public function action($action, $table, $where = array()) {
    if(count($where) === 3) {
        $operators = array('=', '>', '<', '>=', '<=');

        $field = $where[0];
        $operator = $where[1];
        $value = $where[2];

        if(in_array($operator, $operators)) {
            $sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";
            if(!$this->query($sql, array($value))->error()) {
                return $this;
            }
        }

    } return $this;

}

public function get($table, $where) {
    return $this->action('SELECT *', $table, $where);

}
}

class Schedule {

private $_db;
protected $_games = array();

public function __construct() {

    $this->_db = DB::getInstance();

}

public function listGames() {

    $data = $this->_db->get('games', array('id', '>', 0));
    var_dump($data);

    echo '<br>';

}
}

Answer 1

The problem with these video tutorials is their authors who have no clue on the subject, making the result MUCH MUCH WORSE than if you weren't using their mental excrement at all. The damage they are inflicting is so bad that I even had to write a dedicated article that explains why all these helpless "wrappers" are totally unusable for the any real life application, Your first database wrapper's childhood diseases.

Take this wrapper from the video for instance:

• Error reporting is completely flawed
• A useless function for SELECTs.
• Statefulness
• Protected PDO instance

So, in essence, you'll be unable to get from this "wrapper" even such a silly thing as Insert Id. And no error reporting could help you even to realize the problem.

As of your code, just don't bargain for saving yourself typing an SQL keyword. It's silly. SQL is a precious thing, do not dismiss it in favor of some gibberish shortcuts. Neither you should diminish PDO to a state of a crippled invalid, trashing off its most brilliant features.

Your wrapper should make all PDO and SQL features accessible instead of discarding them. So it goes:

class DB
{
    protected static $instance;
    protected $pdo;

    protected function __construct() {
            $opt  = array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                PDO::ATTR_EMULATE_PREPARES   => FALSE,
            );
            $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            $this->pdo = new PDO($dsn, DB_USER, DB_PASS, $opt);

    }

    // a classical static method to make it universally available
    public static function instance()
    {
        if (self::$instance === null)
        {
            self::$instance = new self;
        }
        return self::$instance;
    }

    // a proxy to native PDO methods
    public function __call($method, $args)
    {
        return call_user_func_array(array($this->pdo, $method), $args);
    }

    // a helper function to run prepared statements smoothly
    public function run($sql, $args = [])
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

this wrapper is ten times simpler and at the same time ten times more powerful than that from the video.

And now your class

class Schedule
{
    private $_db;

    public function __construct()
    {
        $this->_db = DB::instance();
    }

    public function listGames()
    {
        $data = $this->_db->query('SELECT * FROM games')->fetchAll();
        var_dump($data);
        echo '<br>';
    }
}

The secret here is that PDO can already give you object data, with not a single extra line of coding.

Although this simple example is not very impressive, the secret here is that this wrapper will serve you for any other example as well, when that from the video will choke. Try to think of any other example and I will show you how simple yet powerful this wrapper is.