# Query Builder

**PDOx Documentación y ejemplos**

El siguiente material te ofrece una explicación breve sobre los métodos que incluye Pdox, todos los comentarios y métodos están en inglés.

* `select`
* `where`
* `groupBy`
* `insert`
* `check`
* `insertId`
* `getQuery`
* `select functions (min, max, sum, avg, count)`
* `grouped`
* `having`
* `update`
* `checksum`
* `numRows`
* `table`
* `in`
* `orderBy`
* `delete`
* `optimize`
* `error`
* `get AND getAll`
* `between`
* `limit - offset`
* `transaction`
* `repair`
* `cache`
* `join`
* `like`
* `pagination`
* `analyze`
* `query`
* `queryCount`

**Methods**

**select**

```php
# Usage 1: string parameter

$db->select('title, content')->table('test')->getAll();
# Output: "SELECT title, content FROM test"

$db->select('title AS t, content AS c')->table('test')->getAll();
# Output: "SELECT title AS t, content AS c FROM test"

# Usage2: array parameter

$db->select(['title', 'content'])->table('test')->getAll();
# Output: "SELECT title, content FROM test"

$db->select(['title AS t', 'content AS c'])->table('test')->getAll();
# Output: "SELECT title AS t, content AS c FROM test"    
```

**select functions (min, max, sum, avg, count)**

```php
# Usage 1:

$db->table('test')->max('price')->get();
# Output: "SELECT MAX(price) FROM test"

# Usage 2:

$db->table('test')->count('id', 'total_row')->get();
# Output: "SELECT COUNT(id) AS total_row FROM test"    
```

**table**

```php
### Usage 1: string parameter

$db->table('table');
# Output: "SELECT * FROM table"

$db->table('table1, table2');
# Output: "SELECT * FROM table1, table2"

$db->table('table1 AS t1, table2 AS t2');
# Output: "SELECT * FROM table1 AS t1, table2 AS t2"

### Usage 2: array parameter

$db->table(['table1', 'table2']);
# Output: "SELECT * FROM table1, table2"

$db->table(['table1 AS t1', 'table2 AS t2']);
# Output: "SELECT * FROM table1 AS t1, table2 AS t2"
```

**get AND getAll**

```php
# get(): return 1 record.
# getAll(): return multiple records.

$db->table('test')->getAll();
# Output: "SELECT * FROM test"

$db->select('username')->table('users')->where('status', 1)->getAll();
# Output: "SELECT username FROM users WHERE status='1'"

$db->select('title')->table('pages')->where('id', 17)->get();
# Output: "SELECT title FROM pages WHERE id='17' LIMIT 1"
```

**join**

```php
$db->table('test as t')->join('foo as f', 't.id', 'f.t_id')->where('t.status', 1)->getAll();
# Output: "SELECT * FROM test as t JOIN foo as f ON t.id=f.t_id WHERE t.status='1'"    
```

You can use this method in 7 ways. These;

* join
* left\_join
* right\_join
* inner\_join
* full\_outer\_join
* left\_outer\_join
* right\_outer\_join

**Examples:**

```php
$db->table('test as t')->leftJoin('foo as f', 't.id', 'f.t_id')->getAll();
# Output: "SELECT * FROM test as t LEFT JOIN foo as f ON t.id=f.t_id"

$db->table('test as t')->fullOuterJoin('foo as f', 't.id', 'f.t_id')->getAll();
# Output: "SELECT * FROM test as t FULL OUTER JOIN foo as f ON t.id=f.t_id"    
```

**where**

```php
$where = [
         'name' => 'Burak',
         'age' => 23,
         'status' => 1
];

$db->table('test')->where($where)->get();
# Output: "SELECT * FROM test WHERE name='Burak' AND age='23' AND status='1' LIMIT 1"

# OR

$db->table('test')->where('active', 1)->getAll();
# Output: "SELECT * FROM test WHERE active='1'"

# OR

$db->table('test')->where('age', '>=', 18)->getAll();
# Output: "SELECT * FROM test WHERE age>='18'"

# OR

$db->table('test')->where('age = ? OR age = ?', [18, 20])->getAll();
# Output: "SELECT * FROM test WHERE age='18' OR age='20'"    
```

