samedi 2 mai 2015

Right way to handle database access in PHP OOP

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

Aucun commentaire:

Enregistrer un commentaire