PHPackages                             stvkoch/simple-query - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. stvkoch/simple-query

ActiveLibrary[Database & ORM](/categories/database)

stvkoch/simple-query
====================

Simple but powerfull query builder. Secury support subquery conditions. See test folder to discovery how it's easy.

271PHP

Since Oct 29Pushed 8y ago1 watchersCompare

[ Source](https://github.com/stvkoch/simple-query)[ Packagist](https://packagist.org/packages/stvkoch/simple-query)[ RSS](/packages/stvkoch-simple-query/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependenciesVersions (2)Used By (0)

Simple SQL Builder
==================

[](#simple-sql-builder)

Master - stable version: [![Travis-ci](https://camo.githubusercontent.com/458c6831c5059f1b6e9fcaf3dd9219864578cb8fb7931e0e2e111ba7abef216d/68747470733a2f2f7472617669732d63692e6f72672f7374766b6f63682f73696d706c652d71756572792e7376673f6272616e63683d6d6173746572)](https://camo.githubusercontent.com/458c6831c5059f1b6e9fcaf3dd9219864578cb8fb7931e0e2e111ba7abef216d/68747470733a2f2f7472617669732d63692e6f72672f7374766b6f63682f73696d706c652d71756572792e7376673f6272616e63683d6d6173746572)

Develop - unstable version [![Travis-ci](https://camo.githubusercontent.com/b140c0ad982d4d95aaa33ef521935e318c09eb7cd3f81771bfdd933d66c6bc94/68747470733a2f2f7472617669732d63692e6f72672f7374766b6f63682f73696d706c652d71756572792e737667)](https://camo.githubusercontent.com/b140c0ad982d4d95aaa33ef521935e318c09eb7cd3f81771bfdd933d66c6bc94/68747470733a2f2f7472617669732d63692e6f72672f7374766b6f63682f73696d706c652d71756572792e737667)

Road map:

```
- calculate query hashCode independ of insertion order. For this change array to splheap

```

Index of README

- Simple SELECT query
- Model
- Query Methos
- Examples
- Improvements

See on examples:
----------------

[](#see-on-examples)

### Simple SELECT query

[](#simple-select-query)

```
$model = \Simple\Model(['table'=>'superA', 'alias'=>'A']);

$query = (new \Simple\Query($model))
    ->where($model->field('username'), 'superUser');

echo $query->sqlSelect();
// SELECT A.* FROM superA AS A WHERE A.username = (?)

// use 'bindParameters' propriety to bind parameters in your database statement
$query->bindParameters
// ['superUser']

// more real example
$supossed_mysql_stmt = $con->prepare($query->sqlSelect());
// you can bind_param this way
foreach ($query->bindParameters as $value) {
    $supossed_mysql_stmt->bind_param(
        $query->type($value), // return 'i', 'd' or 's'
        $value
    );
}
...

```

Why we not build onw string sql?
--------------------------------

[](#why-we-not-build-onw-string-sql)

Using SQL Builder you can:

```
- increase security, avoid sql injection.
- create powerful query without specific order.
- use bind values.
- create easy subquery conditions using query object.
- apply patterns to interprete request and build personal queries.
- not dependent of ORMs.

```

About Model
-----------

[](#about-model)

Model allow to separate table structures to avoid conflicts when you want to create more complex queries.

### Constructor

[](#constructor)

```
$model = new \Simple\Model([
    'table' => 'nameOfTable',
    'alias' => 'shortName',
    'pk' => 'id' ## default value
]);

```

### pk()

[](#pk)

Primary key of model(table)

```
#example
$model = new \Simple\Model([
    'table'=>'superA',
    'alias'=>'A'
]);

echo $model->pk();
# A.id

```

### fk(\\Simple\\Model $modelB)

[](#fksimplemodel-modelb)

How key from model $modelB are represented inside of Model like a foreing key. Useful in joins

```
#example
$modelA = new \Simple\Model([
    'table'=>'superA',
    'alias'=>'A'
]);

$modelB = new \Simple\Model([
    'table'=>'megaB',
    'alias'=>'B'
]);
echo $modelA->fk($modelB);
# A.idMegaB

```

### field($fieldName)

[](#fieldfieldname)

Useful method return anti-collision field name

```
$modelA = new \Simple\Model([
    'table'=>'superA',
    'alias'=>'A'
]);
echo $modelA->field('name');
# A.name

```

### table()

[](#table)

Return table name

### alias()

[](#alias)

return alias table

Now We Can Build Queries!!
--------------------------

[](#now-we-can-build-queries)

```
namespace My\Model;
class User extends \Simple\Model {
    protected $table = 'user';
    protected $alias = 'us';
}

$userModel = new \My\Model\User();
$query = new \Simple\Query($userModel);
$query->where($userModel->field('username'), $usernameParameter);

$stmt = $mysql_con->prepare($query->sqlSelect());
....

```

### Multiples conditions

[](#multiples-conditions)

```
# example A
$userModel = new \Simple\Model(['table'=>'users']);
$query = new \Simple\Query($userModel);
$query->where($userModel->field('username'), $value);
$query->where($userModel->field('password'), $hashPass);

# example B
$userModel = new \Simple\Model(['table'=>'users']);
$query = new \Simple\Query($userModel);
$query->where($userModel->field('name'), 'John%', 'LIKE');
# OR condition
$query->where($userModel->field('surename'), 'John%', 'LIKE', 'OR');
...

# query equal method don't use bind parameter. equal method inject value inside of query. and work in the same way of where
$query->equal($model->field('status'), 1);

# IS NULL
$query->where($model->field('age'), 'NULL', 'IS');

# IS NOT NULL
$query->where($model->field('age'), 'NULL', 'IS NOT');

```

### Subquery condition

[](#subquery-condition)

```
$modelUser = new \Simple\Model(['table'=>'user']);
$modelOrder = new \Simple\Model(['table'=>'order']);

// get all user are active customer in holiday month

$queryOrder = new \Simple\Query($modelOrder);
$queryOrder->field($modelOrder->fk($modelUser))
    ->where('createAt BETWEEN (?) AND (?)', ['2015-12-01','2015-12-31'], 'RAW')
    ->group($modelOrder->fk($modelUser));

$query = new \Simple\Query($modelUser);
$query->where($modelUser->field($modelUser->pk()), $queryOrder, 'IN')
    ->order($modelUser->field('name'))
    ->page(1, 20);

+'SELECT user.* FROM user WHERE user.id IN (SELECT order.idUser FROM order WHERE createAt BETWEEN (?) AND (?) GROUP BY order.idUser) ORDER BY user.name ASC LIMIT 0, 20'

```

### Where

[](#where)

Add condition. This apply to 'having' method

```
$query
    ->where($fieldName, $valueToBind, $function = '=', $operator = 'AND')
    ->where(...);

```

Support conditions functions:

```
'=','>','