# PdoData Documentation

## Quick Usage

```php

require 'vendor/autoload.php';

$config = [
	'driver'    => 'mysql',
	'host'      => 'localhost',
	'database'  => 'test',
	'username'  => 'root',
	'password'  => '',
	'charset'   => 'utf8',
	'collation' => 'utf8_general_ci',
	'prefix'    => ''
];

$db = new PdoData($config);
```

Поздравляю! Теперь вы можете использовать PDOx.

# Detailed Usage and Methods

## config
```php
$config = [
	# Database Driver Type (optional)
	# default value: mysql
	# values: mysql, pgsql, sqlite, oracle
	'driver'    => 'mysql',

	# Host name or IP Address (optional)
	# hostname:port (for Port Usage. Example: 127.0.0.1:1010)
	# default value: localhost
	'host'      => 'localhost',

	# IP Address for Database Host (optional)
	# default value: null
	'port'      => 3306,

	# Database Name (required)
	'database'  => 'test',

	# Database User Name (required)
	'username'  => 'root',

	# Database User Password (required)
	'password'  => '',

	# Database Charset (optional)
	# default value: utf8
	'charset'   => 'utf8',

	# Database Charset Collation (optional)
	# default value: utf8_general_ci
	'collation' => 'utf8_general_ci',

	# Database Prefix (optional)
	# default value: null
	'prefix'     => '',

	# Cache Directory of the Sql Result (optional)
	# default value: __DIR__ . '/cache/'
	'cachedir'	=> __DIR__ . '/cache/sql/'
];

$db = new PdoData($config);
```

