OOP - sql commands inside PHP class

user_4570923 0
Viewed 105

I have a website written with procedural PHP and now I'm re-coding it in (at least partly) OOP style so that I can start learning it. It is a simple page displaying several learning courses (title, description, etc) from a database. The admin can add or delete anything so it has to be dynamic. At first I ran a single line of code:

    $courses=$pdo->run("SELECT id,title,description FROM courses WHERE status=1 ORDER BY id")->fetchAll(PDO::FETCH_CLASS, 'Course');
$cmax = count($courses);

and echoed e.g. $courses[3]->description but I felt like I'm doing nothing else but pretending OOP while just using a multidimensional array. Again, it would be ok for the purpose of the website but my question is, in order to get used to OOP logic, can I do it like this: I'm generating a dropdown menu with only the titles and IDs from the database, and after either is clicked, only then I'm creating the object (to get the description, date, teacher, whatever):

$obj = new Course($pdo,$userSelectedID);
echo $obj->getTitle($pdo);
$obj->showDetails($pdo); // etc

The class: 

class Course {

    protected $id;
    protected $title;
    protected $description;

    public function __construct($pdo,$id) {
        $this->id=$id;
    }

    public function getTitle($pdo) {
        $this->title=$pdo->run("SELECT title FROM courses WHERE id=?",[$this->id])->fetchColumn();
        return $this->title;
    }

    public function getDescription($pdo) {
        $this->description=$pdo->run("SELECT description FROM courses WHERE id=?",[$this->id])->fetchColumn();
        return $this->description;
    }

    public function showDetails($pdo) {
        echo "<h3>".$this->getTitle($pdo)."</h3>".$this->getDescription($pdo);
    }

}

Is this a wrong approach? Is it ok to run sql commands inside a class? Especially when I already had to use some DB data to generate the dropdown menu. I hope my question makes sense.

PS: I've heard passing the PDO object every time isn't the best practice but I'm not there yet to do it with the recommended instance(?)

pdo sql php
1 Answers

A good approach is to create model classes for every table you have in your database. A model contains private attributes corresponding yo your table columns, with associated getters and setters. For your Course table:

class Course {

    protected $id;
    protected $title;
    protected $description;

    public function getId() {
        return $this->id;
    }

    public function setId($id) {
        $this->id = $id;
    }

    public function getTitle() {
        return $this->title;
    }

    public function setTitle($title) {
        $this->title = $title;
    }

    public function getDescription() {
        return $this->description;
    }

    public function setDescription($description) {
        $this->description = $description;
    }

}

Then, you have the concept of Data Access Objects. You can create an abstract class that will be extended by your data access objects:

abstract class AbstractDAO {

    protected $pdo;

    public function __construct($pdo)
    {
        $this->pdo = $pdo;
    }

    abstract public function find($id);

    abstract public function findAll();

    abstract protected function buildModel($attributes);
}

For your course table:

class CourseDAO extends AbstractDAO {


    public function find($id) {
        $statement = $this->pdo->prepare("SELECT * FROM courses WHERE id = :id");
        $statement->execute(array(':id' => $id));
        $result = $statement->fetch(PDO::FETCH_ASSOC);

        return $this->buildModel($result);
    }

    public function findAll() {
        $statement = $this->pdo->prepare("SELECT * FROM courses");
        $statement->execute();
        $results = $statement->fetchAll(PDO::FETCH_ASSOC);

        $courses = [];
        foreach($results as $row)
            $courses[] = $this->buildModel($row);

        return $courses;
    }

    public function findByTitle($title)
    {
        ...
    }

    public function create(Course $course)
    {
        ...
    }

    protected function buildModel($attributes) {
        $course = new Course();

        $course->setId($attributes['id']);
        $course->setTitle($attributes['title']);
        $course->setDescription($attributes['description']);

        return $course;
    }
}

Modern frameworks do this automatically, but I think it is good to understand how it works before using powerful tools like Eloquent or Doctrine

user_37884700
Related