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