PHP code example of mrcnpdlk / pixie

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

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

    

mrcnpdlk / pixie example snippets

// Make sure you have Composer's autoload file included
Name of database driver or IConnectionAdapter class
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'your-database',
    'username'  => 'root',
    'password'  => 'your-password',

    // Optional
    'charset'   => 'utf8',

    // Optional
    'collation' => 'utf8_unicode_ci',

    // Table prefix, optional
    'prefix'    => 'cb_',

    // PDO constructor options, optional
    'options'   => [
        PDO::ATTR_TIMEOUT => 5,
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
];

$queryBuilder = (new \Pecee\Pixie\Connection('mysql', $config))->getQueryBuilder();

$user = $queryBuilder
            ->table('users')
            ->find(3);

$users = $queryBuilder
            ->table('users')
            ->where('hair_color', '=', 'blue')
            ->orWhere('hair_color', '=', 'red')
            ->get();

// Make sure you have Composer's autoload file included
  => 'localhost',
    'database'  => 'your-database',
    'username'  => 'root',
    'password'  => 'your-password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
];

// Creates new connection
$connection = new \Pecee\Pixie\Connection('mysql', $config);

// Get the query-builder object which will initialize the database connection
$queryBuilder = $connection->getQueryBuilder();

// Run query
$person = $queryBuilder
            ->table('persons')
            ->where('name', '=', 'Bobby')
            ->first();

$queryBuilder = new \Pecee\Pixie\Connection('sqlite', [
    'driver'   => 'sqlite',
    'database' => 'your-file.sqlite',
    'prefix'   => 'cb_',
]);

$queryBuilder = new \Pecee\Pixie\Connection('pgsql', [
    'driver'   => 'pgsql',
    'host'     => 'localhost',
    'database' => 'your-database',
    'username' => 'postgres',
    'password' => 'your-password',
    'charset'  => 'utf8',
    'prefix'   => 'cb_',
    'schema'   => 'public',
]);

$queryBuilder->table(array('mytable1', 'mytable2'));

$queryBuilder
    ->table(['table1' => 'foo1'])
    ->join('table2', 'table2.person_id', '=', 'foo1.id');

$queryBuilder->alias('foo1', 'table1');

// Simplified way...

$queryBuilder->table('table1')->alias('foo1');

$row = $queryBuilder
            ->table('my_table')
            ->find(3);

$result = $queryBuilder
            ->table('my_table')
            ->findAll('name', 'Sana');

$queryBuilder
    ->select(
        [
            'mytable.myfield1',
            'mytable.myfield2',
            'another_table.myfield3'
        ]
    );

$queryBuilder->selectDistinct(array('mytable.myfield1', 'mytable.myfield2'));

$subQuery = $queryBuilder->table('person');
$builder = $queryBuilder->table($queryBuilder->subQuery($subQuery))->where('id', '=', 2);

$queryBuilder->table('my_table')->select('*');

$queryBuilder->table('my_table')->select(array('field1', 'field2'));

$results = $queryBuilder
                ->table('my_table')
                ->where('name', '=', 'Sana')
                ->get();

foreach ($results as $row) {
    echo $row->name;
}

$row = $queryBuilder
            ->table('my_table')
            ->where('name', '=', 'Sana')
            ->first();

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'Sana')
    ->count();

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'Sana')
    ->sum('views');

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'Sana')
    ->average('views');

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'Sana')
    ->min('views');

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'Sana')
    ->max('views');

// Creates the first sub-query

$subQuery1 =
    $queryBuilder
        ->table('mail')
        ->select(
            $queryBuilder->raw('COUNT(*)')
        );

// Create the second sub-query

$subQuery2 =
    $queryBuilder
        ->table('event_message')
        ->select(
            $queryBuilder->raw('COUNT(*)')
        );

// Executes the query which uses the subqueries as fields
$count =
    $queryBuilder
        ->select(
            $queryBuilder->subQuery($subQuery1, 'row1'),
            $queryBuilder->subQuery($subQuery2, 'row2')
        )
        ->first();

