Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use PDO connection in other classes?

Tags:

oop

php

pdo

I think I've a problem in understanding how OOP works. I already changed the code that it works, but it isn't the propper way I think. Following scenario (No, I'm not creating a userlogin by myself, its really just for local dev. to understand OOP better):

I've a database.php file:

class Database {

    /* Properties */
    private $conn;
    private $dsn = 'mysql:dbname=test;host=127.0.0.1';
    private $user = 'root';
    private $password = '';

    /* Creates database connection */
    public function __construct() {
        try {
            $this->conn = new PDO($this->dsn, $this->user, $this->password);
        } catch (PDOException $e) {
            print "Error!: " . $e->getMessage() . "";
            die();
        }
        return $this->conn;
    }
}

So in this class I'm creating a database connection and I return the connection (object?)

Then I have a second class, the famous User class (actually I'm not using autoload, but I know about it):

include "database.php";

class User {
    /* Properties */
    private $conn;

    /* Get database access */
    public function __construct() {
        $this->conn = new Database();
    }

    /* Login a user */
    public function login() {
        $stmt = $this->conn->prepare("SELECT username, usermail FROM user");
        if($stmt->execute()) {
            while($rows = $stmt->fetch()) {
                $fetch[] = $rows;
            }
            return $fetch;
        }
        else {
            return false;
        }
    }
}

So thatare my two classes. Nothing big, as you see. Now, don't get confued about the function name login - Actually I just try to select some usernames and usermails from database and displaying them. I try to achieve this by:

$user = new User();
$list = $user->login();

foreach($list as $test) {
    echo $test["username"];
}

And here comes the problem. When I execute this code, I get the following error message:

Uncaught Error: Call to undefined method Database::prepare()

And I'm not sure that I really understand what causes this error.

The code works well when I change the following things:

Change $conn in database.php to public instead of private (I think thats bad...? But when its private, I can only execute querys inside of the Database class, I'm right? So should I put all these querys in the Database class? I think that's bad, because in a big project it will get become really big..)

And the second change I've to do is: Change $this->conn->prepare to $this->conn->conn->prepare in the user.php file. And here I've really no Idea why.

I mean, in the constructor of the user.php I've a $this->conn = new Database() and since new Database will return me the connection object from DB class, I really don't know why there have to be a second conn->

like image 471
Twinfriends Avatar asked Apr 26 '17 09:04

Twinfriends


People also ask

Can I use PDO with MySQL?

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases. So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries.

How can you manually close a PDO connection?

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted—you do this by assigning null to the variable that holds the object.

How fetch data from database in PHP and display PDO?

Fetch data from a result set by calling one of the following fetch methods: To return a single row from a result set as an array or object, call the PDOStatement::fetch method. To return all of the rows from the result set as an array of arrays or objects, call the PDOStatement::fetchAll method.


1 Answers

  • your current class is rather useless. It would make sense to create a database wrapper if it adds some extra functionality to PDO. But given its current code, better to use vanilla PDO instead.
  • Either way, create a single $db instance from either vanilla PDO or your database class.
  • pass it as a constructor parameter into every class that needs a database connection

database.php:

<?php
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new \PDO($dsn, $user, $pass, $opt);

user.php

<?php
class User {
    /* Properties */
    private $conn;

    /* Get database access */
    public function __construct(\PDO $pdo) {
        $this->conn = $pdo;
    }

    /* List all users */
    public function getUsers() {
        return $this->conn->query("SELECT username, usermail FROM user")->fetchAll();
    }
}

app.php

include 'database.php';
$user = new User($pdo);
$list = $user->getUsers();

foreach($list as $test) {
    echo $test["username"],"\n";
}

output:

username_foo
username_bar
username_baz

Check out my (The only proper) PDO tutorial for more PDO details.

like image 170
Your Common Sense Avatar answered Oct 01 '22 17:10

Your Common Sense