FluentPDO - smart SQL builder for PHP.

FluentPDO is small PHP library for rapid query building. Killer feature is "Smart join builder" which generates joins automatically.

• Fluent interface for creating queries step by step
• Smart join builder
• Simple API based on PDO and SQL syntax
• Build SELECT, INSERT, UPDATE & DELETE queries
• Small and fast
• Type hinting with code completion in smart IDEs
• Requires PHP 5.3+ with any database supported by PDO

Sitepoint - Getting Started with FluentPDO

The preferred way to install FluentPDO is via composer. v1.1.x will be the last until the release of 2.0, so we recommend using 1.1.* to ensure no breaking changes are introduced.

Add in your composer.json:

"require": {
	...
	"fpdo/fluentpdo": "1.1.*"
}

then update your dependencies with composer update.

If you are not familiar with composer just copy /FluentPDO directory into your libs/ directory then:

include "libs/FluentPDO/FluentPDO.php";

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

FluentPDO is easy to use:

$query = $fpdo->from('article')
            ->where('published_at > ?', $date)
            ->orderBy('published_at DESC')
            ->limit(5);
foreach ($query as $row) {
    echo "$row[title]\n";
}

executed query is:

SELECT article.*
FROM article
WHERE published_at > ?
ORDER BY published_at DESC
LIMIT 5

If you want to join table you can use full sql join syntax. For example we would like to show list of articles with author name:

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

It was not so much smart, was it? ;-) If your database uses convention for primary and foreign key names, you can write only:

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

Smarter? May be. but best practice how to write joins is not to write any joins ;-)

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

All three commands create same query:

SELECT article.*, user.name 
FROM article 
LEFT JOIN user ON user.id = article.user_id

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

$values = array('title' => 'article 1', 'content' => 'content 1');
$query = $fpdo->insertInto('article')->values($values)->execute();
// or shortly
$query = $fpdo->insertInto('article', $values)->execute();

$set = array('published_at' => new FluentLiteral('NOW()'));
$query = $fpdo->update('article')->set($set)->where('id', 1)->execute();
// or shortly if you update one row by primary key
$query = $fpdo->update('article', $set, 1)->execute();

$query = $fpdo->deleteFrom('article')->where('id', 1)->execute();
// or shortly if you delete one row by primary key
$query = $fpdo->deleteFrom('article', 1)->execute();

Note: INSERT, UPDATE and DELETE will be executed after ->execute():

Full documentation can be found on the FluentPDO homepage

Free for commercial and non-commercial use (Apache License or GPL).