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();
?>

Your DBConnection class doesn't have a method called prepare(). If you want to use PDO's prepare you would have to extend it. But you really have to ask yourself: "do I really need to wrap the PDO class"? — PeeHaa
– PeeHaa, Commented Nov 5, 2012 at 21:17
Also you are using the ancient constructor of PHP (same name as class). You should use __construct(). Also you're trying to make your constructor return something return $DBH. That doesn't work. The constructor return an instance of the class. — PeeHaa
– PeeHaa, Commented Nov 5, 2012 at 21:18
@PeeHaa Thanks for your reply. What exactly I am trying to do is get my information from database using get_all() function without having to give the database connection information. Could you please edit my code and show me how exactly to write the code to achieve this? Thanks :) — black_belt
– black_belt, Commented Nov 5, 2012 at 21:23
@black_belt if you're using php before 5.3.3 all you need to do is rename your DBConnection method call and call it--you're returning a pdo connection from it. — Ray
– Ray, Commented Nov 5, 2012 at 21:31

Community · Accepted Answer · 2016-01-08 12:42:03Z

15

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

edited Jan 8, 2016 at 12:42

CommunityBot

11 silver badge

answered Nov 5, 2012 at 21:31

PeeHaa

73.1k60 gold badges195 silver badges264 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

black_belt Over a year ago

Thanks PeeHaa, for your answer. You have some little mistakes in your code but I got the idea.. thanks a lot :)

PeeHaa Over a year ago

I think I have fixed the mistakes now. yw

Ryan Stone Over a year ago

When using extends PDO get this error? Fatal error: Uncaught ArgumentCountError: PDO::__construct() expects at least 1 parameter, 0 given

purpletree · Accepted Answer · 2012-11-05 21:24:55Z

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.

answered Nov 5, 2012 at 21:24

purpletree

1,9633 gold badges20 silver badges32 bronze badges

Collectives™ on Stack Overflow

Creating a database connection class (PDO) and fetch data

File db.class.php

File show.php

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

File db.class.php

File show.php

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related