PHP code example of wscore / scoresql

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

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

    

wscore / scoresql example snippets

$query = DB::db( 'mysql' )->from( 'myTable' );
// omitting connect returns standard SQL builder.
$query = DB::from( 'thisTable' );

$sqlStatement = DB::from('myTable')
    ->column('col1', 'aliased1')
    ->columns( 'col2', 'col3')
    ->filter( DB::given('status')->is('1') )
    ->select();

DB::from('myTable')
    ->column('col1', 'aliased1')
    ->columns( 'col1', 'col2' )
    ->filter( $query->status->is(1) )
    ->select();

$sqlStatement = DB::from('myTable')
    ->insert( [ 'col1' => 'val1', 'col2'=>'val2' ] );

$query->col1 = 'val1';
$query->col2 = 'val2';
$sqlStatement = DB::from('myTable')->insert();

$sqlStatement = DB::from('myTable')
    ->filter(
        DB::given('name')->like('bob')->or()->status->eq('1')
    )
    ->update( [
        'date' => $query->raw('NOW()'),
        'col2'=>'val2'
    ] );

$query->date = $query->raw('NOW()');
$query->col2 = 'val2';
$sqlStatement = DB::from('myTable')->update();

$bindValues = $query->getBind();

$sqlStatement = DB::from()... // construct SQL statement.
$bindValues   = DB::bind();   // get the binding values from last query.
$stmt = $pdo->prepare( $sqlStatement );
$stmt->execute( $bindValues );

echo DB::from('tab')
    ->filter(
        DB::given('name')->startWith('A')->gender->eq('M')
    )->filterOr(
        DB::given('name')->startWith('B')->gender->eq('F')
    );

echo DB::from('table')
    ->filter(
        DB::given('gender')->is('F')->or()->status->is('1')
    )->filter(
        DB::given('gender')->is('M')->or()->status->is('2')
    )
    ->select();

// alternative way of writing the same sql.
echo DB::from('table')
    ->filter(
        DB::bracket()
            ->gender->is('F')->or()->status->is('1')
        ->close()
        ->open()
            ->gender->is('M')->or()->status->is('2')
        ->close()
    )
    ->select();

$found2 = DB::from( 'dao_user', 'u1' )
    ->join( DB::join( 'dao_user', 'u2' )->using( 'status' ) )
    ->filter( DB::given('user_id')->is(1) )
    ->select();

$found = DB::from( 'dao_user', 'u1' )
    ->join(
        DB::join( 'dao_user', 'u2' )->left()
            ->on( DB::given('status')->identical( 'u1.status' ) )
    )
    ->filter( DB::given()->user_id->is(1) )
    ->select();

$query = DB::from( 'main' )
    ->column(
        DB::subQuery('sub')
            ->column( DB::raw('COUNT(*)'), 'count' )
            ->where( DB::given('status')->identical('$.status') ),
        'count_sub'
    );

$query = DB::from( DB::subQuery('sub')->where( DB::given('status')->is(1)) )
    ->where(
        DB::given('name')->is('bob')
    );

DB::from( 'main' )
    ->value( 'count', DB::subQuery('sub')
                        ->column( DB::raw('COUNT(*)') )
                        ->where( DB::given('status')->is(1) )
    )
    ->toUpdate();

DB::from( 'main' )
    ->value( 'count', DB::subQuery('sub')
                        ->column(DB::raw('COUNT(*)'))
                        ->where( DB::given('status')->is(1) )
    )
    ->toInsert();