PHP code example of francerz / sql-builder

    
        
<?php
require_once('vendor/autoload.php');

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

    

francerz / sql-builder example snippets

$db = DatabaseManager::connect('driver://user:password@host:port/database');

putenv('DATABASE_SCHOOL_DRIVER', 'driver');
putenv('DATABASE_SCHOOL_HOST', 'host');
putenv('DATABASE_SCHOOL_INST', 'instanceName');
putenv('DATABASE_SCHOOL_PORT', 'port');
putenv('DATABASE_SCHOOL_USER', 'user');
putenv('DATABASE_SCHOOL_PSWD', 'password');
putenv('DATABASE_SCHOOL_NAME', 'database');

// Support to Docker secrets
putenv('DATABASE_SCHOOL_PSWD_FILE', '/run/secrets/db_school_password');

$db = DatabaseManager::connect('school');

class Group {
    public $group_id;
    public $subject;
    public $teacher;
}

// SELECT group_id, subject, teacher FROM groups
$query = Query::selectFrom('groups', ['group_id', 'subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeSelect($query);
$groups = $result->toArray(Group::class);

$group = new Group();
$group->subject = 'Database fundamentals';
$group->teacher = 'francerz';

// INSERT INTO groups (subject, teacher) VALUES ('Database fundamentals', 'francerz')
$query = Query::insertInto('groups', $group, ['subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeInsert($query);
$group->group_id = $result->getInsertedId();

$group = new Group();
$group->group_id = 10;
$group->subject = 'Introduction to databases';

// UPDATE groups SET subject = 'Introduction to databases' WHERE group_id = 10
$query = Query::update('groups', $group, ['group_id'], ['subject']);

$db = DatabaseManager::connect('school');
$result = $db->executeUpdate($query);

// DELETE FROM groups WHERE group_id = 10
$query = Query::deleteFrom('groups', ['group_id' => 10]);

$db = DatabaseManager::connect('school');
$result = $db->executeDelete($query);

// Explicit syntax
$query = Query::selectFrom('groups')->where()->equals('group_id', 10);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', 10);

// Explicit syntax
$query = Query::selectFrom('groups')->where()->in('group_id', [10, 20, 30]);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', [10, 20, 30]);

// Explicit syntax
$query = Query::selectFrom('groups')->where()->null('teacher');

// Implicit compact syntax
$query = Query::selectFrom('groups')->where('teacher', 'NULL');

// Explicit syntax
$query = Query::selectFrom('groups');
$query->where()->lessEquals('group_id', 10)->andLike('subject', '%database%');

// Implicit compact syntax
$query = Query::selectFrom('groups');
$query->where('group_id', '<=', 10)->andLike('subject', '%database%');

$query->where(function($subwhere) { });
$query->where->not(function($subwhere) { });
$query->where->and(function($subwhere) { });
$query->where->or(function($subwhere) { });
$query->where->andNot(function($subwhere) { });
$query->where->orNot(function($subwhere) { });

$query = Query::selectFrom('groups');

// Using an anonymous function to emulate parenthesis
$query->where()
    ->like('subject', '%database%')
    ->and(function(ConditionList $subwhere) {
        $subwhere
            ->equals('group_id', 10)
            ->orBetween('group_id', 20, 30);
    });

// INNER JOIN
$query->innerJoin($table, $columns = []);

// CROSS JOIN
$query->crossJoin($table, $columns = []);

// LEFT JOIN
$query->leftJoin($table, $columns = []);

// RIGHT JOIN
$query->rightJoin($table, $columns = []);

$query = Query::selectFrom('groups');
$query
    ->innerJoin('teachers')
    ->on('groups.teacher_id', 'teachers.teacher_id');

// Alias array syntax
$query = Query::selectFrom(['g' => 'groups']);
$query
    ->innerJoin(['t' => 'teachers'])
    ->on('g.teacher_id', 't.teacher_id');

// Alias "AS" string syntax
$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin('teachers AS t')
    ->on('g.teacher_id', 't.teacher_id');

$query = Query::selectFrom('school.groups AS g');
$query
    ->innerJoin('hr.employees AS e')
    ->on('g.teacher_id','e.employee_id');

$query = Query::selectFrom('groups AS g', ['group_id']);
$query
    ->innerJoin('teachers AS t', ['given_name', 'family_name'])
    ->on('g.teacher_id', 't.teacher_id');

$query = Query::selectFrom('groups AS g', ['group_id']);
$query
    ->innerJoin('teachers AS t', [
        'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
    ])->on('g.teacher_id', 't.teacher_id');

$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin('teachers AS t')
    ->on('g.teacher_id', 't.teacher_id');
$query->columns([
    'g.group_id',
    'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
]);

// Creating subquery object
$subquery = Query::selectFrom('teachers');
$subquery->where('active', 1);

$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin(['t' => $subquery])
    ->on('g.teacher_id', 't.teacher_id');
$query->columns([
    'g.group_id',
    'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
]);

// Primary Query for Groups
$groupsQuery = Query::selectFrom(
    'groups',
    ['group_id', 'subject', 'classroom']
);

// Query for Students
$studentsQuery = Query::selectFrom(
    'students',
    ['student_id', 'group_id', 'first_name', 'last_name']
);

// Nesting students within each group
$groupsQuery
    ->nestMany('Students', $studentsQuery, $groupRow, Student::class)
    ->where('students.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);

// Primary Query for Groups
$groupsQuery = Query::selectFrom(
    'groups',
    ['group_id', 'teacher_id', 'subject', 'classroom']
);

// Query for Teachers
$teachersQuery = Query::selectFrom(
    'teachers',
    ['teacher_id', 'first_name', 'last_name']
);

// Linking the first teacher to each group
$groupsQuery
    ->linkFirst('Teacher', $teachersQuery, $groupRow, Teacher::class)
    ->where('teachers.teacher_id', $groupRow->teacher_id);

// Query for Classes
$classesQuery = Query::selectFrom(
    'groups_classes',
    ['class_id', 'group_id', 'topic', 'date']
)->orderBy('date', 'ASC');

// Linking the last class to each group
$groups
    ->linkLast('LastClass', $classesQuery, $groupRow, GroupClass::class)
    ->where('groups_classes.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);

> $groupsQuery->nest(['Students' => $studentsQuery], function (NestedSelect $nest, RowProxy $row) {
>     $nest->getSelect()->where('s.group_id', $row->group_id);
> }, NestMode::COLLECTION, Student::class);
> 

$db = DatabaseManager::connect('school');
try {
    $db->startTransaction();
    
    // Perform any needed operation inside this block to keep consistency.
    
    $db->commit();
} catch (Exception $ex) {
    $db->rollback();
}

// Connecting to database 'school'.
$db = DatabaseManager::connect('school');

// Calls stored procedure with two argments.
/** @var SelectResult[] */
$results = $db->call('procedure_name', 'arg1', 'arg2');

// Shows how many results obtained from procedure.
echo count($results) . ' results.' . PHP_EOL;

// Iterating procedure result sets.
foreach ($results as $i => $selectResult) {
    echo "Fetched " . $selectResult->getNumRows() . PHP_EOL;
}