Категории
Метки

Эффективный конструктор запросов для PHP PDO

    PHP    226

Содержание:

Эффективный конструктор запросов для PHP PDO

PDOx - это быстрый, эффективный и полезный конструктор запросов и класс PDO для PHP. Является отличной альтернативой для RedBeanPHP.

Репозиторий на GitHub по ссылке. Там же можно найти документацию.

В файл composer.json добавить:

{
  "require": {
    "izniburak/pdox": "^1"
  }
}

Затем выполнить команду:

composer install

Или установить командой:

composer require izniburak/pdox

Необходимо в настройках установить соединение с базой данных:

require 'vendor/autoload.php';

$config = [
 'host'		=> 'localhost',
 'driver'	=> 'mysql',
 'database'	=> 'pdox',
 'username'	=> 'root',
 'password'	=> 'root',
 'charset'	=> 'utf8mb4',
 'collation'	=> 'utf8mb4_unicode_ci',
 'prefix'	 => '',
 'cachedir'	=> __DIR__ . 'cache/sql/' # указать свой путь(директории cache/sql необходимо создать заранее)
];

$db = new \Buki\Pdox($config);

# Вариант #1: Параметры в строку через запятую
$db->select('name, status, image')->table('product')->getAll();
# Output: "SELECT name, status, image FROM product"

$db->select('name AS n, status AS s, image AS i')->table('product')->getAll();
# Output: "SELECT name AS n, status AS s, image AS i FROM product"

# Вариант #2: Параметры массивом
$db->select(['name', 'status', 'image'])->table('product')->getAll();
# Output: "SELECT name, status, image FROM product"

$db->select(['name AS n', 'status AS s'])->table('product')->getAll();
# Output: "SELECT name AS n, status AS s FROM product"

# Вариант #1:
$db->table('price')->max('tarif')->get();
# Output: "SELECT MAX(tarif) FROM price"

# Вариант #2:
$db->table('product')->count('id', 'total_count')->get();
# Output: "SELECT COUNT(id) AS total_row FROM product"

# get(): Одна запись.
# getAll(): Множество записей.
$db->table('product')->getAll();
# Output: "SELECT * FROM product"

$db->select('name')->table('product')->where('category_id', 1)->getAll();
# Output: "SELECT name FROM product WHERE category_id='1'"

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

$where = [
 'name' => 'Adam Smith',
 'age' => 22,
 'email' => 'email@adam.com',
];

$db->table('customer')->where($where)->get();
# Output: "SELECT * FROM customer WHERE name='Adam Smith' AND age='22' AND email='email@adam.com' LIMIT 1"

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

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

$db->table('customer')->where('age = ? OR age = ?', [25, 35])->getAll();
# Output: "SELECT * FROM customer WHERE age='25' OR age='35'"

$db->table('product')->where('status', '=', 1)->notWhere('category_id', 1)->getAll();
# Output: "SELECT * FROM product WHERE status = '1' AND NOT category_id = '1'"

$db->table('product')->where('category_id', 1)->orWhere('category_id', '>', 5)->getAll();
# Output: "SELECT * FROM product WHERE category_id = '1' OR category_id > '5'"

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

$db->table('product')
  ->grouped(function($query) {
    $query->where('image', null)->orWhere('category_id', 2);
  })
  ->where('status', 1)
  ->getAll();
# Output: "SELECT * FROM product WHERE (image = NULL OR category_id ='2') AND status ='1'"

$db->table('product')->where('status', 1)->in('id', [1, 5, 12])->getAll();
# Output: "SELECT * FROM product WHERE status = '1' AND id IN ('1', '5', '12')"

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

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

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

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

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

$db->table('customer')->like('name', "%Vir%")->getAll();
# Output: "SELECT * FROM customer WHERE name LIKE '%Vir%'"

$db->table('customer')->where('status', 1)->notLike('name', '%Vir%')->getAll();
# Output: "SELECT * FROM customer WHERE status = '1' AND name NOT LIKE '%Vir%'"

$db->table('customer')->like('name', '%Vir%')->orLike('name', '%Jen%')->getAll();
# Output: "SELECT * FROM customer WHERE name LIKE '%Vir%' OR name LIKE '%Jen%'"

# Пример #1: Один параметр
$db->table('product')->where('status', 1)->groupBy('manufacturer_id')->getAll();
# Output: "SELECT * FROM product WHERE status = '1' GROUP BY category_id"

# Пример #2: Массив параметров
$db->table('product')->where('status', 1)->groupBy(['name', 'manufacturer_id'])->getAll();
# Output: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id, user_id"