You can use this method in 4 ways. These;

* where
* orWhere
* notWhere
* orNotWhere
* whereNull
* whereNotNull

**Example:**

```php
$db->table('test')->where('active', 1)->notWhere('auth', 1)->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND NOT auth = '1'"

# OR

$db->table('test')->where('age', 20)->orWhere('age', '>', 25)->getAll();
# Output: "SELECT * FROM test WHERE age = '20' OR age > '25'"

$db->table('test')->whereNotNull('email')->getAll();
# Output: "SELECT * FROM test WHERE email IS NOT NULL"
```

**grouped**

```php
$db->table('users')
         ->grouped(function($q) {
                 $q->where('country', 'TURKEY')->orWhere('country', 'ENGLAND');
         })
         ->where('status', 1)
         ->getAll();
# Ouput: "SELECT * FROM users WHERE (country='TURKEY' OR country='ENGLAND') AND status ='1'"
```

**in**

```php
$db->table('test')->where('active', 1)->in('id', [1, 2, 3])->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND id IN ('1', '2', '3')"    
```

You can use this method in 4 ways. These;

* in
* orIn
* notIn
* orNotIn

**Example:**

```php
$db->table('test')->where('active', 1)->notIn('id', [1, 2, 3])->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"

# OR

$db->table('test')->where('active', 1)->orIn('id', [1, 2, 3])->getAll();
# Output: "SELECT * FROM test WHERE active = '1' OR id IN ('1', '2', '3')"    
```

**between**

```php
$db->table('test')->where('active', 1)->between('age', 18, 25)->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND age BETWEEN '18' AND '25'"    
```

You can use this method in 4 ways. These;

* between
* orBetween
* notBetween
* orNotBetween

**Example:**

```php
$db->table('test')->where('active', 1)->notBetween('age', 18, 25)->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND age NOT BETWEEN '18' AND '25'"

# OR

$db->table('test')->where('active', 1)->orBetween('age', 18, 25)->getAll();
# Output: "SELECT * FROM test WHERE active = '1' OR age BETWEEN '18' AND '25'"
```

**like**

```php
$db->table('test')->like('title', "%php%")->getAll();
# Output: "SELECT * FROM test WHERE title LIKE '%php%'"    
```

You can use this method in 4 ways. These;

* like
* orLike
* notLike
* orNotLike

**Example:**

```php
$db->table('test')->where('active', 1)->notLike('tags', '%dot-net%')->getAll();
# Output: "SELECT * FROM test WHERE active = '1' AND tags NOT LIKE '%dot-net%'"

# OR

$db->table('test')->like('bio', '%php%')->orLike('bio', '%java%')->getAll();
# Output: "SELECT * FROM test WHERE bio LIKE '%php%' OR bio LIKE '%java%'"    
```

**groupBy**

```php
# Usage 1: One parameter

$db->table('test')->where('status', 1)->groupBy('cat_id')->getAll();
# Output: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id"

# Usage 1: Array parameter

$db->table('test')->where('status', 1)->groupBy(['cat_id', 'user_id'])->getAll();
# Output: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id, user_id"    
```

**having**

```php
$db->table('test')->where('status', 1)->groupBy('city')->having('COUNT(person)', 100)->getAll();
# Output: "SELECT * FROM test WHERE status='1' GROUP BY city HAVING COUNT(person) > '100'"

# OR

$db->table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary)', '<=', 500)->getAll();
# Output: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) <= '500'"

# OR

$db->table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary) > ? AND MAX(salary) < ?', [250, 1000])->getAll();
# Output: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"
```

**orderBy**

