greenman.dk - PHP Database Object / Abstraction

In the process of coding a spare time project, friendsBoard (an ajax chat tool), I wanted a better, cleaner way to deal with reads and writes to the database. SQL code in the frontend scripts, or template layer, is a recipe for disaster. The solution I created, called Sql2Object, hides all sql code away in a base php class. Other classes extend this base class, while providing information about which table they should represent. When objects are instantiated from this child they each represent a row from the table in question, with the columns being represented by properties on the object. Sounds weird? It really isn't - it's more intuitive than representing table data as an array, in my opinion.

Let me give you an example. Most projects need some sort of user class, so let's create one.


 <?php

 include_once 'Sql2Object.class.php';



 class User extends Sql2Object

 {

    const TABLE_NAME = 'friendsBoard_User';




    function __construct()

    {

        parent::__construct(self::TABLE_NAME, 'id',

            array('username', 'password', 'email',


                'longName', 'lastSeenUts', 'activated'));

    }

 }

 ?>

So, now we have a user class, capable of selecting and inserting users in the friendsBoard_User table in our database. We only tell the class what table to use, which column is the primary key, and what other columns the table has. A lot of bang for the buck, if you ask me :-) (note: the table name could be parsed from the class name, and the columns could be found by inspecting the table on instantiation, but I prefer doing things this explicit, better performing, way). Let's try to use it, shall we:


 <?php

    $user = new User();

    $user->username = "Mike";

    $user->password = "hushverysecret";


    $user->email = "";

    if ($user->save()) {

        echo "<p>User saved!</p>";


    } else {

        echo "<p>Failed saving user, error: " . htmlentities($user->getLastError()) . "</p>";


    }

 ?>

In the code above we created the user Mike and saved him to the database. No SQL in sight, just objects, methods and properties. All input values are escaped by Sql2Object, eliminating SQL injection vulnerabilities by forgetting to escape some input somewhere.

Ok, we now need to have a look at Mike's e-mail, so let's find his object. Usually you would select a user based upon his primary key, but let's assume that 'username' is a unique column:


 <?php

    $user = new User();

    $user->username = "Mike";

    $mike = $user->findOne();


    if ($mike) {

        echo "<p>Mike's e-mail is '" . $mike->email . "'</p>";


    } else {

        echo "<p>Sorry, no user with the username Mike</p>";

    }


 ?>

The above finds and returns one object. Let's get an array of all activated users instead:


 <?php

    $user = new User();

    $user->activated = 1;

    foreach($user->findAll() as $userObject) {


        echo "<p>" . htmlentities($userObject->username) . " is activated!</p>";


    }

 ?>

"But," you say, "I want to use more complex conditionals." And how right you are. We shall find all activated users that have been active inside the last two days and have an id which is included in our array $favoriteUsers:


 <?php

    $favoriteUsers = array(3,7,21,5,98);

    $user = new User();


    $user->activated = 1;

    $user->addWhere('lastSeenUts > ' . strtotime("-2 day"));


    $user->setIsIn('id', $favoriteUsers);

    foreach($user->findAll() as $userObject) {


        echo "<p>" . htmlentities($userObject->username) . 

            " is activated, active and a favorite!</p>";


    }

 ?>

And you can do more stuff still, but I won't write a manual just yet ;-) Besides, you should have gotten the idea by now.