Accessing database from static and non-static methods

Question

I'm currently building a REST API in PHP & MySQL, and now I'm stuck at how I should structure the classes and the database connection.

Let's say I have a table in my database for movies. How should I structure my code for connecting to the database?

Currently I have this:

<?php
//Db.php

class Db
{
    private $db;
    private $table = '';
    private $where = '';

    public function __construct()
    {

        $json = json_decode(file_get_contents(INC_ROOT . '/app/db.json'), true);

        $this->db = new \PDO(
            'mysql:host='.$json['host'].';dbname='.$json['dbname'].';charset=utf8',
            $json['username'],
            $json['password']
        );
        $this->db->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
    }

    public function table($tbl)
    {
        $this->table = $tbl;
        $this->where = '';
        return $this;
    }

    ...
}

---

<?php
//Movie.php

class Movie
{
    public $db;
    public $id;
    public $title;

    public static function getById($id)
    {
        $instance = new self();
        $result = $db->table('movies')->where("id = $id")->select();
        $instance->id = $result[0]['id'];
        $instance->title = $result[0]['title'];
        return $instance;
    }

    public static function getAll()
    {
        $instances = array();
        $results = $db->table('movies')->select();
        foreach($results as $movie){
            $instance = new self();
            $instance->id = $movie['id'];
            $instance->title = $movie['title'];
            $instances[] = $instance;
        }
        return $instances;
    }

    public function updateTitle($title)
    {
        $this->title = $title;
        //update in database
    }
}

---

The rest of the api is built using Slim so it's basically

$app = new Slim();
$app->container->singleton('db', function() {
    return new Db();
});

$app->get('/movies', function() use ($app) {
    $db = $app->container['db'];
    $movies = Movie::getAll();
}

$app->get('/movies/:id', function($id) use ($app) {
    $db = $app->container['db'];
    $movie = Movie::getById($id);
}

---

The problem here is that I don't know how to access the database from both the static and non-static methods in the Movie class. I don't want to send an instance of the database class to each Movie method. But creating a new db instance inside each method doesn't seem optimal either.

So where should I create the db instance and how do I access it?

Answer 1

What you probably want to do here is use a repository... This would have your querying methods directly related to your Object and would use and instance of the DB as part of its constructor:

$repo = new MovieRepository($db);
$movies = $repo->findAll();

And or writing you might have:

$repo = new MovieRepository($db);
$movie = $repo->findById($id);
$movie->setTitle('A new Title');
$repo->update($movie);

So in your Slim controller for example:

$app->get('/movies/:id', function($id) use ($app) {
    $repo = new MovieRepository($app->container['db']);
    $movie = $repo->findById($id);
}