## Contents

 * [select](#select)
 * [select functions (min, max, sum, avg, count)](#select-functions-min-max-sum-avg-count)
 * [table](#table)
 * [get AND getAll](#get-and-getall)
 * [join](#join)
 * [where](#where)
 * [grouped](#grouped)
 * [in](#in)
 * [between](#between)
 * [like](#like)
 * [groupBy](#groupby)
 * [having](#having)
 * [orderBy](#orderby)
 * [limit - offset](#limit---offset)
 * [pagination](#pagination)
 * [insert](#insert)
 * [update](#update)
 * [delete](#delete)
 * [analyze](#analyze) - [check](#check) - [checksum](#checksum) - [optimize](#optimize) - [repair](#repair)
 * [query](#query)
 * [insertId](#insertid)
 * [numRows](#numrows)
 * [cache](#cache)
 * [transaction](#transaction) - [commit](#transaction) - [rollBack](#transaction)
 * [error](#error)
 * [queryCount](#querycount)
 * [getQuery](#getquery)

## Methods

### select
```php
# Использование 1: string parameter
$db->select('title, content')->table('test')->getAll();
# Результат: "SELECT title, content FROM test"

$db->select('title AS t, content AS c')->table('test')->getAll();
# Результат: "SELECT title AS t, content AS c FROM test"
```
```php
# Использование: array parameter
$db->select(['title', 'content'])->table('test')->getAll();
# Результат: "SELECT title, content FROM test"

$db->select(['title AS t', 'content AS c'])->table('test')->getAll();
# Результат: "SELECT title AS t, content AS c FROM test"
```

### select functions (min, max, sum, avg, count)
```php
# Использование 1:
$db->table('test')->max('price')->get();
# Результат: "SELECT MAX(price) FROM test"

# Использование 2:
$db->table('test')->count('id', 'total_row')->get();
# Результат: "SELECT COUNT(id) AS total_row FROM test"
```

### table
```php
### Использование 1: string parameter
$db->table('table');
# Результат: "SELECT * FROM table"

$db->table('table1, table2');
# Результат: "SELECT * FROM table1, table2"

$db->table('table1 AS t1, table2 AS t2');
# Результат: "SELECT * FROM table1 AS t1, table2 AS t2"
```
```php
### Использование 2: array parameter
$db->table(['table1', 'table2']);
# Результат: "SELECT * FROM table1, table2"

$db->table(['table1 AS t1', 'table2 AS t2']);
# Результат: "SELECT * FROM table1 AS t1, table2 AS t2"
```

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

$db->table('test')->getAll(); 
# Результат: "SELECT * FROM test"

$db->select('username')->table('users')->where('status', 1)->getAll();
# Результат: "SELECT username FROM users WHERE status='1'"

$db->select('title')->table('pages')->where('id', 17)->get(); 
# Результат: "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();
# Результат: "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();
# Результат: "SELECT * FROM test as t LEFT JOIN foo as f ON t.id=f.t_id"
```

```php
$db->table('test as t')->fullOuterJoin('foo as f', 't.id', 'f.t_id')->getAll();
# Результат: "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();
# Результат: "SELECT * FROM test WHERE name='Burak' AND age='23' AND status='1' LIMIT 1"

# OR

$db->table('test')->where('active', 1)->getAll();
# Результат: "SELECT * FROM test WHERE active='1'"

# OR

$db->table('test')->where('age', '>=', 18)->getAll();
# Результат: "SELECT * FROM test WHERE age>='18'"

# OR

$db->table('test')->where('age = ? OR age = ?', [18, 20])->getAll();
# Результат: "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();
# Результат: "SELECT * FROM test WHERE active = '1' AND NOT auth = '1'"

# OR

$db->table('test')->where('age', 20)->orWhere('age', '>', 25)->getAll();
# Результат: "SELECT * FROM test WHERE age = '20' OR age > '25'"

$db->table('test')->whereNotNull('email')->getAll();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "SELECT * FROM test WHERE active = '1' OR age BETWEEN '18' AND '25'"
```

### like
```php
$db->table('test')->like('title', "%php%")->getAll();
# Результат: "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();
# Результат: "SELECT * FROM test WHERE active = '1' AND tags NOT LIKE '%dot-net%'"

# OR

$db->table('test')->like('bio', '%php%')->orLike('bio', '%java%')->getAll();
# Результат: "SELECT * FROM test WHERE bio LIKE '%php%' OR bio LIKE '%java%'"
```

### groupBy
```php
# Использование 1: One parameter
$db->table('test')->where('status', 1)->groupBy('cat_id')->getAll();
# Результат: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id"
```

```php
# Использование 1: Array parameter
$db->table('test')->where('status', 1)->groupBy(['cat_id', 'user_id'])->getAll();
# Результат: "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();
# Результат: "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();
# Результат: "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();
# Результат: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"
```

### orderBy
```php
# Использование 1: One parameter
$db->table('test')->where('status', 1)->orderBy('id')->getAll();
# Результат: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"

### OR

$db->table('test')->where('status', 1)->orderBy('id desc')->getAll();
# Результат: "SELECT * FROM test WHERE status='1' ORDER BY id desc"
```

```php
# Использование 1: Two parameters
$db->table('test')->where('status', 1)->orderBy('id', 'desc')->getAll();
# Результат: "SELECT * FROM test WHERE status='1' ORDER BY id DESC"
```

```php
# Использование 3: Rand()
$db->table('test')->where('status', 1)->orderBy('rand()')->limit(10)->getAll();
# Результат: "SELECT * FROM test WHERE status='1' ORDER BY rand() LIMIT 10"
```

### limit - offset
```php
# Использование 1: One parameter
$db->table('test')->limit(10)->getAll();
# Результат: "SELECT * FROM test LIMIT 10"
```
```php
# Использование 2: Two parameters
$db->table('test')->limit(10, 20)->getAll();
# Результат: "SELECT * FROM test LIMIT 10, 20"

# Использование 3: with offset method
$db->table('test')->limit(10)->offset(10)->getAll();
# Результат: "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();
# Результат: "SELECT * FROM test LIMIT 15 OFFSET 0"

$db->table('test')->pagination(15, 2)->getAll();
# Результат: "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);
# Результат: "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);
# Результат: "UPDATE users SET username='izniburak', password='pass', activation='1', status='1' WHERE id='10'"
```

### delete
```php
$db->table('test')->where("id", 17)->delete();
# Результат: "DELETE FROM test WHERE id = '17'"

# OR

$db->table('test')->delete();
# Результат: "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();
# Результат: "ANALYZE TABLE users"
```

### check
```php
$db->table(['users', 'pages'])->check();
# Результат: "CHECK TABLE users, pages"
```

### checksum
```php
$db->table(['users', 'pages'])->checksum();
# Результат: "CHECKSUM TABLE users, pages"
```

### optimize
```php
$db->table(['users', 'pages'])->optimize();
# Результат: "OPTIMIZE TABLE users, pages"
```

### repair
```php
$db->table(['users', 'pages'])->repair();
# Результат: "REPAIR TABLE users, pages"
```

### query
```php
# Использование 1: Выберите все записи
$db->query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetchAll();

# Использование 2: Выберите одну запись
$db->query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetch();

# Использование 3: Другие запросы, такие как 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
# Использование: ...->cache($time)->...
$db->table('pages')->where('slug', 'example-page')->cache(60)->get(); 
# время кэширования: 60 секунд
```

### queryCount
```php
$db->queryCount(); 
# Количество всех SQL-запросов на странице от начала до конца.
```

### getQuery
```php
$db->getQuery(); 
# Последний SQL-запрос.
```
