Back in 2010, I was building a hosted VOIP PBX that required a LOT of calls to the database. I wanted to make it as simple as I could without having to import a huge ORM (you know which ones I’m talking about). PicoORM is a very tiny (10.2kb) library for PHP 7.1 and above that provides an easy way of working with databases by representing table columns as properties of an object. It provides complete developer control and doesn’t get in the way of rapid development.
Obtaining PicoORM
- Clone it from GitHub at https://github.com/paigejulianne/picoorm.git and require it at the top of your PHP scripts
- Add it via composer using composer require paigejulianne/picoorm within your project
Example Database Used Throughout this Post
This SQL can be imported directly into an existing MySQL/MariaDB database to rebuild the structure.
CREATE TABLE `user` (
`id` bigint(20) UNSIGNED NOT NULL,
`email` varchar(255) NOT NULL,
`password_hash` varchar(255) NOT NULL,
`acl` tinyint(3) UNSIGNED DEFAULT 0
);
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
Setting Up Your Connection
PicoORM relies heavily on PDO and its database drivers, which must be installed and working correctly on your system. The global variable _PICO_PDO is simply a PDO object. The simplest example, which connects to a MySQL or MariaDB database is:
$GLOBALS['_PICO_PDO'] = new PDO("mysql:host=localhost;db_name=database", "username", "password");The exact PDO connection string will depend on which database you are using. See a list of PDO Database drivers and find their connection string at https://www.php.net/manual/en/pdo.drivers.php
Inheriting the PicoORM Class
For each table that you wish to use PicoPDO with, you must create a new class that extends the base PicoORM class. This is as simple as::
class User extends PicoORM {
// custom code here
}- Your class name must match the name of your table. In MySQL/MariaDB, table names are case-insensitive.
- You do not necessarily need an id or autonumbered field in your table.
Creating a Record
Say we want to create a new user in our user table. Using the User class, we can do the following:
$email = "example@paigejulianne.com";
$password_hash = password_hash("example");
$newUser = new User();
$newUser->email = $email;
$newUser->password_hash = $password_hash;
$newUser->save();Alternatively, you can set fields dynamically and all at once by using the method setMulti
$newUser = new User();
$newUser->setMulti(['email' => $email, 'password_hash' => $password_hash]);Checking to Ensure a Record Exists
There are two ways to check to see if a record exists in the database. The first way uses the static method exists. The other attempts to create the object. If you use the latter method, you’ll need to check to see if one of the column fields is set.
// returns a simple true/false if the record exists in the table
$userExists = User::exists($email, "email");
// returns an object based on email and check that the record has an `id` column that is set to *something*
$user = new User($email, "email");
if (isset($user->id)) {
// true
} else {
// false
}Retrieving a Record
As you saw in the previous example, retrieving a record is simple. If your table contains a column named id, you can simply pass the value in the object constructor. However, if you want to retrieve a record on any other field, you can pass the column name as the second parameter to the constructor.
// fetch a user based on `id` column
$user = new User(1);
// fetch a user based on `email` column
$user = new User("example@paigejulianne.com", "email");Retrieving Multiple Records
You can choose to retrieve multiple objects by using the getAllObjects and pass it multiple “filters”. For example, to filter based on the column acl, you can do the following:
$filter = [ ['acl', '>=', 0] ];
$allUsers = User::getAllObjects('id', $filter);If only one-row matches, you’ll get a single User object. But, normally this will return an array of User objects.
Getters and Setters
As seen in our user creation script, setting the value of a column is as simple as setting an object property. So, retrieving a column value is just as easy.
$user = new User(1);
echo 'This user\'s email address is ', $user->email;
// changing email address
$user->email = "picoorm@paigejulianne.com";Writing Changes to the Database
By default, PicoORM doesn’t write changes to the database until you either call the save method or the object is destroyed (for example, the script is ending). I recommend calling save every time you set a field to prevent race conditions.
// explicit save
$user->email = "picoorm@paigejulianne.com";
$user->save();
// implicit save
$user->email = "example@paigejulianne.com";
die(); // at this point, the object is destroyed by PHP.
// However, under the covers, PicoORM calls the
// save() method to write changes to the database.Implementing Your Own Functions
Your class can be extended to implement your own functions.
class User extends PicoORM {
// returns a boolean if the new user was created
public static function createNewUser($email, $password_plain, $acl = 0) {
if (!self::exists($email, 'email')) {
$newUser = new User();
$newUser->email = $email;
$newUser->password_hash = password_hash($password_plain);
$newUser->save();
return true;
} else {
return false;
}
}
// login the user and set a session variable if password matches and returns a boolean
public static function loginUser($email, $password_plain) {
$user = new User($email, 'email');
if (isset($user->id)) {
if (password_verify($password_plain, $user->password_hash)) {
$_SESSION['user_id'] = $user->id;
return true;
} else {
// password did not match
return false;
}
} else {
// user not found
return false;
}
}
}Working Directly with SQL
Yes, you can work directly with SQL through PicoORM using three static methods, depending on your needs just as easily as working with the built-in methods. You can also use placeholders in your SQL like you would with a PDO statement (and strings will be properly escaped).
If you use the “magic” text _DB_ in your SQL statement, it will be replaced by the table name (which is based on the class name).
// fetch one row from the database
$email = "example@paigejulianne.com";
$sql = "SELECT * FROM _DB_ WHERE email = ?";
$resultArray = User::_fetch($sql, [$email]);
// fetch multiple rows from the database
$acl = 0;
$sql = "SELECT * FROM _DB_ WHERE acl >= ?";
$resultArray = User::_fetchAll($sql, [$acl]);
// fetch a PDOStatement to do what you want with it
$statement = User::_doQuery($sql, [$acl]);You can also use the base class of PicoORM to access a database by passing a third, optional parameter to any of the above methods.
$email = "example@paigejulianne.com";
$sql = "SELECT * FROM _DB_ WHERE email = ?";
$resultArray = PicoORM::_fetch($sql, [$email], 'user');Working with Other Databases
If for some reason, you have a table in a database that isn’t in the database you specified in your connection string (see the top of this tutorial), as long as the database user has access, you can still use PicoORM with it. Simply use the database name as the root name and the table name as the class name. For example:
// work with a table called `Transactions` in the `Log` database
class Log\Transactions extends PicoORM {
}Bonus Content
If you made it this far, congratulations! Here’s a helper method that you might find useful. Characters that could be ambiguous (or hard to discern) are removed.
const ALLOWED_CHARS = "abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789";
const ALLOWED_CHAR_COUNT = 55;
public static function generateRandomString($length = 6): string {
for ($i = 0; $i < $length; $i++) {
$random .= self::ALLOWED_CHARS[rand(0, self::ALLOWED_CHAR_COUNT - 1)];
}
return $random;
}Getting Help / Suggestions / Bug Reports / Feedback
For help and assistance, please use GitHub discussions, found at https://github.com/paigejulianne/picoorm/discussions
All suggestions, bug reports, or feedback must be submitted through GitHub at https://github.com/paigejulianne/picoorm/issues