A simple and complete query builder to add practicality to your project
- Instalation
- Connection
- Select statement
- More Examples
- Expressions
- Insert statement
- Update statement
- Delete statement
composer require lucasmialichi/query-builder
// build connection ...
$qb = new QueryBuilder(
new Connection(
"127.0.0.1", // host
"root", // user
123, // password
"teste" // db name
)
);
$qb->select()->from('users')->where('id', '=', ':Id');
$qb->addParam(':Id', 1);
$result = $qb->execute(); //Instance of QueryBuilder/Connection/QueryResult
the example above converted to SQL:
SELECT * FROM users where id = :Id
Methods for query result:
- fetchAll: fetch all result as an associative array
- fetchFunction: fetch each row implementing a userfunction
- fetchAssociative: fetch using iteration (Generator)
- count: count numbers of rows before fetch
PHP:
$qb->select([
"id" => "identifier",
"name" => "userName",
"birth_date" => "birthDate"
])
->from('users', "u")
->join("address", "u.address_id = a.id", "a")
->where('id', '=', ':Id')
->orWhere("birth_date", ">", date("Y-m-d H:i:s"))
->limit(10)
->offset(1);
SQL:
SELECT id AS identifier, name AS userName, birth_date AS birthDate FROM users u INNER JOIN address a ON u.address_id = a.id WHERE ( id = :Id ) OR ( birth_date > 2023-12-18 14:30:43 ) LIMIT 10 OFFSET 1
There are two ways to instanciate expressions in this qb
staticly calling directly QueryBuilder class
$expression = QueryBuilder::Expr();
Or if you aready had initilized queryBuilder you can call it in any moment of the query
$qb->select()->from('users')->where(
$qb->expr("id")->in([1,2,3])->or()->between(1,10)
);
You can also use expression in select statement, like:
$qb->select(
$qb->expr()->caseWhen("id = 1", "0")
->when("id = 2", "1")
->else("2")
->end()
)->from('users');
PHP
$qb->insert([
"name" => "alfredo",
"birth_date" => "1988-06-11"
])->into("users");
the values are automatically binded to the query as parameters
SQL:
INSERT INTO users (name, birth_date) VALUES ('alfredo', '1988-06-11')
PHP
$qb->update([
"name" => "alfredo",
"birth_date" => "1988-06-11"
])->from("users")
->where("id", "=", ":userId")
->addParam(":userId", 2);
the values are automatically binded to the query as parameters
SQL:
UPDATE users SET name = 'alfredo', birth_date = '1988-06-11' WHERE id = :userId
$qb->delete()->from("users")->where($qb->expr("id")->notBetween(1,5))
SQL:
DELETE FROM users WHERE (id not between 1 and 5)