Right way to handle database access in PHP OOP

Question

I need some help with database access in PHP. I'm trying to do things the OOP way but I'm not sure if I'm heading the right way.

Lets say I have a class Person, for example:

class Person {
    private $id;
    private $firstname;
    private $lastname;
    // maybe some more member variables

    function __construct($id = NULL) {
        if(isset($id)) {
            $this->id = $id;
            $this->retrieve();
        }
    }

    // getters, setters and other member functions

    private function retrieve() {
        global $db;

        // Retrieve user from database
        $stmt = $db->prepare("SELECT firstname, lastname FROM users WHERE id = :id");
        $stmt->bindParam(":id", $this->id, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetch();

        $this->firstname = $result['firstname'];
        $this->lastname = $result['lastname'];
    }

    function insert() {
        global $db;

        // Insert object into database, or update if exists
        $stmt = $db->prepare("REPLACE INTO users (id, firstname, lastname) VALUES (:id, :firstname, :lastname)");
        $stmt->bindParam(":id", $this->id, PDO::PARAM_INT);
        $stmt->bindParam(":firstname", $this->firstname, PDO::PARAM_STR);
        $stmt->bindParam(":lastname", $this->lastname, PDO::PARAM_STR);
        $stmt->execute();
    }
}

Note that this is just an example I just wrote to describe my question, not actual code I use in an application.

Now my first question is: is this the correct way to handle database interaction? I thought this would be a good way because you can instantiate an object, manipulate it, then insert/update it again.

In other words: is it better to handle database interaction inside the class (like in my example) or outside it, in the code that instantiates/uses the class?

My second question is about updating a whole bunch of rows that may or may not have been modified. Lets say the class Person has a member variable $pets[], which is an array containing all the pets that person owns. The pets are stored in a separate table in the database, like this:

+---------+-------------+---------+
|  Field  |    Type     |   Key   |
+---------+-------------+---------+
| pet_id  | int(11)     | PRI     |
| user_id | int(11)     | MUL     |
| name    | varchar(25) |         |
+---------+-------------+---------+

Lets say I modified some pets in the Person object. Maybe I added or deleted some pets, maybe I only updated some pet's names.

What is the best way to update the whole Person, including their pets in that case? Lets say one Person has 50 pets, do I just update them all even if only one of them has changed?

I hope this is clear enough ;)

EDIT:

Even more importantly, how do I handle deletions/insertions at the same time? My current approach is that on an "edit page", I retrieve a certain Person (including their pets) and display/print them in a form for the user to edit. The user then can edit pets, add new pets or delete some pets. When the user clicks the "apply" button, the form gets POSTed back to the PHP script.

The only way I can think of to update these changes into the database is to just remove all pets currently listed in the database, and then insert the new set of pets. There are some problems with this though: first of all, all rows in the pets table are deleted and reinserted on every edit, and second, the auto increment id will take huge leaps every time because of this.

I'm feeling I'm doing something wrong. Is it just not possible to let users remove/add pets and modify existing pets at the same time (should I handle those actions separately)?

Answer 1

What you're trying to accomplish is a task called object-relational mapping (mapping objects to tables in a relational database and vice-versa). Entire books have been written on that, but I'll try to give a short overview.

Now my first question is: is this the correct way to handle database interaction? I thought this would be a good way because you can instantiate an object, manipulate it, then insert/update it again.

It's a valid approach. However, in general, you should try to adhere to Separation of Concerns. In my opinion, modelling a domain entity (like a person, in your case) and storing this object in/loading from a database are two (arguably three) different concerns that should be implemented in separate classes (again, personal opinion!). It makes unit-testing your classes very difficult and adds a lot of complexity.

Regarding ORM, there are several design patterns that have emerged over time. Most prominently:

• Active Record is basically the approach that you've already suggested in your question; it tightly couples data and data access logic together in one object. In my opinion not the best approach, because it violates Separation of Concerns, but probably easiest to implement.

• Gateways or Mappers: Try to create a separate class for accessing your Persons table (something like a PersonGateway. That way, your Person class contains only the data and assorted behaviour, and your PersonGateway all kinds of insert/update/delete methods. A mapper on the other hand might be a class that converts generic database result objects (for instance a row returned by a PDO query) into Person objects (in this case, the Person class does not need to be aware of the existence of such a mapper class).

What is the best way to update the whole Person, including their pets in that case? Lets say one Person has 50 pets, do I just update them all even if only one of them has changed?

Again, several possibilities. You should map our Pet as a separate class in any case.

• Keep the pets in their separate table and implement your own data access logic for this class (for example using one of the patterns mentioned above). You can then individually update, insert or delete them at will. You can keep track of added/deleted pets in your parent object (the person) and then cascade your update operations when you persist the parent object.

• Embed the pets collection inside your Persons table. Depending on the average size of this collection, and how you might want to query them, this might not be a good idea, though.

If your project gains complexity, you might also want to have a look at ORM frameworks (like for example the Doctrine ORM) that take care of these problems for you.

Answer 2

In answer to question 1:

I would suggest having the sql in the class, and feed in the condition via the argument as per your example. As they would (should) all relate to a table of data representing Person objects.

On your second:

If pets are in a second table, I would suggest you having this as a separate class, with different sql queries. The variable $pets[] in your example can hold the pet_ids and you can have separate sql in the Pet class to do any changing, adding or removing as necessary.