Creating a database connection class (PDO) and fetch data

Question

I am new to OOP, so I am trying to learn how to create classes and use them. Currently I am trying to fetch data from my MySQL table.

To create the connection with MySQL I am using PDO. I have created a separate class for database connection. I have included the class in my show.php file. Now I want to fetch data from MySQL database. The problem is when I run my show.php file it shows this error message

Fatal error: Call to undefined method DBConnection::prepare() in C:\xampp\htdocs\jm\show.php on line 10`

But it was supposed to display just array.

What is the solution to this problem?

File db.class.php

<?php
    class DBConnection {

        function DBConnection(){

            $host = 'localhost';
            $dbname = 'srijon';
            $user = 'root';
            $pass = '';

            try {
                $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
                $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                return $DBH;
            }
            catch(PDOException $e) {

                echo 'ERROR: ' . $e->getMessage();
            }

        } // function ends

    } // class ends
?>

File show.php

<?php
    require_once 'db.class.php';

    function get_all(){

        $db = new DBConnection();

        $sql = "SELECT * FROM information";
        $STH = $db->prepare($sql);
        $STH->execute();
        $STH->setFetchMode(PDO::FETCH_ASSOC);

        return $STH;
    }

    echo get_all();
?>

Answer 1

IMHO you can just inject the PDO connection into the functions that need it:

<?php

$dbHandle = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$dbHandle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// always disable emulated prepared statement when using the MySQL driver
$dbHandle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

function get_all($dbHandle) {
    $sql = "SELECT * FROM information";
    $stmt = $dbHandle->prepare($sql);
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    return $stmt;
}

get_all($dbHandle);

If you really think you need some class to access to database (other than PDO) (which you don't need) you would have to extend PDO:

<?php

class DBConnection extends PDO
{
    public function __construct()
    {
        parent::__construct("mysql:host=$host;dbname=$dbname", $user, $pass);
        $this->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // always disable emulated prepared statement when using the MySQL driver
        $this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }
}

$dbHandle = new DBConnection();

function get_all($dbHandle) {
    $sql  = "SELECT * FROM information";
    $stmt = $dbHandle->prepare($sql);
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    return $stmt;
}

get_all($dbHandle);

Answer 2

Maybe change the DBConnections FUNCTION to a __contstruct() function. In addition, you'd need to extend the PDO class to use all the methods within it.