Record Creation - PHP

After connecting to the database, your "Create, Read, Update, and Delete" (CRUD) application must be able to create rows in the database.

CRUD

The acronym CRUD stands for Create, Read, Update and Delete. These are the basic foundations of any application that interacts with a database. Many PHP web applications are examples of CrudScreen applications (http://c2.com/cgi/wiki?CrudScreen).

The sample application saves bookmarks to a database, so let's name the Active Record class Bookmark. To create a new bookmark, use new to create a Bookmark and set the instance's properties. When all of the (mandatory) properties are set, use the save() method to store the bookmark in the database.

This test captures that intent: 

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function testNew() {
    $link = new Bookmark;
    $link->url = 'http://simpletest.org/';
    $link->name = 'SimpleTest';
    $link->description = 'SimpleTest project homepage';
    $link->tag = 'testing';
    $link->save();
    $this->assertEqual(1, $link->getId());
  }
}

According to this test, the class Bookmark has a few public attributes and a save() method. After the instance is saved in the database, getId() should return the database row ID assigned to this Bookmark.

Here are the Bookmark class attributes:

  class Bookmark {
    public $url;
    public $name;
    public $description;
    public $tag;
  }

Let's turn to the save() method. It requires a database connection, so let's use the DB::conn() connection factory in the constructor:

  class Bookmark {
    protected $id;
    protected $conn;
    // ...
    public function __construct() {
      $this->conn = DB::conn();
    }
  }

$conn is now a database connection suitable for save() to use.

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

The ADOdb MySQL driver supports positional parameter substitution and also properly quotes the parameters. SQL parameters are indicated in a query by question marks (?) and you pass the substitution values in an array as a second parameter to the execute() method.

The Insert_ID() method should catch your eye: it returns the value of the AUTO_INCREMENT field from the last executed insert statement.

So far, the tests have proven that attributes can be set, that save() is functional,  and that  the $id attribute has been set to 1. Let's dig a little more into the database table and verify that the other bookmark attributes have been set properly, too.

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function testNew() {
    $link = new Bookmark;
    $link->url = 'http://simpletest.org/';
    $link->name = 'SimpleTest';
    $link->description = 'SimpleTest project homepage';
    $link->tag = 'testing';
    $link->save();
    $this->assertEqual(1, $link->getId());
    // fetch the table as an array of hashes
    $rs = $this->conn->getAll('select * from bookmark');
    $this->assertEqual(1, count($rs), 'returned 1 row');
    foreach(array('url', 'name', 'description', 'tag') as $key) {
      $this->assertEqual($link->$key, $rs[0][$key]);

    }
  }
}

The highlighted code fetches the entire bookmark table. The getAll() method executes the passed query and returns the resultset as an array of row hashes. The assertEqual() line validates that only a single row is present in the result test. The foreach loop compares the attributes of the object $link to fields in the row returned.

The code works, but adding bookmarks this way-setting each attribute by hand-can get a bit tedious. Instead, let's add a convenience method to the test case to facilitate adding bookmark objects.

The ActiveRecordTestCase::add() method takes four parameters and creates and inserts a new Active Record Bookmark object. And just in case you want to use the new object in tests later, add() returns the created Bookmark object as well.

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function add($url, $name, $description, $tag) {
    $link = new Bookmark;
    $link->url = $url;
    $link->name = $name;
    $link->description = $description;
    $link->tag = $tag;
    $link->save();
    return $link;
  }
}

You can actually write a test method inside the test case to prove this works:

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function testAdd() {
    $this->add('http://php.net', 'PHP', 
      'PHP Language Homepage', 'php');
    $this->add('http://phparch.com', 'php|architect', 
      'php|arch site', 'php');
    $rs = $this->conn->execute('select * from bookmark');
    $this->assertEqual(2,$rs->recordCount());
    $this->assertEqual(2,$this->conn->Insert_ID());
  }
}

Now that bookmarks can be created and saved to the database, let's add a way for an Active Record Bookmark object to easily retrieve data from the database and store the values as instance attributes. A common technique to create an Active Record object is to pass an identifier such as the bookmark ID (or some set of criteria) to its constructor and load the row associated with that ID from the database. Here is a test that demonstrates that:

class ActiveRecordTestCase extends UnitTestCase {
  // ...
  function testCreateById() {
    $link = $this->add(
      'http://blog.casey-sweat.us/',
      'My Blog',
      'Where I write about stuff',
      'php');
    $this->assertEqual(1, $link->getId());  
    $link2 = new Bookmark(1);
    $this->assertIsA($link2, 'Bookmark');   
    $this->assertEqual($link, $link2);
  }
}

This test passes an ID to the constructor, something the existing tests do not do. Passing an ID has to be optional, because existing tests that create new, empty Bookmark instances must continue to work.

Here's some code to realize the requirements of the test(s):

class Bookmark {
  // ...
  const SELECT_BY_ID = 'select * from bookmark where id = ?';
  public function __construct($id=false) {
    $this->conn DB::conn();
    if ($id) {
      $rs = $this->conn->execute(
        self::SELECT_BY_ID
        ,array((int)$id));
      if ($rs) {  
        $row = $rs->fetchRow();
        foreach($row as $field => $value) {
          $this->$field = $value;
        }
      } else {
        trigger_error('DB Error: '.$this->conn->errorMsg());
      }
    }
  }
  // ...
}

This constructor allows an $id parameter, which is false by default. If a non-false $id parameter is passed, then Bookmark queries the database for a row in the bookmark table with the corresponding ID. If such a row exists, all of the attributes of the object are set to the values recovered by the database query.