Dec 14, 2014

Ketan Patel

Introduction to FluentPDO


In any project the most time consuming and tedious task is to write queries. Its very boring thing to do especially when you have to deal with multi tables for the joins because they makes the query very complex and lengthy.Such complex joins query requires your attention also. So in this tutorial we are going to learn very cool thing : FluentPDO

As per the official site of FluentPDO,

FluentPDO is small PHP library for rapid query building. Killer feature is "Smart join builder" which generates joins automatically. FluentPDO is a perfect choice for small and large projects.


  1. Zero configuration
  2. Fluent interface for creating queries step by step
  3. Smart join builder for quick prototyping queries
  4. Build SELECT, INSERT, UPDATE & DELETE queries
  5. Simple API based on PDO and SQL syntax
  6. Code completion for smart IDEs
  7. Requires PHP 5.1+ with any database supported by PDO
  8. Free for commercial and non-commercial use Apache License or GPL.sss


Here we have two ways to install the FluentPDO.One is the prefered way using composer and the second is manual installation.

Preferred way

The preferred way of installing FluentPDO is by using composer. After the successful installation of composer, add the below lines in your composer.json:

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

then update your dependencies with composer update.

Manual Installation

In manual installation, you just have to copy /FluentPDO directory into your libs/ directory. If your project hasn't libs directory then simply create it.

Now include the FluentPDO.php file by using the following line in your script.

include "libs/FluentPDO/FluentPDO.php"; 

Usage Example

include "libs/FluentPDO/FluentPDO.php";

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

$query = $fpdo->from('users')->where('id > 0'); // simple select query
$query->fetch();    // to fetch only one row
$query->fetchAll(); // to fetch all rows

Query executed is:
SELECT users.* FROM users WHERE id > 0

You can echo the query being executed by using ,
 echo $query->getQuery();

CRUD Operations Examples

Below are the examples of how you can use FluentPDO for the basic CRUD operations.


$query = $fpdo->from('users')->where('id', 1);
// or shortly if you select one row by primary key
$query = $fpdo->from('users', 1);


$values = array('firstname' => 'james', 'lastname' => 'rown');
$query = $fpdo->insertInto('users')->values($values);
// or shortly 
$query = $fpdo->insertInto('users', $values);


$set = array('firstname' => 'My first name');
$query = $fpdo->update('users')->set($set)->where('id', 3);

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


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

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

Useful Select Operations

 Simple find with primary key and where

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

foreach($query as $row){
    echo 'Hey, ' . $row['firstname'] . ' ' . $row['lastname'] . '!!';    

Selecting specific fields only

$query = $fpdo->from('users')->select(array('firstname', 'lastname'))->where('id', $userid);

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('book')->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 choose.
$query = $fpdo->from('book')->orderBy('price DESC');


The having() method has a very simple syntax. In the next example we are filtering every item with a price lower than $500.
$query = $fpdo->from('book')->having('price < 500');

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('users')->select('firstname, COUNT(*) AS cn')->groupBy('firstname');

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 twenty results...
     $query = $fpdo->from('users')->where('id', $userid)->limit(20)->offset(0);



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');
      $data   = $query->fetch();

     var_dump($data); // will display output

array (size=3)
  'id' => string '1' (length=1)
  'firstname' => string 'james' (length=5)
  'lastname' => string 'rown' (length=4)

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

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

var_dump($firstName); // will output:
string 'james' (length=5)


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

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

     $query = $fpdo->from('users');
     $data  = $query->fetchAll('id', 'firstname, lastname');

       var_dump($data); // will output:

  array (size=2)
  1 => 
    array (size=3)
      'id' => string '1' (length=1)
      'firstname' => string 'james' (length=5)
      'lastname' => string 'rown' (length=4)
  2 => 
    array (size=3)
      'id' => string '2' (length=1)
      'firstname' => string 'kevin' (length=5)
      'lastname' => string 'wills' (length=5)

Best Usage with Joins

For example: we have the below classic join query 
SELECT article.*, 
  FROM article 
       LEFT JOIN user ON = article.user_id

can be written as 

$query = $fpdo->from('article')
              ->leftJoin('user ON = article.user_id')

ohh just using leftJoin() method it has became very easy to write and understand.

We can make even better than the previous one. By using database uses convention for primary and foreign key names, you can write only:


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

and the best option is,
$query = $fpdo->from('article')->select('');

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.

Clauses like select(), where(), groupBy() or orderBy() can be written as


$query = $fpdo->from('comments')
                ->where('user.country_id', $country)

So from the above examples you can see that how its easy to write joins query with FluentPDO.

This listing will become too long if all the examples will be written. So i would suggest you to download it and explore them. All the features of the FluentPDO are well explained using the examples here


FluentPDO is a small and simple project. Its suitable for some projects only and absolutely not for every project.

There is a room for improvement, even though it could be a good choice for a little or medium applications, just in case you want to play alone without any big frameworks ;)

Download FluentPDO

Ketan Patel

About Ketan Patel -

I have developed a wide range of websites using CorePHP, Opencart, CakePHP and CodeIgniter including sites for startup companies and small businesses. Apart from my blogging life, I like to read Novels, Listening music and Net surfing.

Subscribe to this Blog via Email :