Getting Started with FluentPDO

You know the story: writing SQL queries is so boring. Especially when you don’t have time to do it. If you feel like me, today we are going to see something really cool: Fluent PDO. If the term “PDO” sounds new to you, don’t worry. It’s a really simple concept: in the PHP world PDO stands for Persistent Data Object and it helps you abstract some basic database-related operations (like inserts, updates, deletes etc.). It’s a layer between you and the database.

The result? No more SQL queries. Maybe this is not the first one you have seen: there are many similar projects out there and every single one has its key features. Fluent’s key feature is a great JOIN Query Builder.

Our FluentPDO Test Project

First of all, we will need a sample project to work with. Let’s think… what about a simple multi-user wishlist?

There are going to be many users, and each will have favorite products. For every user we will store the first name, last name and the signup date. For every item we will store the name, brand, price and the related user id.

I am going to use a simple MySQL database. Here’s the structure for our data:

You will have to specify your connection details in the PDO constructor method. Type your database name after the dbname= piece in the first parameter, then write your username and password as second and third arguments.

Then, you will pass the PDO object as a parameter for the FluentPDO object constructor.

That’s all, FluentPDO does not need anything else to work. No extra configuration.

Basic Select Operations

We already have some dummy data. Let’s start with the “Hello World” of SQL queries. A simple select with a where and the user primary key id as a parameter to retrieve basic information.

Nothing hard to understand, here. FluentPDO has a good and readable syntax, so it’s really easy to understand what we are doing.

The from() method is used to set the right table. The where() method is used to filter our results with the same name clause. By default, in the where() method you just have to specify the field name and the value. The “=” is implied. Of course, you can also use different comparison operators. In that case you will have to write them right after the field name.

$fpdo->from('items')->where('price >', 1000);

Getting the results is very easy: they are stored in the $query object we just used. You can iterate it with a foreach cycle as the example shows.

In that specific case (searching an item by its primary id) we can also use a shortcut in the from() method:

FetchAll

Last, but not least, we have the fetchAll() method.

Here’s the syntax:

fetchAll($index = '', $selectOnly = '')

With fetchAll() we have complete control on what we are taking from the result. The first parameter, $index, is the field used as index, the $selectOnly is useful to specify what fields you want to pick up.

The method insertInto() is used to specify what table you want to use for the operation. Then, you will have to use the values() method to assign the desired values (in this case they are stored in the $values associative array).

The last step will be the execute() method, which will return the new record’s primary key.

Delete

If you want to delete a record knowing its primary key, you can do it with the deleteFrom() shortcut above.

Note: as you can see from the examples here, you must use the execute() method to run the delete query. If you don’t, you will not change anything on the database. The same thing works also for inserts and updates. Keep it in mind.

Advanced Features

As I told you before, every project of that kind has its unique features. No exceptions for FluentPDO: we are going to analyse two of these features: the Join Query Builder and the Debugger.

The Join Query Builder

Probably the most important unique feature of FluentPDO. The builder is really useful if you want to simplify your work and write less code. Let’s see how to use it.

We are going to start with a “classic” join query made with FluentPDO.

Conclusion

FluentPDO is a small and simple project. It is absolutely not suitable for every project, and could be improved – especially seeing as it’s been dormant for six months now – but it could be a good choice for a little/medium applications, just in case you don’t want to involve a big framework in the game. Thanks to some features like the Join Query Builder, it’s a good compromise.

Francesco is a web developer and consultant from Italy. He is the founder of Laravel-Italia, the official Italian Laravel Community, and writes for HTML.IT, the first italian web development portal. He also translated some books about Laravel. In the meantime he follows other projects, works as a freelance backend consultant for PHP applications and studies IT Engineering in Rome. He loves to learn new things, not only about PHP or development but everything. He hopes to work for IBM, sooner or later.