PHP PDO query not executed

Question

I am trying to execute a query by binding parameters. I have a query() function in DB.php, which takes the query and parameters as arguments. In this function, the query is successfully prepared using prepare(), but the query is not executed when i am using execute() on it.(I am not getting "Success" printed on the screen.)

What should i do? So that the the query gets executed successfully.

Code:

DB.php

<?php

class DB {
    private static $_instance = null;
    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()) {
            echo "Success";
        } 

        }

    }


}

index.php

<?php 
require_once 'core/init.php';

DB::getInstance()->query("SELECT username FROM users WHERE username = ?", array('alex'));

Answer 1

An else statement with:

print_r($this->_query->errorInfo());

Shows : Array ( [0] => 3D000 [1] => 1046 [2] => No database selected )

Then I checked the PDO connection line :

Changed this line:

$this->_pdo = new PDO('mysql:host = '. Config::get('mysql/host').';dbname =' .Config::get('mysql/db').'', Config::get('mysql/username'), Config::get('mysql/password'));

Into this:

$this->_pdo = new PDO('mysql:host = '. Config::get('mysql/host').';dbname=' .Config::get('mysql/db').'', Config::get('mysql/username'), Config::get('mysql/password'));

Removed the whitespace after dbname and it worked!

Answer 2

there are many flaws in this otherwise very smart class by intention, such as lack of error reporting and statefulness. To make it sane, change it as follows:

class DB {
    private static $_instance = null;

    protected function __construct() {
        $opt  = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
        $this->_pdo = new PDO('mysql:host = '. Config::get('mysql/host').'; dbname ='. Config::get('mysql/db').'', Config::get('mysql/username'), Config::get('mysql/password'), $opt);
    }

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

    public function query($sql, $params = array()) {
        $stmt = $this->_pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}

Note that you have to be able to see PHP errors in general. Obviously.

However, this class lacks access to some of PDO functions. You can add them manually but personally I prefer to use some magic to make them all called automatically. So, I wrote my own PDO wrapper, which is serving me good if I am bound to use PDO instead of my own DB wrapper. It's leaky abstraction too, as it's using PDO statement to return data, but one have to be absolutely mad to dump that brilliant mechanism that can return your data in dozen different formats.

In any case there should be absolutely no variables like

$_query,
$_error,
$_results,

as they introduce state in your class which have to be strictly stateless.