Completing the round: updating and deleting rows - PHP

I suppose that the first example has been more than illustrative for introducing several features specifically inherent to PHP 5. From this point, the rest of the operations for either updating or deleting a row from the database table shouldn't be a troubling process. Therefore, here's the sample code for updating a row:

// include class files
function __autoload($class_name) {
    require_once 'classes/'.$class_name.'class.php5';
}
try {
    // connect to MySQL
    $db=new MySQL(array('host'=>'host','user'=>'user',
         'password'=>'password','database'=>'databasename'));
    // instantiate a new DBIGenerator object
    $gn=new DBIgenerator('users','user','classes/');
    // generate class file
    $gn->generate();
    // get $user object
    $user=$gn->getObject();
    // set object properties
    $user->setid($_GET['id']);
    $user->setfirstname('Paul');
    $user->setlastname('Hedgecomb');
    // update table row
    $user->update();
    echo 'Row successfully updated';
}
catch (Exception $e){
    echo $e->getMessage();
    exit();
} 

The above example implements the same concepts described in the first case. We directly connect to MySQL, select the database, and instantiate a "DBIGenerator" object. Then, the "user" DB interface class is generated, returning a $user object. Next, a given row is updated with the new values for the "firstname" and "lastname" fields.

A short explanation is in order here. Notice that we're obtaining the ID of the row to be updated from a hypothetical "id" variable passed on the URL querystring, as we see below:

$user->setid($_GET['id']);

This parameter usually might be dynamically generated from a regular link, similar to this basic example:

<a href="update.php?id=10" title="update row">Update row</a>

I guess you get the idea. Finally, the row is updated by calling the "update()" method:

$user->update();

As you might guess, deleting a row is nearly identical to the updating process. It's as simple as this:

// include class files
function __autoload($class_name) {
    require_once 'classes/'.$class_name.'class.php5';
}
try {
    // connect to MySQL
    $db=new MySQL(array('host'=>'host','user'=>'user',
          'password'=>'password','database'=>'databasename'));
    // instantiate new DBIGenerator object
    $gn=new DBIgenerator('users','user','classes/');
    // generate class file
    $gn->generate();
    // get $user object
    $user=$gn->getObject();
    // delete row from table
    $user->setid($_GET['id']);
    $user->delete();
    echo 'Row successfully deleted';
}
catch (Exception $e){
    echo $e->getMessage();
    exit();
} 

Wasn't that simple? In this case, we've replaced the "update()" method with the "delete()" method, thus removing the specified row from the database table.

Okay, I think that's all for now. Having described in detail how DML operations are performed using the PHP 5 updated version of the "DBIGenerator" class, our round trip around database interfaces has already finished. As you know, every journey has to end, so be patient for a little longer and join me in reading the conclusions.

Conclusion

Over this series, we've learned the core concepts behind object-oriented database interfaces, as an efficient and secure programming mechanism to access databases utilizing a single communication point.

Moreover, we've ranged from a simplistic approximation, by accomplishing single-row DML operations, to a more complex application, working with many database interfaces and multiple tables. Certainly the experience has proven to be instructive and truly fun.

Database interfaces are powerful tools for implementing in large projects, where maintenance of SQL code might be a sometimes cumbersome task. Thus, if your next web project looks like a 10,000 line application, consider implementing them to solve most of the hard-coded SQL issues.