Tiny ORM (Object Relational Mapper) for people with little object oriented experience
This article is meant to help PHP developers who are still using procedural programming make their first steps in object oriented programming by showing them how to access a database using an ORM. In this article we will be using MySQL but the concept behind an ORM can be applied to any relational database.
Download Tiny ORM1. The Procedural Way
PHP has many functions that allow a developer to access a database directly like mysql_connect(), mysql_fetch_array(), mysql_error(), etc. In the most simple of cases this is how you would normally use them to insert something into the database.
// Example 1.1
// Connect to the database
$db = mysql_connect('localhost', 'user', 'pass') or die (mysql_error());
// Select a database
mysql_select_db('dbname', $db) or die (mysql_error());
// The data you want to insert
$name = 'John';
// The sanitized version of your data (to prevent SQL injection attacks)
$s_name = mysql_real_escape_string($name);
// Execute a query
$result = mysql_query("INSERT INTO user (id, name) VALUES (NULL, '$s_name')", $db);
// get results from the query
if (mysql_error($db) != '') {
// The query produced an error
echo mysql_error($db);
} else {
return mysql_insert_id($db);
}
As you can see, you need many PHP lines for just one call to the database. Since you will need to select many things from the database in your project you may decide to wrap all this code into a function in order to keep your code DRY (Do not repeat yourself).
// Example 1.2
function insert_user($name) {
$db = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
// Select a database
mysql_select_db('dbname', $db) or die(mysql_error());
// The sanitized version of your data (to prevent SQL injection attacks)
$s_name = mysql_real_escape_string($name);
// Execute a query
$result = mysql_query("INSERT INTO user (id, name) VALUES (NULL, '$s_name')", $db);
// get results from the query
if (mysql_error($db) != '') {
// The query produced an error
echo mysql_error($db);
} else {
return mysql_insert_id($db);
}
}
// So the next time you need to insert users, you'd simply do:
$userId1 = insert_user('John');
$userId2 = insert_user('Jane');
$userId3 = insert_user('Bill');
This is much better than having a whole bunch of PHP code all over the place, but is still far from optimal. On most scenarios you'll need to pass a lot more parameters to this function, making it very hard to read. Let's pass the name, last name, email, edit privileges, delete privileges, last login date, user avatar, country id reference to another table, and the user type:
// Example 1.3
$userId = insert_user('John', 'Doe', 'john@example.com', 1, 0, '2010-02-10', 'john.jpg', 289, 'admin');
If you haven't read the function definition then you don't have a way to figure out what all the parameters are which is a bad thing since this will slow you down quite a bit. To solve that problem you might want to modify your function to accept an array of paremeters:
// Example 1.4 $new_user = array( 'nameFirst' => 'John', 'nameLast' => 'Doe', 'email' => 'john@example.com', 'hasEditPrivilege' => 1, 'hasDeletPrivilege' => 0, 'lastLogin' => '2010-02-10', 'avatar' => 'john.jpg', 'countryId' => '289', 'userType' => 'admin' ); $userId = insert_user($new_user);
At first sight, this is a rather better implementation than before but it still has some serious flaws. What if the user makes a typo in the array keys? Well, we solved the problem of making sure that the values we pass to the function match the table fields we want but we still need to know the exact array keys in order for this to work well. So, let's leave it at that for now and pretend you have made a whole bunch of these functions and put them together in a file.
You now have a huge file with a hundred or so functions when you decide you need to change the database schema a bit: you forgot to add a field to store the password! So you go back and modify ALL your functions and you waste massive hours changing every function call and function definition and since you made a few typos you spend another good bunch of hours debugging. By this time you wish you had written all those unit tests you lazily didn't do!
Now let's pretend you need to change the database name or the password... you get the point. Any little change in the database requires heavy changes in your code.
2. Doing Things The Tiny ORM Way
To solve this problem we will be using an ORM to manage our database. There are a few very powerful ORMs for PHP like Doctrine or Propel but they both have a very steep learning curve. So we'll make our own home grown version of an ORM. It won't have nearly as many fancy features as the other ORMs have but it will be extremely easy to implement and will save you hours of typing and bugtracking. It will also leave you -let's hope- enough knowledge to modify it and make it better suit your needs.
The concept is simple; one PHP class per table. To keep things simple we will have the class name be the same as the table name. So let's start by creating a couple of tables for our following examples. We want to have a user table ta can hold the user's first name, last name, email, and a one to one relation with a privilege table. The privilege table will define if the user can edit, write, or delete articles on a pretend blog application.
-- Example 2.1 CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nameFirst` varchar(50) DEFAULT NULL, `nameLast` varchar(50) DEFAULT NULL, `email` varchar(60) DEFAULT NULL, `privilegeId` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `nameFirst` varchar(50) DEFAULT NULL, `nameLast` varchar(50) DEFAULT NULL, `email` varchar(60) DEFAULT NULL, `privilegeId` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
So now you have your MySQL database setup with your empty tables. Let's setup our directory structure a little bit (you don't have to actually do this as the example files provided have all the files in the correct place):
/
+--scripts/
+- config.php Main configuration file you'll include in your entry points
+- class_autoloader.php Provides autoload functionality
+- generate_classes.php Generates classes based on your database schema
+- orm_base/
+- orm_user/
+- user/
+- DB.php Database singleton
+- DB_Basic.php Basic database functionality
+- DB_Deleter.php Used to delete more than one row at a time
+- DB_Selector.php Used to select more than one row at a time
+- IDB_Basic.php Interface for DB_Basic.php
Directory structure explained
The / folder will hold your index.php and any other entry points your app has. The scripts folder will hold your project configurations and any leftover function library that you may have from your procedural programming days. You want this directory to be as empty as possible because you want all your code to be as object oriented as possible. The scripts folder has three subfolders: orm_base will hold your ORM classes. You will never need to edit any of the files in this dir as they will be auto-generated by our ORM (more on this later). The orm_user dir will hold the ORM files that are customized by you. And finally the user dir, which holds our ORM functionality and any other classes generated by you which have nothing to do with ORM but with project specific needs.
Note: You'll find a few files already setup for you. Don't worry about them as we will see what they do as we go along.
Database setup
First you need to set up a database connection. Open the scripts/user/DB.php file and make the necessary changes to match your desired connection. The config you are looking for is right at the top. Don't worry if you don't understand the rest of the code; you don't need to, as you won't be needing to modify anything in there. For the curious out there, this particular class is a Singleton.
// Example 2.2 $this->host = 'localhost'; $this->user = ''; $this->pass = ''; $this->dbname = '';
You might have not noticed but none of the classes have an ending php tag ?^gt;. This is done on purpose and is meant to avoid accidentally outputting whitespace by the class. Whitespace is not a good thing to have if you will be sending headers to the browser with header(). We'll leave the ending tags out just in case. Before proceeding, make sure your DB is configured properly by opening the index.php file in the browser. Since there is nothing going on in the script so far, other than an attempt to connect to the database, if you don't see anything it means that your connection was successful, otherwise you'll get a PHP error message.
Generate classes based on your data model
Now open scripts/generate_classes.php and find the following array at the top:
// Example 2.3 $classNames = array( 'user' => 'User', 'privilege' => 'Privilege', );
This is where you will configure the table names and classes for your projects. So the keys on this array represent tables and the values represent classes. In our case the user table will be represented with the User class (notice the capital U), and the privilege table with the Privilege Class. Open this script in your browser and you will see something like this:
E:\htdocs\scripts\orm_base\Privilege_Base.php E:\htdocs\scripts\orm_user\Privilege.php E:\htdocs\scripts\orm_base\User_Base.php E:\htdocs\scripts\orm_user\User.php
Note: The file paths will be different on your system depending on what your document root is.
This output means that the script has generated four files based on your database schema. Two "base" files have been generated in the orm_base/ dir and two "user" files have been generated in the orm_user/ dir. Now try refreshing the browser and you will see the following:
E:\htdocs\scripts\orm_base\Privilege_Base.php E:\htdocs\scripts\orm_user\Privilege.php (skipped) E:\htdocs\scripts\orm_base\User_Base.php E:\htdocs\scripts\orm_user\User.php (skipped)
The files in the orm_user/ dir have not been generated again while the files in the orm_base/ dir have been overwritten. This is very important because all your customizations for this classes will have to be done in the orm_user/ files. This is part of the magic right here. Whenever you change anything in the database schema you must run the generate_classes.php file and the orm_base/ dir gets updated with the classes representing the new schema. Remember when you had to change all those functions by hand? Not anymore! The generate_classes.php file takes care of this for you.
3. Inserting objects into the database
Once you've generated your classes you are ready to use them. Let's start by creating a new user and their related privileges.
// Example 3.1
// Create new User object and store it in $user
$user = new User();
// Set it's properties
$user->set_nameFirst('John');
$user->set_nameLast('Doe');
$user->set_email('john@example.com');
// Dump it to screen
$user->dump();
First we instantiate a new User and store it in a variable called $user. We can then use the object's "setters" to populate it with the data we want. Our object hasn't been saved to the database because we need to enter a privilegeId but since we haven't entered the privilege in the database we don't know its id. Before we commit anything to the database let's make sure the object has been populated correctly by using the dump() method. This method outputs the object's properties in a human readable way and serves no purpose other than debugging.
User --------------------------
id:
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId:
-------------------------------
All the info we want is there so let's create a privilege, insert it into the database, then populate our user with the privilege id and commit the new user to the database.
// Example 3.2
// Create new Privilege object and store it in $privilege
$privilege = new Privilege();
$privilege->set_canWrite(0);
$privilege->set_canDelete(0);
$privilege->set_canEdit(1);
// Insert the privilege into the database
$privilege->insert();
// Let's see the new id
$privilege->dump();
/*
Privilege -------------------
id: 1
canEdit: 1
canWrite: 0
canDelete: 0
-----------------------------
*/
// Create new User object and store it in $user
$user = new User();
// Set it's properties
$user->set_nameFirst('John');
$user->set_nameLast('Doe');
$user->set_email('john@example.com');
// Now that we know the privilege, set it's id
$user->set_privilegeId($privilege->get_id());
$user->insert();
// Dump it to screen
$user->dump();
/*
User --------------------------
id: 1
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId: 1
-------------------------------
*/
As you can see, after you've executed an insert() method, the object id is immediately available for you to use.
4. Selecting objects from the database
Selecting rows is really easy. Before trying the following examples go ahead and create a few more users so that our tables have some more data in them.
If you know the row's id then creating an object for that row only takes one line.
// Example 4.1
$user = new User(4);
$user->dump();
/*
User --------------------------
id: 4
nameFirst: Kathy
nameLast: Rogers
email: kathy@example.com
privilegeId: 4
-------------------------------
*/
Our ORM has created objects for related tables too. This means that we can access privileges quite easily from our original user object without having to do any table joins or extra queries.
// Example 4.2 $user = new User(4); echo $user->privilege->get_canWrite(); echo $user->privilege->get_canEdit(); echo $user->privilege->get_canDelete(); */
You can also select a collection of objects like a WHERE clause, and do an ORDER BY at the same time. For this we will use the DB_Selector class which you have seen earlier on. The DB_Selector class only contains a static method which means you don't have to instantiate it, you can use it right away and instead of using -> to access its methods, you need to use double colons ::. The DB_Selector class takes four parameters. First you pass it an object that specifies what kind of objects you'd like it to return. This is how the class knows what table to do the search in. You can pass it three other optional arguments; a WHERE clause, an ORDER BY clause, and a LIMIT clause in plain old SQL format, respectively. The class always returns an array of objects of the type you pass it. Let's see it in action.
// Example 4.3
<?php
require 'scripts/config.php';
// Select ALL users
$users = DB_Selector::select(new User());
foreach ($users as $user) {
$user->dump();
}
/*
User --------------------------
id: 1
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId: 1
-------------------------------
User --------------------------
id: 2
nameFirst: Jane
nameLast: Doe
email: jane@example.com
privilegeId: 2
-------------------------------
User --------------------------
id: 3
nameFirst: Bob
nameLast: Smith
email: bob@example.com
privilegeId: 3
-------------------------------
User --------------------------
id: 4
nameFirst: Kathy
nameLast: Rogers
email: kathy@example.com
privilegeId: 4
-------------------------------
*/
// Select users whose last name starts with the letter d
$users = DB_Selector::select(new User(), "nameLast LIKE 'd%'");
foreach ($users as $user) {
$user->dump();
}
/*
User --------------------------
id: 1
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId: 1
-------------------------------
User --------------------------
id: 2
nameFirst: Jane
nameLast: Doe
email: jane@example.com
privilegeId: 2
-------------------------------
*/
// Select users and order them alphabetically descending by last name
$users = DB_Selector::select(new User(), '', 'nameLast DESC');
foreach ($users as $user) {
$user->dump();
}
/*
User --------------------------
id: 3
nameFirst: Bob
nameLast: Smith
email: bob@example.com
privilegeId: 3
-------------------------------
User --------------------------
id: 4
nameFirst: Kathy
nameLast: Rogers
email: kathy@example.com
privilegeId: 4
-------------------------------
User --------------------------
id: 1
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId: 1
-------------------------------
User --------------------------
id: 2
nameFirst: Jane
nameLast: Doe
email: jane@example.com
privilegeId: 2
-------------------------------
*/
// Select the first user entered in the database whose last name is Doe
$users = DB_Selector::select(new User(), 'nameLast = "Doe"', 'id ASC', 1);
foreach ($users as $user) {
$user->dump();
}
/*
User --------------------------
id: 1
nameFirst: John
nameLast: Doe
email: john@example.com
privilegeId: 1
-------------------------------
*/
5. Deleting objects from the database
If you know the object's id then deleting an object is really a one liner. Just don't forget to delete any related objects because the ORM won't do it for you. Again, if you use an ORM like Doctrine, it WILL do this for you and much more. Ours doesn't! By the way the insert() and deleteById() methods return a boolean value with the operation's result.
// Example 5.1 $user = new User(4); // Delete related privilege object $user->privilege->deleteById(); // Delete user object $user->deleteById();
There is a DB_Deleter class which works in exactly the same way as DB_Selector but it only takes two parameters: the object type you want to delete (again, this is so that the class knows which table to use when deleting rows, and the second parameter is the WHERE clause.
// Example 5.2 // Delete all the users whose last name is Doe DB_Deleter::delete(new User(), 'nameLast = "Doe"');
6. Updating objects on the database
To update an object simply call the UpdateById() method on it. Make sure to call it in all its related objects as well if you have changed them, or they won't get updated.
// Example 6.1
$user = new User(1);
// Update the field you want
$user->set_nameFirst('Jack');
// Commit the change to the database
$user->updateById();
// If you are only modifying the related object then you
// don't need to update the parent object. Only updating
// the related object will do.
$user = new User(4);
$user->privilege->set_canEdit(0);
$user->privilege->updateById();
7. Customizing your classes
Let's pretend you will be doing some things like outputting the user's name a lot. You could do it like this:
// Example 7.1 echo $user->get_nameFirst() . " " . $user->get_nameLast();
But it would much better to be able to do something like:
// Example 7.2 echo $user;
This returns an unfortunate error:
Catchable fatal error: Object of class User could not be converted to string in E:\htdocs\index.php on line 7
Well, let's convert it to a string then! This is were the orm_user/ dir comes into to play. Remember that when we generated the classes we said that the files in this dir weren't overwritten by the class generator? This is because you will customize the class' behaviour here and you don't want to lose your changes every time you run the class generator. Let's start with our first customization. Open the orm_user/User.php class and replace its content with the following:
// Example 7.3
class User extends User_Base {
public function __toString() {
return $this->get_nameFirst() . " " . $this->get_nameLast();
}
}
We can now echo our object and get what we expect instead of an error:
// Example 7.4 echo $user; // John Doe
8. Overriding built-in methods to add functionality
Let's say you wanted to keep track of when the user's row was last updated. First we'll add the corresponding field to our user table and run the generate_classes.php script to update our ORM classes.
alter table `tinyorm`.`user` add column `lastUpdated` int UNSIGNED NULL after `privilegeId`;
We've added an integer column that will store a UNIX timestamp with the user's last update date. One way of doing this would be to manually update the field in our code like this:
// Example 8.1 $user = new User(2); $user->set_lastUpdated(time()); $user->updateById();
That's ok and will get the job done, but since our project might be very big and we will be updating the users all over the place we might forget to update the timestamp. That's why we will override the updateByID() method to do this for us every time the user is updated. Open the orm_user/User.php class once again and add the following method to it:
// Example 8.2
public function updateById() {
// Update the field with the current time
$this->set_lastUpdated(time());
// Call the original updateById() to save the row
parent::updateById();
}
Now every time you call the updateById() method the lastUpdated field gets automatically updated. How about that? We just added an extra field to the database and customized our update method and you didn't need to write a single query by hand!
9. Recap
By using an ORM we've cut the amount of code by many many lines and avoided rewriting existing code every time we made a change to the database schema. Less rewriting means les bugs and also the code is much more powerful and readable than its procedural version. If you use a decent PHP IDE you will even get autocompletion as a bonus which makes working with objects even faster.
I hope this brief introduction will leave you wanting to jump to object oriented programming right away. I know you'll benefit from it greatly!
10. Cheatsheet
Example 10.1
// Create a new user
$user = new User();
// Set a property
$user->set_email('email@example.com');
// Get a property
echo $user->get_email();
// Insert
$user->insert();
// Update
$user->updateById();
// Delete
$user->deleteById();
// Related object setter
echo $user->privilege->set_canEdit(1);
//Related object getter
echo $user->privilege->get_canEdit();
//Related object update
echo $user->privilege->updateById();
// Select multiple users
$users = DB_Selector::select(new User());
// Select multiple users with WHERE clause
$users = DB_Selector::select(new User(), 'email LIKE "%@hotmail.com"');
// Select multiple users with WHERE and ORDER BY clauses
$users = DB_Selector::select(new User(), 'email LIKE "%@hotmail.com"', 'nameLast ASC');
// Select multiple users with WHERE, ORDER BY, and LIMIT clauses
$users = DB_Selector::select(new User(), 'email LIKE "%@hotmail.com"', 'nameLast ASC', 10);
// Select multiple users with only a LIMIT clause
$users = DB_Selector::select(new User(), '', '', 10);
// Delete multiple users
$users = DB_Deleter::delete(new User());
// Delete multiple users with WHERE clause
$users = DB_Deleter::delete(new User(), 'email LIKE "%@hotmail.com"');
