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:

… and here’s the SQL dump (with some dummy data).

CREATE TABLE IF NOT EXISTS items (   
        id int(11) NOT NULL AUTO_INCREMENT,   
        name varchar(100) NOT NULL,   
        brand varchar(100) NOT NULL,   
        price decimal(10,2) NOT NULL,   
        user_id int(10) unsigned NOT NULL,   
        PRIMARY KEY (id),   
        KEY user_id (user_id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

    INSERT INTO items (id, name, brand, price, user_id) 
    VALUES 
    (1, 'Last Awesome Phone', 'Awesome Brand', '550.00', 1), 
    (2, 'Last Awesome TV', 'Awesome Brand', '1200.00', 1), 
    (3, 'Fantastic E-Car', 'E-Cars Inc.', '80000.00', 2), 
    (4, 'Fantastic E-Bike', 'E-Bikes Co. Ltd.', '16000.00', 2);

    CREATE TABLE IF NOT EXISTS users (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,   
        first_name varchar(100) NOT NULL,   
        last_name varchar(100) NOT NULL,   
        signup_date datetime NOT NULL,   
        PRIMARY KEY (id) ) ENGINE=InnoDB  
        DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

    INSERT INTO users (id, first_name, last_name, signup_date) 
    VALUES 
    (1, 'Francesco', 'Malatesta', '2014-06-29 13:00:00'), 
    (2, 'John', 'Foo Bar', '2014-06-20 11:16:39');

    ALTER TABLE items   ADD CONSTRAINT items_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id);

Note: As you can easily imagine, this is not going to be a “complete” project. We are just trying FluentPDO, so we are not going to cover things like login, signup or application structure.

Installation

You install Fluent with Composer, including the library as a dependency:

"require": {
        ...
        "lichtner/fluentpdo": "dev-master"  
    }

Once done, you need to instantiate like this:

$pdo = new PDO("mysql:dbname=wishlist", "root", "password");
    $fpdo = new FluentPDO($pdo);

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.

$user_id = 1;

    $query = $fpdo->from('users')->where('id', $user_id);

    foreach($query as $row){
        echo 'Hello, ' . $row['first_name'] . ' ' . $row['last_name'] . '!';    
    }

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:

$query = fpdo->from('users', $user_id);

    // will be the same thing as...

    $query = $fpdo->from('users')->where('id', $user_id);

Let’s see something more complicated than this.

Select specific fields

If you want, you can select specific fields using the select() method right after the from(). All you have to do is tell FluentPDO what fields you want to pick up with an array.

Here’s an example:

$query = $fpdo->from('users')->select(array('first_name', 'last_name'))->where('id', $user_id);

Limit and Offset

It is very easy to set limit and offset parameters to retrieve only a certain number of rows from the database. You can use the limit() and offset() methods like this.

// selecting the first ten results...
    $query = $fpdo->from('users')->where('id', $user_id)->limit(10)->offset(0);

The only parameter for both methods is an integer specifying the desired value (number of items for limit(), number of items to skip for offset()).

Having, Group By and Order By

There are also available methods for “HAVING”, “GROUP BY” and “ORDER BY” instructions.

Let’s see them with some examples.

Order By

The orderBy() method is used to order results with specific criteria. Let’s make an example: here’s how to order results by price, from cheapest to most expensive.

$query = $fpdo->from('items')->orderBy('price');

If you want to invert the order (getting results from the most expensive to the cheaper) you just have to add “DESC” after the column you chose.

$query = $fpdo->from('items')->orderBy('price DESC');

Having

The having() method has a very simple syntax. In the next example we are filtering every item with a price lower than $2000.

$query = $fpdo->from('items')->having('price < 2000');

Quite simple.

You can use every comparison operator you need.

Group By

With the groupBy() method you can group results using a specific field as a criteria. Here we are showing an items count for every brand.

$query = $fpdo->from('items')->select('brand, COUNT(*) AS c')->groupBy('brand');

Note: you can specify an alias for a field just as you do in classic SQL.

Fetching Methods

Fetch

Using a foreach is not the only way to fetch results. What if we want to retrieve only the first result from the set?

Just use the fetch() method:

$query = $fpdo->from('users');
    $row = $query->fetch();

    var_dump($row);
    // will output:
    // array(4) { ["id"]=> string(1) "1" ["first_name"]=> string(9) "Francesco" ["last_name"]=> string(9) "Malatesta" ["signup_date"]=> string(19) "2014-06-29 13:00:00" }

You can also fetch a single column, specifying its name as a parameter.

$query = $fpdo->from('users');
    $firstName = $query->fetch('first_name');

    var_dump($firstName);
    // will output:
    // string(9) "Francesco"

FetchPairs

With fetchPairs() you can retrieve results as an associative array. Using a syntax like:

fetchPairs($column1, $column2);

you will get an output like

// [column1_value] => "column2_value"

Here’s an example, using user unique id and the first name.

$query = $fpdo->from('users');
    $row = $query->fetchPairs('id', 'first_name');

    var_dump($row);
    // will output:
    // array(2) { [1]=> string(9) "Francesco" [2]=> string(4) "John" }

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.

Here you have an example:

$query = $fpdo->from('users');
    $row = $query->fetchAll('id', 'first_name, last_name');

    var_dump($row);
    // will output:
    // array(2) { [1]=> array(3) { ["id"]=> string(1) "1" ["first_name"]=> string(9) "Francesco" ["last_name"]=> string(9) "Malatesta" } [2]=> array(3) { ["id"]=> string(1) "2" ["first_name"]=> string(4) "John" ["last_name"]=> string(7) "Foo Bar" } }

Note: the column used as index (id, in this example) is also included in the final array.

Ok, that’s enough for select operations. Let’s take a look at the other CRUD operations.

Insert, Update and Delete

FluentPDO is not just about selecting things. It also has classes for manipulating data in an easy way.

Let’s start with the Insert operation.

Insert

$values = array('first_name' => 'Joe', 'last_name' => 'Doe', 'signup_date' => '2014-06-30 11:00:00');       
    $query = $fpdo->insertInto('users')->values(values);    
    $insert = $query->execute();

    var_dump($insert);
    // will output:
    // string(1) "3"

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.

You can also use this shortcut, if you want:

$query = $fpdo->insertInto('users', $values);

Update

The update method is really similar. Let’s see an example.

$set = array('last_name' => 'Foo Foo Bar');
    $query = $fpdo->update('users')->set($set)->where('id', 2);

    // you can also use this shortcut:
    $query = $fpdo->update('users', $set, 1);
    $query->execute();

Using the set() method you can specify new values for your update operation.

With the where() method we are filtering the affected rows. There is also a shortcut, as before.

Delete

The delete operation is even simpler. Here’s a quick example.

$query = $fpdo->deleteFrom('users')->where('id', 3);

    // ... or you can use this:
    $query = $fpdo->deleteFrom('users', 3);
    $query->execute();

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.

Something like that:

$query = $fpdo->from('items')->leftJoin('user ON user.id = items.user_id')->select('user.first_name');

Ok: we are using the classic syntax in the special method leftJoin(). Not bad.

However, we can do something better. If you use conventions in your table structure, you can use this code:

$query = $fpdo->from('article')->leftJoin('user')->select('user.name');

Great, huh? Well, quick is really cool… but what about smart?

Take a look here:

$query = $fpdo->from('items')->select('users.first_name');

It gets better.

Actually, FluentPDO understands what you want to do and automatically builds the query using the data you provided in the select() method, with a table.field_name format string.

Here you can read the final built query for the last example:

SELECT items.*, users.first_name 
    FROM items 
    LEFT JOIN users ON users.id = items.user_id

That definitely looks good.

If you want, of course, you can create aliases for fields:

$query = $fpdo->from('items')->select('users.first_name AS user_first_name');

The Debugger

FluentPDO comes out with a built-in debugger system that you can use to test your queries and inspect them.

It works with a simple closure system. If you want to use debug, just place this code after your connection code.

$fpdo->debug = function($BaseQuery) {
        echo "query: " . $BaseQuery->getQuery(false) . "<br/>";
        echo "parameters: " . implode(', ', $BaseQuery->getParameters()) . "<br/>";
        echo "rowCount: " . $BaseQuery->getResult()->rowCount() . "<br/>";  
    };

You can customize the closure as you want, just remember the $BaseQuery object as a parameter.

The $BaseQuery object is an instance of the BaseQuery class.

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.

Frequently Asked Questions (FAQs) about Getting Started with FluentPDO