README

PHP SQL query builder using PDO. It's a quick and light library.

Features

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

Installation

Composer

The preferred way to install PHP Query Builder is via composer.

Run this command for installing the PHP Query Builder:

composer require kamandlou/php-query-builder

Make a connection

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$db = DB::connection($pdo);

Set fetch mode

$db = DB::connection($pdo)->setFetchMode(PDO::FETCH_ASSOC);

DB Class Methods

setPrefix(string $prefix)

Set a prefix to be added to all table names.

$db->setPrefix('db');

setSeparator(string $separator)

Set the separator character used when building queries.

$db->setPrefix('db')->setSeparator('_'); // Result: db_tableName

CRUD Query Examples

SELECT

$db->table('users')
    ->select()
    ->where('id', '>', 100)
    ->orderByDesc('id')
    ->get();

$db->table('users')
    ->select('id', 'username')
    ->where('id', '>', 100)
    ->orderByDesc('id')
    ->get();

You can get the pure SQL query with toSql method.

$db->table('users')
    ->select('id', 'username')
    ->where('id', '>', 100)
    ->orderByRand()
    ->toSql();

Select Class Methods

The Select class provides methods for building SQL SELECT queries.

select(...$columns)

Specify columns to select.

$db->select('id', 'name')
// OR
$db->select() // If you don't specify any columns it selects all columns

where($column, $operator, $value)

Add a WHERE condition to the query.

$db->select()->where('age', '>', 18);

• $column - The column name to compare against.
• $operator - Comparison operator (>, <, =, <>, etc).
• $value - The value to compare the column against.

orWhere($column, $operator, $value)

Add an OR WHERE condition to the query.

$db->select()->orWhere('age', '>', 18);

• $column - The column name to compare against.
• $operator - Comparison operator (>, <, =, <>, etc).
• $value - The value to compare the column against.

toSql()

Get the generated SQL query string.

$sql = $db->select()->orWhere('age', '>', 18)->toSql();
echo $sql;

first()

Execute the query and return the first result row.

$user = $db->table('users')->select('id', 'name')->where('id', '=', 5)->first();

get()

Execute the query and return all result rows.

$users = $db->table('users')->select('id', 'name')->where('id', '>', 100)->get();

orderBy(string $column, string $direction = 'ASC')

Set the ORDER BY clause for the query.

$users = $db->table('users')->select('id', 'name', 'age')->orderBy('age', 'DESC')->get();

• $column - Name of column to sort by.
• $direction - Sort direction, 'ASC' or 'DESC'.

orderByDesc(string $column)

Set the ORDER BY clause to sort by a column in descending order.

$users = $db->table('users')->select('id', 'name', 'age')->orderByDesc('age')->get();

• $column - Name of column to sort by.

orderByRand()

Set the ORDER BY to random order.

$users = $db->table('users')->select()->orderByRand()->get();

count()

Get the count of the records.

$count = $db->table('users')->select()
    ->where('id','>',3)
    ->where('id', '<', 100000)
    ->count();
// OR
$count = $db->table('users')->select()->count();