Is there a reason my execute command for PDO isn't working?

Question

I am just beginning to learn about PHP OOP through videos on youtube and I am following along in building a website application.

I have to connect to the SQL database using PDO as the guy explained. I followed everything he did but my execution part for the PDO is not working and I don't know what is wrong with it. The code is below. it does not bring up any errors it just doesn't bring back any data from the database. I put some echos in between the function to find out how far the code is going and that is how I found out that it prepares the query but never executes it. Please help me figure this out. Thank you.

class Database{
private function connect()
{
    $string = DBDRIVER . ":host =" .DBHOST.";dbname =".DBNAME;
    if ($conn = new PDO($string, DBUSER, DBPASS)) {
        return $conn;
    } else {
        die("Could not connect to database");
    }
}

The code above is the connection to the database. it is working correctly as far as I can tell.

The code below is supposed to return the data from the database back to be displayed using a view.php file

public function query($query, $data = array(), $data_type = "object")
{

    $con = $this->connect();
    $stm = $con->prepare($query);
    print_r($stm);

    if ($stm) {
        $check = $stm->execute($data); //right here is where I concluded that the code stops
        print_r($check);//does not print anything
        if ($check) {
            if ($data_type == "object") {
                $data = $stm->fetchAll(PDO::FETCH_OBJ);
            } else {
                $data = $stm->fetchAll(PDO::FETCH_ASSOC);
            }

            if (is_array($data) && count($data) > 0) {
                return $data;
            }
        }
    }
    return false;
}

below is the part written in the controller to assign the query to the database to execute.

function index(){

        $db = new Database();
        
        $data = $db->query("select * from users");
        if($data){echo $data;} //echos nothing because $data is empty
        $this -> view('home', ['rows' => $data]);
    }

Which PHP Version are you using? As of version 8.0.0, PHP automatically runs in a `Exception mode` when errors occur. Maybe you could wrap your `$stm->execute()` in a `try{ $stm->execute($data); } catch(PDOException e) { echo e->getMessage(); }` so you can see what the error is. — Laisender, Jun 05 '22 at 19:25
Side note: you `echo $data;` but `$data` is an array. Use `print_r($data);`. — bloodyKnuckles, Jun 05 '22 at 19:31
@Laisender FYI, there is ABSOLUTELY no point in adding that try catch stuff. In case you have displaying errors turned on, the error will be shown already without that ugly addition. In case you don't - well that's what should be actually done instead. Because on the dev server you want to see all errors, not just one selected — Your Common Sense, Jun 06 '22 at 06:14

Jonathan · Accepted Answer · 2022-06-05T20:07:02.703

i have tested your code the error occurs because u have a space between the dbname and the =. A semicolon after the DBNAME is also missing (it is not needed).

class Database{
private function connect()
{
    $string = DBDRIVER . ":host=" .DBHOST.";dbname=".DBNAME.";";
    if ($conn = new PDO($string, DBUSER, DBPASS)) {
        return $conn;
    } else {
        die("Could not connect to database");
    }
}

Now it works for me.

i am using this connection class for a long time, maybe you want to use it.

class Connection{

    private const CONNECTION = [
        "Host" => "",
        "User" => "",
        "Password" => "",
        "Databasename" => ""
    ];

    /**
     * Includes the pdo connection.
     * @var \PDO|null
     */
    private \PDO|null $PDO = null;

    /**
     * Includes the already called instance.
     * @var Connection|null
     */
    private static self|null $Connection = null;

    /**
     * Creates the DB connection and sets it to the class.
     * @return void 
     * @throws Exception 
     */
    private function __construct()
    {
        $Connection_str = 'mysql:';
        $Connection_str .= 'host=' . self::CONNECTION["Host"] . ';';
        $Connection_str .= 'dbname=' . self::CONNECTION["Databasename"] . ';';
        $Connection_str .= 'charset=utf8mb4';
        $this->PDO = new \PDO($Connection_str, self::CONNECTION["User"], self::CONNECTION["Password"]);
        $this->PDO->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->PDO->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
        $this->PDO->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);
    }

    /**
     * Returns the static DB connection instance.
     * @return Connection 
     * @throws Exception 
     */
    public static function get(): Connection
    {
        return !isset(self::$Connection) ? self::$Connection = new self : self::$Connection;
    }

    /**
     * Returns the PDO instance.
     * @return PDO 
     * @throws Exception 
     */
    public function getPDO(): \PDO
    {
        return $this->PDO;
    }
}

You can use the connection class using the following code:

$SQLString = "select * from users";
$Data = [

];
$Query = Connection::get()->getPDO()->prepare($SQLString);
$Query->execute($Data);
$Data = $Query->fetchAll();

I just tried your solution now and removed the space you talked about and it worked! Thank you so much. I didn't realize there was a space there. Thank you! — Ezra, Jun 05 '22 at 20:20
You don't know how long i spent trying to fix this and it was space and a semi-colon all along. I believe I would definitely have a better day ahead! Thank you so much! — Ezra, Jun 05 '22 at 20:23

Is there a reason my execute command for PDO isn't working?

1 Answers1