PHP code example of digitalstars / simple-sql

1. Go to this page and download the library: Download digitalstars/simple-sql library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php. 
    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

digitalstars / simple-sql example snippets




use DigitalStars\SimpleSQL\Parser;

$pdo = new \PDO(DB_DSN, DB_LOGIN, DB_PASS);

Parser::setPDO($pdo);


use DigitalStars\SimpleSQL\Select;
$select = Select::create()->setFrom('table_name tn'); // Устанавливаем таблицу в From

$select->setSelect(['id', 'name', 'age']);


$select->setSelect([
    'id',
    'name',
    'age_now' => 'tn.age',
    'age_plus_10' => 'tn.age + 10',
    'age_plus_20' => 'tn.age + 20'
]);


use DigitalStars\SimpleSQL\Select;
$select = Select::create()->setFrom('table_name tn'); // Устанавливаем таблицу в From

$select->addSelect('id');
$select->addSelect('name');
$select->addSelect('age', 'age_now');
$select->addSelect('age + 10', 'age_plus_10');
$select->addSelect('age + 20', 'age_plus_20');


use DigitalStars\SimpleSQL\Select;
use DigitalStars\SimpleSQL\Components\From;
$select = Select::create()->setSelect(['id', 'name', 'age']);

$select->setFrom('table_name tn');
// Или
$select->setFrom('table_name tn', 'tn');
// Или
$from = From::create('table_name tn');
$select->setFrom($from);
// Или
$from = From::create('table_name', 'tn');
$select->setFrom($from);
// Или
$select->setFrom('table_name');


use DigitalStars\SimpleSQL\Select;

$select_sub = Select::create()
    ->setSelect(['id', 'name', 'age'])
    ->setFrom('table_name tn');

$select = Select::create()
    ->setSelect(['tn_name', 'tn_age'])
    ->setFrom($select_sub, 'sub');


use DigitalStars\SimpleSQL\Components\Join;

$select = Select::create()
    ->setSelect(['name', 'age', 't2.other_info', 't3.other_info'])
    ->setFrom('table_name_one', 'tno')
    ->addJoin(Join::inner('table_name_two t2', 'tno.id = t2.id'))
    ->addJoin(Join::left('table_name_three t3', 'tno.id = t3.id'));


$select1 = Select::create()
    ->setSelect(['id', 'name', 'age', 't2.other_info', 't3.other_info'])
    ->setFrom('table_name_one', 'tno')
    ->addJoin(Join::inner('table_name_two t2', 'tno.id = t2.id'))
    ->addJoin(Join::left('table_name_three t3', 'tno.id = t3.id'));

$select = Select::create()
    ->setSelect(["t.id", "inf.tno_name", 'sum' => 'inf.t2_other_info + IFNULL(inf.t3_other_info, 0)'])
    ->setFrom('main_table', 't')
    ->addJoin(Join::inner(From::create($select1, 'inf'), 'inf.tno_id = t.id'));


use DigitalStars\SimpleSQL\Select;
use DigitalStars\SimpleSQL\Components\Where;

$select = Select::create()
    ->setSelect(['id', 'name', 'age'])
    ->setFrom('table_name tn')
    ->setWhere(Where::create('age > ?i', 18)->w_and('name = ?s', 'John'));


use DigitalStars\SimpleSQL\Select;

$select = Select::create()
    ->setSelect(['name', 'age', 'sum_salary' => 'SUM(salary)'])
    ->setFrom('table_name tn')
    ->setGroupBy(['name', 'age']);


use DigitalStars\SimpleSQL\Select;
use DigitalStars\SimpleSQL\Components\Where;

$select = Select::create()
    ->setSelect(['name', 'age', 'sum_salary' => 'SUM(salary)'])
    ->setFrom('table_name tn')
    ->setGroupBy(['name', 'age'])
    ->setHawing(Where::create('sum_salary > ?i', 1000));


use DigitalStars\SimpleSQL\Select;

$select = Select::create()
    ->setSelect(['name', 'age', 'sum_salary' => 'SUM(salary)'])
    ->setFrom('table_name tn')
    ->setGroupBy(['name', 'age'])
    ->setOrderBy(['name' => 'ASC', 'age' => 'DESC']);


use DigitalStars\SimpleSQL\Insert;

$insert = Insert::create()
    ->setFields(['name' => '?s', 'age' => '?i'])
    ->addValues(['John', 25])
    ->addValues(['Mike', 30])
    ->setFrom('table_name')
    ->setFieldsUpdateOnDuplicate(['age']);

// Или

$insert = Insert::create()
    ->setFields(['name' => '?s', 'age' => '?i'])
    ->setValues([['John', 25], ['Mike', 30]])
    ->setFrom('table_name')
    ->setFieldsUpdateOnDuplicate(['age']);


use DigitalStars\SimpleSQL\Insert;
use DigitalStars\SimpleSQL\Select;

$select = Select::create()
    ->setSelect(['name', 'age'])
    ->setFrom('table_name_2');

$insert = Insert::create()
    ->setFields(['name' => '?s', 'age' => '?i'])
    ->setValues($select)
    ->setFrom('table_name')
    ->setIgnoreDuplicate();


use DigitalStars\SimpleSQL\Update;

$update = Update::create()
    ->setSet(['name' => '?s', 'age' => '?i'], ['John', 25])
    ->setFrom('table_name')
    ->setWhere(Where::create('tn.id = ?i', 1));

// Или

$update = Update::create()
    ->addSet('name', '?s', 'John')
    ->addSet('age', '?i', 25)
    ->setFrom('table_name')
    ->setWhere(Where::create('tn.id = ?i', 1));


use DigitalStars\SimpleSQL\Update;
use DigitalStars\SimpleSQL\Select;

$select = Select::create()
    ->setSelect(['name', 'age', 'id'])
    ->setFrom('table_name_2');

$update = Update::create()
    ->setSet(['name' => '?s', 'age' => '?i'], ['John', 25])
    ->setFrom('table_name')
    ->setWhere(Where::create('tn.id = ?i', 1))
    ->setJoin(Join::inner(From::create($select, 'tn2'), 'tn2.tn2_id = tn.id'));


use DigitalStars\SimpleSQL\Delete;

$delete = Delete::create()
    ->setFrom('table_name')
    ->setWhere(Where::create('id = ?i', 1))
    ->setLimit(1);