composer require ludal/mysql-querybuilder
This is a PHP query builder for simple MySQL queries. It allows you to write them without using strings nor heredoc, which often breaks the code's cleanliness.
This package is designed to support MySQL DBMS. However, it may work with other DBMS (PostgreSQL, SQLite...), but I cannot guarantee that, so use it at your own risk.
๐ก Made with โค๏ธ in ๐ซ๐ท
First, initialize a new instance of QueryBuilder
.
$builder = new QueryBuilder();
๐ก You can also pass a PDO instance as a parameter to execute and fetch queries directly.
$pdo = new PDO($dsn, $login, $password); $builder = new QueryBuilder($pdo);
From this instance, you can now build your query:
$select = $builder
->select()
->from('users')
->where('name = :name');
$update = $builder
->update('users')
->set(['name' => 'John'])
->where('id = 6');
Then, you can either:
- Convert your query into a SQL string :
toSQL()
- Bind parameters:
setParam('name', 'John')
,setParams(['name' => 'John'])
... - Execute the query :
execute()
,execute(['John'])
... - Fetch the results of your query :
fetch()
,fetchAll()
,fetch(PDO::FETCH_COLUMN)
... - Get the rowCount :
rowCount()
- Get the PDO statement corresponding to your query :
getStatement()
- And more: see docs for a full reference
$select->toSQL(); // returns 'SELECT * FROM users'
$select->fetchAll(); // returns the rows fetched from the db
$select->getStatement(); // get the PDO statement, useful for handling errors
$update->execute(); // executes the UPDATE query
-
SELECT
-
UPDATE
-
DELETE FROM
-
INSERT INTO
-
WHERE
-
GROUP BY
-
ORDER BY
-
LIMIT
-
OFFSET
$pdo = new PDO(...);
$qb = new QueryBuilder($pdo);
QueryBuilder::setDefaultFetchMode(PDO::FETCH_ASSOC);
// SELECT
$res = $qb
->select()
->from('users')
->where('id < 4', 'name = :name')
->orWhere('age < 12')
->orderBy('id', 'desc')
->limit(2)
->offset(1)
->fetchAll();
// INSERT
$insert = $qb
->insertInto('articles')
->values(['title' => 'Lorem ipsum', 'content' => 'Some content'])
->getStatement();
$insert->execute();
$insert->errorCode(); // or any other PDOStatement method
// UPDATE
$updated = $qb
->update('connections')
->set(['exp' => true, 'date' => date('Y-m-d')])
->where(['token' => $token])
->orderBy('date')
->limit(1)
->execute();
// DELETE
$rowCount = $qb
->deleteFrom('users')
->where('id > 5')
->orWhere('name = :name')
->orderBy('id', 'desc')
->limit(10)
->setParam(':name', 'John')
->rowCount(); // will execute, and return the rowCount
Wiki under construction. ๐ง
- Vincent Aranega for tips and tricks about the code organization