```php
# Usage 1: One parameter

$db->table('test')->where('status', 1)->orderBy('id')->getAll();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"

### OR

$db->table('test')->where('status', 1)->orderBy('id desc')->getAll();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id desc"

# Usage 1: Two parameters

$db->table('test')->where('status', 1)->orderBy('id', 'desc')->getAll();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id DESC"

# Usage 3: Rand()

$db->table('test')->where('status', 1)->orderBy('rand()')->limit(10)->getAll();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY rand() LIMIT 10"
```

**limit - offset**

```php
# Usage 1: One parameter

$db->table('test')->limit(10)->getAll();
# Output: "SELECT * FROM test LIMIT 10"

# Usage 2: Two parameters

$db->table('test')->limit(10, 20)->getAll();
# Output: "SELECT * FROM test LIMIT 10, 20"

# Usage 3: with offset method

$db->table('test')->limit(10)->offset(10)->getAll();
# Output: "SELECT * FROM test LIMIT 10 OFFSET 10"
```

**pagination**

```php
# First parameter: Data count of per page
# Second parameter: Active page

$db->table('test')->pagination(15, 1)->getAll();
# Output: "SELECT * FROM test LIMIT 15 OFFSET 0"

$db->table('test')->pagination(15, 2)->getAll();
# Output: "SELECT * FROM test LIMIT 15 OFFSET 15"  
```

**insert**

```php
$data = [
         'title' => 'test',
         'content' => 'Lorem ipsum dolor sit amet...',
         'time' => '2017-05-19 19:05:00',
         'status' => 1
];

$db->table('pages')->insert($data);
# Output: "INSERT INTO test (title, content, time, status) VALUES ('test', 'Lorem ipsum dolor sit amet...', '2017-05-19 19:05:00', '1')"    
```

**update**

```php
$data = [
         'username' => 'izniburak',
         'password' => 'pass',
         'activation' => 1,
         'status' => 1
];

$db->table('users')->where('id', 10)->update($data);
# Output: "UPDATE users SET username='izniburak', password='pass', activation='1', status='1' WHERE id='10'"    
```

**delete**

```php
$db->table('test')->where("id", 17)->delete();
# Output: "DELETE FROM test WHERE id = '17'"

# OR

$db->table('test')->delete();
# Output: "TRUNCATE TABLE delete"    
```

**transaction**

```php
$db->transaction();

$data = [
         'title' => 'new title',
         'status' => 2
];

$db->table('test')->where('id', 10)->update($data);
$db->commit();

# OR

$db->rollBack();
```

**analyze**

```php
$db->table('users')->analyze();
# Output: "ANALYZE TABLE users"
```

**check**

```php
$db->table(['users', 'pages'])->check();
# Output: "CHECK TABLE users, pages"    
```

**checksum**

```php
$db->table(['users', 'pages'])->checksum();
# Output: "CHECKSUM TABLE users, pages"
```

**optimize**

```php
$db->table(['users', 'pages'])->optimize();
# Output: "OPTIMIZE TABLE users, pages"    
```

**repair**

```php
$db->table(['users', 'pages'])->repair();
# Output: "REPAIR TABLE users, pages"
```

**query**

```php
# Usage 1: Select all records

$db->query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetchAll();

# Usage 2: Select one record

$db->query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetch();

# Usage 3: Other queries like Update, Insert, Delete etc...

$db->query('DELETE FROM test WHERE id=?', [10])->exec();  
```

**insertId**

```php
$data = [
         'title' => 'test',
         'content' => 'Lorem ipsum dolor sit amet...',
         'time' => time(),
         'status' => 1
];

$db->table('pages')->insert($data);

var_dump($db->insertId());
```

**numRows**

```php
$db->select('id, title')->table('test')->where('status', 1)->orWhere('status', 2)->getAll();

var_dump($db->numRows());    
```

**error**

```php
$db->error();    
```

**cache**

```php
# Usage: ...->cache($time)->...

$db->table('pages')->where('slug', 'example-page')->cache(60)->get();
# cache time: 60 seconds    
```

**queryCount**

```php
$db->queryCount();
# The number of all SQL queries on the page until the end of the beginning.
```

**getQuery**

```php
$db->getQuery();
# Last SQL Query.
```