$queryBuilder->where($queryBuilder->subQuery($subQuery), '!=', 'value');

$queryBuilder
    ->table('my_table')
    ->where('name', '=', 'usman')
    ->whereNot('age', '>', 25)
    ->orWhere('type', '=', 'admin')
    ->orWhereNot('description', 'LIKE', '%query%');

$queryBuilder
    ->table('my_table')
    ->whereIn('name', array('usman', 'sana'))
    ->orWhereIn('name', array('heera', 'dalim'));

$queryBuilder
    ->table('my_table')
    ->whereNotIn('name', array('heera', 'dalim'))
    ->orWhereNotIn('name', array('usman', 'sana'));

$queryBuilder
    ->table('my_table')
    ->whereBetween('id', 10, 100)
    ->orWhereBetween('status', 5, 8);

$queryBuilder
    ->table('my_table')
    ->whereNull('modified')
    ->orWhereNull('field2')
    ->whereNotNull('field3')
    ->orWhereNotNull('field4');

$queryBuilder
    ->table('my_table')
    ->where('my_table.age', 10)
    ->where(function(QueryBuilderHandler $qb) {
        $qb->where('name', 'LIKE', '%pecee%');

        // You can provide a closure on these wheres too, to nest further.
        $qb->orWhere('description', 'LIKE', '%usman%');
    });

$query = $queryBuilder
            ->table('my_table')
            ->groupBy('age')
            ->orderBy('created_at', 'ASC');

$queryBuilder
    ->groupBy(array('mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3'));
    ->orderBy(array('mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3'));

$queryBuilder
    ->having('total_count', '>', 2)
    ->orHaving('type', '=', 'admin');

$queryBuilder
    ->limit(30);
    ->offset(10);

$queryBuilder
    ->table('my_table')
    ->join('another_table', 'another_table.person_id', '=', 'my_table.id')

$queryBuilder
    ->join('another_table', 'another_table.person_id', '=', 'my_table.id', 'FULL OUTER')

$queryBuilder
    ->table('user')
    ->join('user_data', 'user_data.user_id', '=', 'user.user_id');

$queryBuilder
    ->table('user')
    ->joinUsing('user_data', 'user_id');

$queryBuilder
    ->join('another_table', function($table)
    {
        $table
            ->on('another_table.person_id', '=', 'my_table.id')
            ->on('another_table.person_id2', '=', 'my_table.id2')
            ->orOn('another_table.age', '>', $queryBuilder->raw(1));
    })

$firstQuery =
    $queryBuilder
    ->table('people')
    ->whereNull('email');

$secondQuery =
    $queryBuilder
    ->table('people')
    ->where('hair_color', '=', 'green')
    ->union($firstQuery);

$thirdQuery =
    $queryBuilder
    ->table('people')
    ->where('gender', '=', 'male')
    ->union($secondQuery);

$items = $thirdQuery->get();

$query = $queryBuilder->query('SELECT * FROM persons WHERE age = 12');

$kids = $query->get();

$queryBuilder
    ->query('SELECT * FROM persons WHERE age = ? AND name = ?', array(10, 'usman'));

$queryBuilder
    ->table('my_table')
    ->select($queryBuilder->raw('count(cb_my_table.id) as tot'))
    ->where('value', '=', 'Ifrah')
    ->where($queryBuilder->raw('DATE(?)', 'now'))

$data = [
    'name' => 'Sana',
    'description' => 'Blah'
];

$insertId = $queryBuilder
                ->table('my_table')
                ->insert($data);

$data = [
    array(
        'name'        => 'Sana',
        'description' => 'Blah'
    ),
    array(
        'name'        => 'Usman',
        'description' => 'Blah'
    ),
];

$insertIds = $queryBuilder
                ->table('my_table')
                ->insert($data);

$data = [
    'name'    => 'Sana',
    'counter' => 1
];

$dataUpdate = [
    'name'    => 'Sana',
    'counter' => 2
];

$insertId =
    $queryBuilder
        ->table('my_table')
        ->onDuplicateKeyUpdate($dataUpdate)
        ->insert($data);