$db->table('customer')->where('status', 1)->groupBy('name')->having('MAX(age)', 20)->getAll();
# Output: "SELECT * FROM customer WHERE status='1' GROUP BY name HAVING MAX(age) > '20'"

$db->table('customer')->where('status', 1)->groupBy('name')->having('AVG(age)', '<=', 30)->getAll();
# Output: "SELECT * FROM customer WHERE status='1' GROUP BY name HAVING AVG(age) <= '30'"

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

# Пример #1: Один параметр
$db->table('product')->where('status', 1)->orderBy('category_id')->getAll();
# Output: "SELECT * FROM product WHERE status='1' ORDER BY category_id ASC"

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

// Пример #2: Два параметра
$db->table('product')->where('status', 1)->orderBy('category_id', 'desc')->getAll();
# Output: "SELECT * FROM product WHERE status='1' ORDER BY category_id DESC"

# Пример #3: Rand()
$db->table('product')->where('status', 1)->orderBy('rand()')->limit(10)->getAll();
# Output: "SELECT * FROM product WHERE status='1' ORDER BY rand() LIMIT 10"

# Пример #1: Один параметр
$db->table('product')->limit(10)->getAll();
# Output: "SELECT * FROM product LIMIT 10"

# Пример #2: Два параметра
$db->table('product')->limit(10, 20)->getAll();
# Output: "SELECT * FROM product LIMIT 10, 20"

# Пример #3: С методом offset()
$db->table('product')->limit(10)->offset(10)->getAll();
# Output: "SELECT * FROM product LIMIT 10 OFFSET 10"

# Первый параметр: количество данных на странице
# Второй параметр: Активная страница
$db->table('product')->pagination(15, 1)->getAll();
# Output: "SELECT * FROM product LIMIT 15 OFFSET 0"

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

$insert = [
  'name' => 'Product 31',
  'category_id' => 5,
  'manufacturer_id' => 1,
  'status' => 1,
  'image' => null,
];

$db->table('product')->insert($insert);
# Output: "INSERT INTO product (name, category_id, manufacturer_id, status, image)
#           VALUES ('Product 31', '5', '1', '1', NULL")

var_dump($db->insertId()); // Если успешное добавление - вернётся ID добавленной записи

# Если данные обновились, то вернётся 1, иначе вернётся 0
$update = [
  'name' => 'Product 1 updated',
  'status' => 1,
  'image' => null,
];

$db->table('product')->where('id', 1)->update($update);
# Output: "UPDATE product SET name='Product 1 updated', status='1', image=NULL WHERE id='1'"

# Удалить запись в таблице
$db->table('product')->where('id', 31)->delete();
# Output: "DELETE FROM product WHERE id = '31'"

# Очистить всю таблицу
$db->table('product')->delete();
# Output: "TRUNCATE TABLE delete"

# Пример #1: Найти все записи
$db->query('SELECT * FROM product WHERE `category_id` = ? AND status = ?', [1, 1])->fetchAll();

# Пример #2: Найти одну запись
$db->query('SELECT * FROM product WHERE id = ? AND status = ?', [1, 1])->fetch();

# Пример #3: Другие запросы: like, update, insert, delete, etc...
$db->query("UPDATE `product` SET `status` = ? WHERE id = ?", [0, 1])->exec();

var_dump($db->numRows());

# Используйте: ...->cache($time)->...
$db->table('product')->where('status', 1)->cache(60)->getAll();
# cache time: 60 seconds

var_dump($db->error());

# Количество всех SQL-запросов на странице
$queryCount = $db->queryCount();
var_dump($queryCount);

# Последний SQL запрос
$lastQuery = $db->getQuery();
var_dump($lastQuery);

# Пример #1:
$db->table('product as p')
  ->select('p.id as id, p.name as name, c.name as category')
  ->join('category as c', 'c.id', 'p.category_id')
  ->where('p.status', 1)
  ->getAll();
# Output:
# "SELECT p.id AS id, p.name AS name, c.name AS category
#  FROM product as p
#  JOIN category as c ON c.id=p.category_id WHERE p.status='1'"

# Пример #2:
$db->table('product as p')
  ->select('p.id as id, p.name as name, c.name as category')
  ->leftJoin('category as c', 'c.id', '=', 'p.category_id')
  ->where('p.status', 1)
  ->limit(5)
  ->getAll();
# Output: "SELECT p.id AS id, p.name AS name, c.name AS category
# FROM product as p
# JOIN category as c ON c.id=p.category_id WHERE p.status='1' LIMIT 5"
Утверждения в PHPUnit
Логирование в PHP проектах

Другие записи по теме PHP

Логирование в PHP проектах

Логирование в PHP проектах

Показаны способы реализации записи данных в лог-файлы. Простой способ логирования, использование пакета monolo...