This library provides a fluent interface for generating SQL statement strings.
The DBAL library extends this library with a wrapper for PDO.
composer require wtframework/sql
- MariaDB
- MySQL
- PostgreSQL
- SQLite
- TSQL
- DELETE
- INSERT
- REPLACE
- SELECT
- TRUNCATE
- UPDATE
- ALTER TABLE
- CREATE TABLE
- CREATE INDEX
- DROP TABLE
- DROP INDEX
use WTFramework\SQL\SQL;
echo $stmt = SQL::select()
->from('users')
->where('email_address', 'admin@example.net');
SELECT * FROM users WHERE email_address = ?
After casting the object to a string, like above, the bindings
method will return an array of bound parameters.
print_r($stmt->bindings());
Array
(
[0] => admin@example.net
)
The default global grammar is MySQL
. Use the static SQL::use
method to change this. This will not apply to any existing statements.
use WTFramework\SQL\Grammar;
$stmt1 = SQL::select();
SQL::use(Grammar::TSQL);
$stmt2 = SQL::select();
// $stmt1 will use MySQL, $stmt2 will use TSQL.
The use
method can also be used to override the grammar of an individual statement.
$stmt1->use(Grammar::PostgreSQL);
A simple implementation of the library is also provided, using a subset of the statements, clauses, and services.
use WTFramework\SQL\Simple\SQL;
$stmt = SQL::delete();
$stmt = SQL::insert();
$stmt = SQL::replace();
$stmt = SQL::select();
$stmt = SQL::truncate();
$stmt = SQL::update();
Supported clauses:
$stmt->ignore();
$stmt->top($row_count);
$stmt->table($table);
$stmt->from($table);
$stmt->join($table);
$stmt->using($table);
$stmt->where($column, $value);
$stmt->orderBy($column);
$stmt->limit($row_count);
$stmt->offset($offset);
Supported clauses:
$stmt->ignore();
$stmt->top($row_count);
$stmt->into($table);
$stmt->column($column);
$stmt->values($values);
$stmt->set($column, $value);
$stmt->select($stmt);
$stmt->onDuplicateKeyUpdate($column, $value);
$stmt->onConflict($upsert);
Supported clauses:
$stmt->into($table);
$stmt->column($column);
$stmt->values($values);
$stmt->set($column, $value);
$stmt->select($stmt);
Supported clauses:
$stmt->distinct();
$stmt->top($row_count);
$stmt->column($column);
$stmt->from($table);
$stmt->join($table);
$stmt->where($column, $value);
$stmt->groupBy($column);
$stmt->having($column, $value);
$stmt->union($stmt);
$stmt->orderBy($column);
$stmt->limit($row_count);
$stmt->offset($offset);
$stmt->offsetRows($offset);
$stmt->fetchRows($row_count);
Supported clauses:
$stmt->table($table);
Supported clauses:
$stmt->ignore();
$stmt->top($row_count);
$stmt->table($table);
$stmt->from($table);
$stmt->join($table);
$stmt->set($column, $value);
$stmt->where($column, $value);
$stmt->orderBy($column);
$stmt->limit($row_count);
$stmt->offset($offset);
use WTFramework\SQL\Simple\SQL;
SQL::bind($value);
SQL::raw($string);
SQL::subquery($stmt);
SQL::table($name);
SQL::upsert();
To extend the library you can use the static macro
method, passing the new method name and a closure to call. This works for both static and non-static methods. This is available on the SQL
class as well as all statement and service classes.
use WTFramework\SQL\SQL;
SQL::macro('count', function (string $table)
{
return static::select()
->column('COUNT(*) AS counter')
->from($table);
});
SQL::count('users');