$data = [
    'name'        => 'Sana',
    'description' => 'Blah'
];

$queryBuilder
    ->table('my_table')
    ->where('id', 5)
    ->update($data);

$queryBuilder
    ->table('my_table')
    ->where('id', '>', 5)
    ->delete();

$queryBuilder
    ->transaction(function (Transaction $transaction) {

        $transaction
            ->table('my_table')
            ->insert(array(
                'name' => 'Test',
                'url' => 'example.com'
            );

        $transaction
            ->table('my_table')
            ->insert(array(
                'name' => 'Test2',
                'url' => 'example.com'
            ));
});

$queryBuilder
    ->transaction(function (Transaction $transaction)
        {
            $transaction
                ->table('my_table')
                ->insert($data);

            // Commit changes (data will be saved)
            $transaction->commit();

            // Rollback changes (data would be rejected)
            $transaction->rollback();
        }
    );

$queryBuilder->table('people')->insert([
    [
        'name' => 'Simon',
        'age' => 12,
        'awesome' => true,
        'nickname' => 'ponylover94',
    ],
    [
        'name' => 'Peter',
        'age' => 40,
        'awesome' => false,
        'nickname' => null,
    ],
    [
        'name' => 'Bobby',
        'age' => 20,
        'awesome' => true,
        'nickname' => 'peter',
    ],
]);

$queryHandler =
    $queryBuilder
        ->table('my_table')
        ->where('id', '=', 3)
        ->getQuery();

$queryHandler->getSql();

$queryHandler->getBindings();

$queryHandler->getRawSql();

$queryString = $queryBuilder->getLastQuery()->getRawSql();

$subQuery =
    $queryBuilder
        ->table('person_details')
        ->select('details')
        ->where('person_id', '=', 3);


$query =
    $queryBuilder
        ->table('my_table')
        ->select('my_table.*')
        ->select(
            $queryBuilder->subQuery($subQuery, 'table_alias1')
        );

$nestedQuery =
    $queryBuilder
        ->table(
            $queryBuilder->subQuery($query, 'table_alias2')
        )
        ->select('*');

// Execute query
$result = $nestedQuery->get();

$queryBuilder->pdo();

$connection = $queryBuilder->getConnection();

$queryBuilder
    ->table('my_table')
    ->asObject('SomeClass', array('ctor', 'args'))
    ->first();

$queryBuilder
    ->table('my_table')
    ->setFetchMode(PDO::FETCH_COLUMN|PDO::FETCH_UNIQUE)
    ->get();

$queryBuilder
    ->setOverwriteEnabled(true);

$adapterConfig = [
    'query_overwriting' => false,
];

$queryBuilder->registerEvent(EventHandler::EVENT_BEFORE_SELECT, 'users', function(EventArguments $arguments)
{
    $arguments
        ->getQueryBuilder()
        ->where('status', '!=', 'banned');
});

$queryBuilder->registerEvent(EventHandler::EVENT_AFTER_INSERT, 'my_table', function(EventArguments $arguments)
{
    $arguments
        ->getQueryBuilder()
        ->table('person_details')->insert(array(
        'person_id' => $insertId,
        'details' => 'Meh',
        'age' => 5
    ));
});

$queryBuilder->registerEvent(EventHandler::EVENT_AFTER_INSERT, 'person_details', function(EventArguments $arguments)
{
    $arguments
        ->getQueryBuilder()
        ->table('person_details')
        ->where('id', $insertId)
        ->update([
            'created_at' => date('Y-m-d H:i:s')
        ]);
});

$queryBuilder->registerEvent(EventHandler::EVENT_AFTER_DELETE, 'my_table', function(EventArguments $arguments)
{
    $bindings = $arguments->getQuery()->getBindings();

    $arguments
        ->getQueryBuilder()
        ->table('person_details')
        ->where('person_id', $binding[0])
        ->delete();
});

$queryBuilder->removeEvent($event, $table = null);

$sql = $exception->getQueryObject()->getRawSql();