Updating Records - PHP

The Create and Read portions of CRUD are complete; what about Update? It makes sense to use save() to update an Active Record object, but as it is now, save() only handles INSERT statements. To recap, save() looks like this:

class Bookmark{
  // ...
  const INSERT_SQL = “
    insert into bookmark (url, name, description, 
      tag, created, updated)
    values (?, ?, ?, ?, now(), now())
    “;
  protected function save() {
    $rs = $this->conn->execute(
      self::INSERT_SQL
      ,array($this->url, $this->name, 
        $this->description, $this->tag));
    if ($rs) {
      $this->id = (int)$this->conn->Insert_ID();
    } else {
      trigger_error(‘DB Error: ‘.$this->conn->errorMsg());
    }
  }
}

However, after you already have a valid instance, you would rather see something like:

class Bookmark {
  // ...
  const UPDATE_SQL = “
    update bookmark set
      url = ?,
      name = ?,
      description = ?,
      tag = ?,
      updated = now()
    where id = ?
    “;
  public function save() {
    $this->conn->execute(
      self::UPDATE_SQL
      ,array(
        $this->url,
        $this->name,
        $this->description,
        $this->tag,
        $this->id));
  }
}

To differentiate between INSERT and UPDATE, you need to detect if a bookmark is new or if it’s been loaded from the database.  

First, refactor the two “versions” of save() into individual protected methods with the descriptive names insert() and update().

class Bookmark {
  // ...
  protected function insert() {
    $rs = $this->conn->execute(
      self::INSERT_SQL
      ,array($this->url, $this->name, 
        $this->description, $this->tag));
    if ($rs) {
      $this->id = (int)$this->conn->Insert_ID();
    }
  }
  protected function update() {
      $this->conn->execute(
        self::UPDATE_SQL
        ,array(
          $this->url,
          $this->name,
          $this->description,
          $this->tag,
          $this->id));
  }
}

Now you can change save() to look at this info:

class Bookmark {
  const NEW_BOOKMARK = -1;
  protected $id = Bookmark::NEW_BOOKMARK;
  // ...
  public function save() {
    if ($this->id == Bookmark::NEW_BOOKMARK) {
      $this->insert();
    } else {
      $this->update();
    }
  }
}

Just one last issue: timestamps change in the database whenever you insert or update a record. There is no other way to keep an accurate timestamp in the Bookmark other than making another trip to the database to retrieve it. Since this applies to either inserts or updates, change the Active Record class to always update the timestamp before leaving the save() method in order to prevent the latter from getting out of sync.

class Bookmark {
  // ...
  public function save() {
    if ($this->id == self::NEW_BOOKMARK) {
      $this->insert();
    } else {
      $this->update();
    }
    $this->setTimeStamps();
  }
  protected function setTimeStamps() {
    $rs = $this->conn->execute(
      self::SELECT_BY_ID
      ,array($this->id));
    if ($rs) {
      $row = $rs->fetchRow();
      $this->created = $row[‘created’];
      $this->updated = $row[‘updated’];
    }
  }
}

Bookmark gets to the heart of the ActiveRecord pattern: save() knows the SQL statement required to update or insert into the database table, knows the object’s current state, and can assemble the needed parameter substitution array from the object’s own attributes.  Let’s test it:

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function testSave() {
    $link = Bookmark::add(
      ‘http://blog.casey-sweat.us/’,
      ‘My Blog’,
      ‘Where I write about stuff’,
      ‘php’);
    $link->description = 
      ‘Where I write about PHP, Linux and other stuff’;
    $link->save();
    $link2 = Bookmark($link->getId());
    $this->assertEqual($link->getId(), $link2->getId());
    $this->assertEqual($link->created, $link2->updated);
  }
}

For now, let’s skip how to implement DELETE. There is an example in Chapter 16—The Data Mapper Pattern, but you can easily derive it from the insert() and update() methods.