midorikocak/querymaker

QueryMaker is a little library that helps you create simple PDO queries

v1.4.7 2020-03-15 04:01 UTC

Requires

• php: ~7.4

Requires (Dev)

• opsway/psr12-strict-coding-standard: ^0.3.0
• phpunit/php-token-stream: ^3.1
• phpunit/phpunit: >=8.0
• squizlabs/php_codesniffer: ^3.0

MIT

• Midori Kocak

• midorikocak
• querymaker

README

This small library, allows you to create simple SQL queries to use with PDO easily. Just using methods with simple db command names, you can create seamless statements and key value array to use in execution.

Motivation

When using PDO, writing queries are prone to syntax and parameter errors. To prevent them in simple queries you can use this library.

Requirements

Strictly requires PHP 7.4.

Install

Via Composer

$ composer require midorikocak/querymaker

Usage

There are starter methods to create a query, such as SELECT and UPDATE.

Select

$queryMaker = new QueryMaker();
$queryMaker->select('users');
echo $queryMaker->getQuery();

The above example will output:

SELECT * FROM users

Select with fields

Fields to select can be specified as well:

$queryMaker = new QueryMaker();
$queryMaker->select('users', ['id', 'email']);
echo $queryMaker->getQuery();

The above example will output:

SELECT id, email FROM users

Fields with different operators

Field values can include operators, such as: =,>, <,<=,>=

$queryMaker = new QueryMaker();
$queryMaker->select('users', ['id', 'email'])->where('id', '3', '>=');
echo $queryMaker->getQuery();
echo $queryMaker->getStatement();

The above example will output:

SELECT id, email FROM users WHERE id>='3'
SELECT id, email FROM users WHERE id>=:id' 

Delete

$queryMaker = new QueryMaker();
$queryMaker->delete('users');
echo $queryMaker->getQuery();

The above example will output:

DELETE FROM users

Where

To specify WHERE clauase use where($key, $value) method.

$queryMaker = new QueryMaker();
$queryMaker->select('users', ['id', 'email'])->where('id', 3);
echo $queryMaker->getQuery();
echo $queryMaker->getStatement();

The above example will output:

SELECT id, email FROM users WHERE id='3'
SELECT id, email FROM users WHERE id=:id

AND and OR

Contraints such as AND and OR, are methods as well. and($key, $value) and or($key, $value)

$queryMaker = new QueryMaker();
$queryMaker->select('users', ['id', 'email'])->where('id', 3)->and('email', '[e-mail]')->or('username', 'midori');
echo $queryMaker->getQuery();
echo $queryMaker->getStatement();

The above example will output:

SELECT id, email FROM users WHERE id='3' OR username='midori'
SELECT id, email FROM users WHERE id=:id OR username=:username

Multiple AND and OR clauses can have same field conditions.

$queryMaker = new QueryMaker();
$queryMaker->select('users', ['id', 'email'])->where('email', '[e-mail]')->and('id', '>3')->and('id', '<5');

ORDER BY

To specify ORDER BY clauase use order($key, $order) method.

$queryMaker = new QueryMaker();
$queryMaker->select('users')->orderBy('id');
echo $queryMaker->getQuery();

The above example will output:

SELECT * FROM users ORDER BY id ASC

OFFSET and LIMIT

To specify OFFSET and LIMIT clauase use offset($offset) and limit($offset) methods.

$queryMaker = new QueryMaker();
$queryMaker->select('users')->orderBy('id')->limit(3)->offset(2);
echo $queryMaker->getQuery();

The above example will output:

SELECT * FROM users ORDER BY id ASC LIMIT 3 OFFSET 2

Get key value array to execute

It's also possible to get values as key value pair to easily execute.

$db = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

$queryMaker = new QueryMaker();

$queryMaker->select('users', ['id', 'email'])->where('id', 3)->and('email', '[e-mail]')->or('username', 'midori');

$statement = $db->prepare($query->getStatement());

$statement->execute($query->getParams());

Insert

To specify INSERT operation, insert() method, expects a key value array.

$queryMaker = new QueryMaker();
$queryMaker->insert('users', ['email' => '[e-mail]', 'username' => 'midorikocak']);
echo $queryMaker->getQuery();
echo $queryMaker->getStatement();

The above example will output:

INSERT INTO users (email, username) VALUES ('[e-mail]', 'midorikocak')
INSERT INTO users (email, username) VALUES (:email, :username)

Update

To specify UPDATE operation, handy update() method, expects a key value array. All statement params are generated thoroughly.

$queryMaker = new QueryMaker();
$queryMaker->update('users', ['email' => '[e-mail]', 'username' => 'midorikocak'])->where('id', 3);
echo $queryMaker->getQuery();
echo $queryMaker->getStatement();

The above example will output:

UPDATE users SET email='[e-mail]', username='midorikocak' WHERE id='3'
UPDATE users SET email=:email, username=:username WHERE id=:id

Warning

This library is for educational purposes. Use at your own risk. Exposing query values and using it would create security issues.

Change log

Please see CHANGELOG for more information on what has changed recently.

Testing

$ composer test

Contributing

Please see CONTRIBUTING and CODE_OF_CONDUCT for details.

Security

If you discover any security related issues, please email [e-mail] instead of using the issue tracker.

Credits

• Midori Kocak
• All Contributors

License

The MIT License (MIT). Please see License File for more information.