FluentPDO

FluentPDO is a PHP SQL query builder using PDO. It's a quick and light library featuring a smart join builder, which automatically creates table joins for you.

Features

• Easy interface for creating robust queries
• Supports any database compatible with PDO
• Ability to build complex SELECT, INSERT, UPDATE & DELETE queries with little code
• Type hinting for magic methods with code completion in smart IDEs

Requirements

Version 4 release supports PHP 8 and is actively maintained.

Reference

Sitepoint - Getting Started with FluentPDO (No longer fully compatible)

Installation

Add the following line in your composer.json file:

"require": {
	...
	"geste/fluentpdo": "^4"
}

update your dependencies with composer update, and you're done!

Getting Started

Create a new PDO instance, and pass the instance to FluentPDO:

$pdo = new PDO('mysql:dbname=fluentdb', 'root');
$fluent = new \Envms\FluentPDO\Query($pdo);

Then, creating queries is quick and easy:

$query = $fluent->from('comment')
             ->where('article.publishedAt > ?', $date)
             ->orderBy('publishedAt DESC')
             ->limit(5);

which would build the query below:

SELECT comment.*
FROM comment
LEFT JOIN article ON article.articleId = comment.articleId
WHERE article.publishedAt > ?
ORDER BY article.publishedAt DESC
LIMIT 5

To get data from the select, all we do is loop through the returned array:

foreach ($query as $row) {
    echo $row['title'] . PHP_EOL;
}

Using the Smart Join Builder

Let's start with a traditional join, below:

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

That's pretty verbose, and not very smart. If your tables use proper primary and foreign key names, you can shorten the above to:

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

That's better, but not ideal. However, it would be even easier to not write any joins:

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

Awesome, right? FluentPDO is able to build the join for you, by you prepending the foreign table name to the requested column.

All three snippets above will create the exact same query:

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

Close your connection

Finally, it's always a good idea to free resources as soon as they are done with their duties:

$fluent->close();

CRUD Query Examples

SELECT

$query = $fluent->from('article')->where('id', 1);
$query = $fluent->from('user', 1); // shorter version if selecting one row by primary key

INSERT

$values = ['title' => 'article 1', 'content' => 'content 1'];

$query = $fluent->insertInto('article')->values($values)->execute();
$query = $fluent->insertInto('article', $values)->execute(); // shorter version

UPDATE

$set = ['published_at' => new Literal('NOW()')];

$query = $fluent->update('article')->set($set)->where('id', 1)->execute();
$query = $fluent->update('article', $set, 1)->execute(); // shorter version if updating one row by primary key

DELETE

$query = $fluent->deleteFrom('article')->where('id', 1)->execute();
$query = $fluent->deleteFrom('article', 1)->execute(); // shorter version if deleting one row by primary key

Note: INSERT, UPDATE and DELETE queries will only run after you call ->execute()

Full documentation can be found on the FluentPDO homepage but is partly no longer compatible anymore!

License

Free for commercial and non-commercial use under the Apache 2.0 or GPL 2.0 licenses.