PHP PDO

The PDO is an extension of PHP that defines an interface for accessing databases through PHP. This allows to switch the underlying database without having to modify the code itself.

For a school project, me and three other colleagues had to create a website from scratch using PHP. To make it easier, I created a system that would implement PDO while reflecting the database structure.

PDO Manager

In order to make the bridge between the PDO interface and the external code, I had to create a manager that would handle basic tasks. For example, it was able to connect to the database and call stored procedures and functions with the given arguments.

The external code would simply need to run the following code in order to directly call procedures and functions:

$a = 10;
$b = "0x0";
PDO_Manager::call_procedure("test", $a, $b, null);

The manager would then automatically create the query needed to call it. The advantage is also that the manager would convert PHP values into SQL values. For example, it would convert null into NULL, or true into 0 or 1.

PDO Object

The main problem we had with the current use of PDO is that elements are handled through string maps. This can be insecured, because each mention of a column needs the exact column name. The types might also be wrong due to most of the values being strings.

This problematic was solved by creating a class that would bridge this gap:

class User extends PDO_Object
{
    #[PDO_Object_Id("idUser")]
    public string $Id = "";
    
    #[PDO_Object_Id("alias")]
    public string $Alias = "";
    
    #[PDO_Object_Id("name")]
    public string $Name = "";
}

By giving a string map, the object would assign each value to the correct property, following the column's name defined in the attribute. This allows to separate the database names and the PHP names, which can be different due to different conventions.

It also allows to have type safety that are more understandable than before:

function updateUser(array $user): void
{
    // Need to assume that the array has the correct values
}

function updateUser(User $user): void
{
    // Prevents giving the wrong values
}

You can also add instance functions to these classes to make special queries. For example, you could call $user->censorName() to call special queries or functions from a given state.

Utilities

While adding the support for custom queries, I also added custom functions to help create the query:

// Instead of this:
User::select(
    [User::NAME],
    "ORDER BY " + User::NAME + ", " + User::ALIAS + " LIMIT " + 5 + " OFFSET " + 10
);

// You could do this:
User::select(
    [User::NAME],
    orderByAll([User::NAME], [User::ALIAS]),
    limit(5, 10)
);

It hides the exact implementation of the queries whilst keeping it understandable and easily modifiable. You would only need to modify the function limit to change its behaviour.

Conclusion

This little project is very useful to efficiently handle oriented-object problems. It unifies the easiness of oriented-object programming while building upon the interface of PDO.