title: Right way to handle database access in PHP OOP
body: <p>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.</p>
  
  <p>Lets say I have a class Person, for example:</p>
  
  <pre><code>class Person {
      private $id;
      private $firstname;
      private $lastname;
      // maybe some more member variables
  
      function __construct($id = NULL) {
          if(isset($id)) {
              $this-&gt;id = $id;
              $this-&gt;retrieve();
          }
      }
  
      // getters, setters and other member functions
  
      private function retrieve() {
          global $db;
  
          // Retrieve user from database
          $stmt = $db-&gt;prepare("SELECT firstname, lastname FROM users WHERE id = :id");
          $stmt-&gt;bindParam(":id", $this-&gt;id, PDO::PARAM_INT);
          $stmt-&gt;execute();
          $result = $stmt-&gt;fetch();
  
          $this-&gt;firstname = $result['firstname'];
          $this-&gt;lastname = $result['lastname'];
      }
  
      function insert() {
          global $db;
  
          // Insert object into database, or update if exists
          $stmt = $db-&gt;prepare("REPLACE INTO users (id, firstname, lastname) VALUES (:id, :firstname, :lastname)");
          $stmt-&gt;bindParam(":id", $this-&gt;id, PDO::PARAM_INT);
          $stmt-&gt;bindParam(":firstname", $this-&gt;firstname, PDO::PARAM_STR);
          $stmt-&gt;bindParam(":lastname", $this-&gt;lastname, PDO::PARAM_STR);
          $stmt-&gt;execute();
      }
  }
  </code></pre>
  
  <p>Note that this is just an example I just wrote to describe my question, not actual code I use in an application.</p>
  
  <p>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.</p>
  
  <p>In other words: is it better to handle database interaction <strong>inside</strong> the class (like in my example) or <strong>outside</strong> it, in the code that instantiates/uses the class?</p>
  
  <p>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 <em>$pets[]</em>, which is an array containing all the pets that person owns. The pets are stored in a separate table in the database, like this:</p>
  
  <pre><code>+---------+-------------+---------+
  |  Field  |    Type     |   Key   |
  +---------+-------------+---------+
  | pet_id  | int(11)     | PRI     |
  | user_id | int(11)     | MUL     |
  | name    | varchar(25) |         |
  +---------+-------------+---------+
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>I hope this is clear enough ;)</p>
  
  <p>EDIT:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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)?</p>