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

add an else statement after the ($this->_query->execute()) line & add some error handling to it — Rob G
– Rob G, Commented Jul 10, 2015 at 8:56
My guess would be that you never reach the $this->_query->execute() because the $this->_pdo->prepare($sql) call fails. — Cyclonecode
– Cyclonecode, Commented Jul 10, 2015 at 9:17
@RobGudgeon I added print_r($this->_query->errorInfo()) in else statement the output shows Array ( [0] => 3D000 [1] => 1046 [2] => No database selected ) — Mayank_17
– Mayank_17, Commented Jul 10, 2015 at 9:28

Your Common Sense · Accepted Answer · 2015-07-10 10:11:29Z

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.

Mayank_17 · Accepted Answer · 2015-07-10 13:29:24Z

0

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!

answered Jul 10, 2015 at 13:29

Mayank_17

751 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP PDO query not executed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related