🔧 MySQL QueryBuilder

composer require ludal/mysql-querybuilder

ℹ️ Presentation

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 🇫🇷

📘 Usage

Getting Started

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

Supported Statements

• SELECT
• UPDATE
• DELETE FROM
• INSERT INTO

Supported Clauses

• WHERE
• GROUP BY
• ORDER BY
• LIMIT
• OFFSET

Code Samples

$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

📖 Docs

Wiki under construction. 🚧

🙏 Acknowledgements

• Vincent Aranega for tips and tricks about the
  code organization