PHP code example of tivins / database

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

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

    

tivins / database example snippets

use Tivins\Database\Database;
use Tivins\Database\Connectors\MySQLConnector;

st'));

$posts = $db->select('books', 'b')
    ->leftJoin('users', 'u', 'b.author_id = u.id')
    ->addFields('b')
    ->addField('u', 'name', 'author_name')
    ->condition('b.year', 2010)
    ->execute()
    ->fetchAll();

# MySQL
$connector = new MySQLConnector('dbname', 'user', 'password');
# SQLite
$connector = new SQLiteConnector('path/to/file');

$db = new Database (new MySQLConnector(
  dbname:   'my_database', 
  user:     'my_user', 
  password: 'my_encrypted_password',
  host:     'localhost',
  port:     3306,
));

$database = new Database($connector);

// from database object
$query = $db->select('users', 'u');
// from new object
$query = new SelectQuery($db, 'users', 'u');

$data = $db->select('books', 'b')
    ->addFields('b')
    ->condition('b.reserved', 0)
    ->execute()
    ->fetchAll();

$db->select('books', 'b')
    ->addFields('b', ['id', 'title'])
    ->leftJoin('users', 'u', 'u.id = b.owner')
    ->addField('u', 'name', 'owner_name')
    ->condition('b.reserved', 1)
    ->execute()
    ->fetchAll();

$db->select('books', 'b')
    ->addField('b', 'title')
    ->addExpression('concat(title, ?)', 'some_field', time())
    ->condition('b.reserved', 0)
    ->execute()
    ->fetchAll();

$tagsQuery = $db->select('tags', 't')
    ->innerJoin('book_tags', 'bt', 'bt.tag_id = t.id')
    ->addFields('t')
    ->addExpression('count(bt.book_id)', 'books_count')
    ->groupBy('t.id')
    ->orderBy('t.name', 'asc');

$db->select('books', 'b')
    ->addFields('b')
    ->conditionExpression('concat(b.id, "-", ?) = b.reference', $someValue)
    ->execute();

$query->limit(10);          # implicit start from 0.
$query->limitFrom(0, 10);   # explicit start from 0.
$query->limitFrom(100, 50); # will fetch 50 rows from 100th row.

$query->orderBy('field', 'desc');

$query->orderBy('post_type', 'desc')
      ->orderBy('date', 'asc');

$db->insert('book')
    ->fields([
        'title' => 'Book title',
        'author' => 'John Doe',
    ])
    ->execute();

$db->insert('book')
    ->multipleFields([
        ['title' => 'Book title', 'author' => 'John Doe'],
        ['title' => 'Another book title', 'author' => 'John Doe Jr'],
    ])
    ->execute();

$db->insert('book')
    ->multipleFields([
          ['Book title', 'John Doe'],
          ['Another book title', 'John Doe Jr'],
        ], 
        ['title', 'author'])
    ->execute();

$db->insert('geom')
    ->fields([
        'name'     => $name,
        'position' => new InsertExpression('POINT(?,?)', $x, $y)
    ])
    ->execute();

  [$name, $x, $y]
  

$db->update('book')
    ->fields(['reserved' => 1])
    ->condition('id', 123)
    ->execute();

$db->merge('book')
    ->keys(['ean' => '123456'])
    ->fields(['title' => 'Book title', 'author' => 'John Doe'])
    ->execute();

$db->delete('book')
    ->whereIn('id', [3, 4, 5])
    ->execute();

$query = $db->create('sample')
    ->addAutoIncrement(name: 'id')
    ->addInteger('counter', 0, unsigned: true, nullable: false)
    ->addInteger('null_val', null, nullable: false)
    ->addJSON('json_field')
    ->execute();

  $query->addPointer('id_user'); // Shortcut to Not-null Unsigned Integer
  

  Enum Fruits { case Apple; case Banana; }
  $query->addEnum('fruits', Fruits::cases());
  

  $query->addStdEnum('fruits', ['apple','banana'], 'apple');
  

$qry = $db->selectInsert('users')->matching(['name' => 'test', 'state' => 1]);
$qry->fetch()->id; // 1
$qry->getProcessedOperation(); // MergeOperation::INSERT

$qry = $db->selectInsert('users')->matching(['name' => 'test', 'state' => 1]);
$qry->fetch()->id; // 1
$qry->getProcessedOperation(); // MergeOperation::SELECT

$matches = ['email' => '[email protected]'];
$obj = $db->selectInsert('users')
    ->matching($matches)
    ->fields($matches + ['name' =>  'user', 'created' => time()])
    ->fetch();

$query = $db->select('books', 'b')
    ->addExpression('concat(title, ?)', 'some_field', time())
    ->execute();

$total = $db->select('table','t')
    ->addCount('*')
    ->execute()
    ->fetchField();

->condition('field', 2);      // eg: where field = 2
->condition('field', 2, '>'); // eg: where field > 2
->condition('field', 2, '<'); // eg: where field < 2
->whereIn('field', [2,6,8]);  // eg: where field in (2,6,8)
->like('field', '%search%');  // eg: where field like '%search%'
->isNull('field');            // eg: where field is null
->isNotNull('field');         // eg: where field is not null

$db->select('book', 'b')
    ->fields('b', ['id', 'title', 'author'])
    ->condition(
        $db->or()
        ->condition('id', 3, '>')
        ->like('title', '%php%')
    )
    ->execute();

$db->select('book', 'b')
    ->fields('b', ['id', 'title', 'author'])
    ->condition(
        (new Conditions(Conditions::MODE_OR))
        ->condition('id', 3, '>')
        ->like('title', '%php%')
    )
    ->execute();

$db->select('maps_polygons', 'p')
    // ->...
    ->having($db->and()->isNotNull('geom'))
    ->execute()
    //...
    ;

use Tivins\Database{ Database, DatabaseException, MySQLConnector };
function makeSomething(Database $db)
{
    $db->transaction()
    try {
        // do some stuff
    }
    catch (DatabaseException $exception) {
        $db->rollback();
        // log exception...
    }
}

try {
    $this->db = new Database($connector);
}
catch (ConnectionException $exception) {
    $this->logErrorInternally($exception->getMessage());
    $this->displayError("Cannot join the database.");
}

try {
    $this->db->insert('users')
        ->fields([
            'name' => 'DuplicateName',
        ])
        ->execute();  
}
catch (DatabaseException $exception) {
    $this->logErrorInternally($exception->getMessage());
    $this->displayError("Cannot create the user.");
}

sql
    insert into `book` (`title`,`author`) values (?,?), (?,?);
    

sql
  insert into `geom` (`name`, `position`) values (?, POINT(?,?))