PHP code example of phpixie / database

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

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

    

phpixie / database example snippets

$slice = new \PHPixie\Slice();
$database = new \PHPixie\Database($slice->arrayData(array(
    'default' => array(
        'driver' => 'pdo',
        'connection' => 'sqlite::memory:'
    )
)));

return array(
    //You can define multiple connections
    //each with a different name
    'default' => array(
        'driver'     => 'pdo',
        
        //MySQL
        'connection' => 'mysql:host=localhost;dbname=phpixie',
        
        //or SQLite
        'connection' => 'sqlite:/some/file',
        
        //or Postgres
        'connection' => 'pgsql:dbname=exampledb',
        
        'user'       => 'root',
        'password'   => 'password'
    ),
    
    'other' => array(
        //connecting to MongoDD
        'driver'   => 'mongo',
        'database' => 'phpixie',
        'user'     => 'pixie',
        'password' => 'password'
    )
);

$connection = $database->get('default');

// SELECT * FROM `posts` WHERE `status`='published'
// LIMIT 5 OFFSET 1
$query = $connection->selectQuery();
$posts = $query
    ->table('posts')
    ->where('status', 'Published')
    ->limit(5)
    ->offset(1)
    ->execute();
    
// Specifying fields
$query->fields(array('id'));

// You can remove limit, offset
// specified fields, etc from the query
// using clearSomething()
$query->clearFields();

//And get it using getSomething()
$query->getFields();

//Using OR and XOR logic
$query
    ->where('status', 'published')
    ->orWhereNot('status', 'deleted')
    ->xorWhere('id', 5);
    
//Shorthand functions
$query
    ->and('status', 'published')
    ->orNot('status', 'deleted')
    ->xor('id', 5);

// WHERE `status` = 'published'
// OR NOT (`id` = 4 AND `views` = 5)
$query
    ->where('status', 'published')
    ->startOrNotGroup()
        ->where('id', 4)
        ->and('views', 4)
    ->endGroup();

// Less verbose syntax
$query
    ->where('status', 'published')
    ->or(function(query) {
        $query
            ->where('id', 4)
            ->and('views', 4);
    });
    
// More verbose syntax
// Useful for programmatic filters
$query
    ->addOperatorCondition(
        $logic    = 'and',
        $negate   = false,
        $field    = 'status',
        $operator = '=',
        array('published')
    )
    ->startConditionGroup(
        $logic    = 'and',
        $negate   = false
    );

// So far we only compared fields with values
// But there are other operators available

// >, < , >=, <=, '!='
$query->where('views', '>', 5);

// comparies fields to other fields
// can be done by adding an '*'
$query->where('votes', '>=*', 'votesRequired');

// Between
$query->where('votes', 'between', 5, 6);

// In
$query->where('votes', 'in', array(5, 6));

// Like
$query->where('name', 'like', 'Welcome%');

// Regexp
$query->where('name', 'regexp', '.*');

// SQL expression
$expression = $database->sqlExpression('LOWER(?)', array('text'));
$query->where('title', $expression);

// You can also use it for fields
// SELECT COUNT(1) as `count`
$expression = $database->sqlExpression('COUNT(1)');
$query->fields(array(
    'count' => $expression
));

// When specofying a table
// you can also define an alias for it
$query->table('posts', 'p');

// INNER JOIN `categories`
$query->join('categories')

// LEFT JOIN `categories` AS `c`
$query->join('categories', 'c', 'left')

$query
    ->on('p.categoryId', 'c.categoryId');
    
// The on() conditions can be used in
// the same way as where(), and apply
// to the last join() statement
$query
    ->join('categories', 'c', 'left')
        ->on('p.categoryId', 'c.id')
        ->or('p.parentCategoryId', 'c.id')
    ->join('authors')
        ->on('p.authorId', 'authors.id');
        
// You can use subqueries as tables,
// but you must supply the alias parameter

$query->join($subqeury, 'c', 'left')

//UNIONs
$query->union($subquery, $all = true);

$query
    ->fields(array(
        'count' => $database->sqlExpression('COUNT(1)');
    ))
    ->having('count', '>', 5)
    ->or('count', '<', 2);

// Delete syntax is very similar to select
// except it doesn't support HAVING syntax
$connection->deleteQuery()
    ->where('id', 5)
    ->execute();

// Count query is a shorthand that returns the count
// of matched items
$count = $connection->countQuery()
    ->where('id', '>', 5)
    ->execute();

// Inserting
$insertQuery = $connection->insertQuery();
$insertQuery->data(array(
    'id'    => 1,
    'title' => 'Hello'
))->execute();

// Insert multiple rows
$insertQuery->batchData(
    array('id', 'title'),
    array(
        array(1, 'Hello'),
        array(2, 'World'),
    )
)->execute();

// Getting insert id
$connection->insertId();

// Updating
$updateQuery = $connection->updateQuery();
$updateQuery
    ->set('name', 'Hello')
    ->where('id', 4)
    ->execute();

// increment values
$updateQuery
    ->increment(array(
        'views' => 1
    ))
    ->execute();

$query
    ->where('status', 'published')
    ->startOrGroup();

// Add placeholder inside the OR goup
$placeholder = $query->addPlaceholder(
    $logic  = 'and',
    $negate = false,
    $allowEmpty = false
);

$query
        ->and('views', '>', 5);
    ->endGroup();

// so far this results in
// WHERE `status` = 'published'
// OR (<placeholder> AND `views` > 5)

// Now we can replace the placeholder by
// adding conditions to it
$placeholder->where('votes', '>', 5);

$database->beginTransaction();
// ...
try {
    // ...
    $database->commitTransaction();
} catch(\Exception $e) {
    $database->rollbackTransaction();
    throw $e;
}

$name = $database->savepointTransaction();
$database->rollbackTransactionTo($name);

$posts = $query
    ->collection('posts')
    // subdocument conditions
    ->where('author.name', 'Dracony')
    ->limit(1)
    ->offset(1)
    ->execute();

$connection->updateQuery()
    ->collection('posts')
    ->set('done', true)
    ->unset(array('started', 'inProgress'))
    ->execute();
    
$connection->insertQuery()
    ->collection('posts')
    ->batchData(array(
        array(
            'name' => 'Trixie'
        ),
        array(
            'name' => 'Stella'
        )
    ))
    ->execute();

//setting conditions for subdocuments
$query
    ->startOrNotSubdocumentGroup('author')
        ->where('name', 'Dracony')
    ->endGroup();
    
//setting conditions for subarray items
$query
    ->startOrNotSubarrayItemGroup('authors')
        ->where('name', 'Dracony')
    ->endGroup();