PHP code example of latinosoft / pixie

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

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

    

latinosoft / pixie example snippets

// Make sure you have Composer's autoload file included
          'driver'    => 'mysql', // Db driver
            'host'      => 'localhost',
            'database'  => 'your-database',
            'username'  => 'root',
            'password'  => 'your-password',
            'charset'   => 'utf8', // Optional
            'collation' => 'utf8_unicode_ci', // Optional
            'prefix'    => 'cb_', // Table prefix, optional
            'options'   => array( // PDO constructor options, optional
                PDO::ATTR_TIMEOUT => 5,
                PDO::ATTR_EMULATE_PREPARES => false,
            ),
        );

$connection = new \Pixie\Connection($config);

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

$query = $qb->table('my_table')->where('name', '=', 'Sana');

// Get result
$query->get();

$qb->registerEvent('before-select', 'users', function($qb)
{
    $qb->where('status', '!=', 'banned');
});

// Make sure you have Composer's autoload file included
/ Db driver/adapter
            'host'      => 'localhost',
            'database'  => 'your-database',
            'username'  => 'root',
            'password'  => 'your-password',
            'charset'   => 'utf8', // Optional
            'collation' => 'utf8_unicode_ci', // Optional
            'prefix'    => 'cb_', // Table prefix, optional
        );

new \Pixie\Connection($config);

//$connection here is optional, if not given it will always associate itself to the first connection, but it can be useful when you have multiple database connections.
$qb = new \Pixie\QueryBuilder\QueryBuilderHandler($connection);

// Run query
$query = $qb->table('my_table')->where('name', '=', 'Sana');

$connection = new \Pixie\Connection(array(
        'driver'   => 'sqlite',
        'database' => 'your-file.sqlite',
        'prefix'   => 'cb_',
));

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

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

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

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

$query = $qb->table('my_table')->select('*');

->select(array('mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3'));

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

$query = $qb->table('my_table')->where('name', '=', 'Sana');
$result = $query->get();

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

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

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

$subQuery1 = $this->builder->table('mail')->select($this->builder->raw('COUNT(*)'));
$subQuery2 = $this->builder->table('event_message')->select($this->builder->raw('COUNT(*)'));

$count = $this->builder->select($this->builder->subQuery($subQuery1, 'row1'), $this->builder->subQuery($subQuery2, 'row2'))->first();

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

$qb->table(['my_table' => 'm'])
    ->where('m.name', '=', 'usman')
    ->whereNot('m.age', '>', 25)
    ->orWhere('m.type', '=', 'admin')
    ->orWhereNot('m.description', 'LIKE', '%query%')
    ->select(['m.age' => 'my_age', 'm.type' => 'my_type'])
    ;

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

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

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

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

$qb->table('my_table')
            ->where('my_table.age', 10)
            ->where(function($q)
                {
                    $q->where('name', 'LIKE', '%usman%');
                    // You can provide a closure on these wheres too, to nest further.
                    $q->orWhere('description', 'LIKE', '%usman%');
                });

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

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

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

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

->limit(30);

->offset(10);

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

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

$qb->table(['my_table' => 'm'])
    ->join(['another_table' => 'a'], 'a.person_id', '=', 'm.id')

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

$query = $qb->query('select * from cb_my_table where age = 12');

var_dump($query->get());

$qb->query('select * from cb_my_table where age = ? and name = ?', array(10, 'usman'));

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

$data = array(
    'name' => 'Sana',
    'description' => 'Blah'
);
$insertId = $qb->table('my_table')->insert($data);

$data = array(
    array(
        'name'        => 'Sana',
        'description' => 'Blah'
    ),
    array(
        'name'        => 'Usman',
        'description' => 'Blah'
    ),
);
$insertIds = $qb->table('my_table')->insert($data);

$data = array(
    'name'    => 'Sana',
    'counter' => 1
);
$dataUpdate = array(
    'name'    => 'Sana',
    'counter' => 2
);
$insertId = $qb->table('my_table')->onDuplicateKeyUpdate($dataUpdate)->insert($data);

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

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

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

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

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

$qb->transaction(function (qb) {
    $qb->table('my_table')->insert(array(/* data... */));

    $qb->commit(); // to commit the changes (data would be saved)
    $qb->rollback(); // to rollback the changes (data would be rejected)
});

$query = $qb->table('my_table')->where('id', '=', 3);
$queryObj = $query->getQuery();

$queryObj->getSql();
// Returns: SELECT * FROM my_table where `id` = ?

$queryObj->getBindings();
// Returns: array(3)

$queryObj->getRawSql();
// Returns: SELECT * FROM my_table where `id` = 3

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


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

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

$qb->pdo();

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

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

$qb->registerEvent('before-select', 'users', function($qb)
{
    $qb->where('status', '!=', 'banned');
});

$qb->registerEvent('after-insert', 'my_table', function($queryBuilder, $insertId)
{
    $data = array('person_id' => $insertId, 'details' => 'Meh', 'age' => 5);
    $queryBuilder->table('person_details')->insert($data);
});

$qb->registerEvent('after-insert', 'person_details', function($queryBuilder, $insertId)
{
    $queryBuilder->table('person_details')->where('id', $insertId)->update(array('created_at' => date('Y-m-d H:i:s')));
});

$qb->registerEvent('after-delete', 'my_table', function($queryBuilder, $queryObject)
{
    $bindings = $queryObject->getBindings();
    $queryBuilder->table('person_details')->where('person_id', $binding[0])->delete();
});

$qb->removeEvent('event-name', 'table-name');

sql
SELECT (SELECT COUNT(*) FROM `cb_mail`) as row1, (SELECT COUNT(*) FROM `cb_event_message`) as row2 LIMIT 1