PHP code example of simplon / postgres

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

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

    

simplon / postgres example snippets

$dbConn->fetchRow('SELECT * FROM names WHERE name = :name', array('name' => 'Peter'));

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT * FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

$pgSqlManager = new \Simplon\Postgres\Manager\PgSqlManager($dbConn);
$pgSqlManager->fetchRow($sqlBuilder);

$config = array(
    // ocalhost',
    'user'       => 'rootuser',
    'password'   => 'rootuser',
    'database'   => 'our_database',
    
    // optional
    
    'fetchMode'  => \PDO::FETCH_ASSOC,
    'charset'    => 'utf8',
    'port'       => 3306,
    'unixSocket' => null,
);

// standard setupPostgres
$dbConn = new \Simplon\Postgres\Postgres(
    $config['host'],
    $config['user'],
    $config['password'],
    $config['database']
);

\Simplon\Postgres\Postgres::__construct(
    $host,
    $user,
    $password,
    $database,
    $fetchMode = \PDO::FETCH_ASSOC,
    $charset = 'utf8',
    array $options = array('port' => 3306, 'unixSocket' => '')
);

$pgSqlManager = new \Simplon\Postgres\Manager\PgSqlManager($dbConn);

$result = $dbConn->fetchColumn('SELECT id FROM names WHERE name = :name', array('name' => 'Peter'));

// result
var_dump($result); // '1' || false

$result = $dbConn->fetchColumnMany('SELECT id FROM names WHERE name = :name', array('name' => 'Peter'));

// result
var_dump($result); // ['1', '15', '30', ...] || false

$cursor = $dbConn->fetchColumnMany('SELECT id FROM names WHERE name = :name', array('name' => 'Peter'));

foreach ($cursor as $result)
{
    var_dump($result); // '1'
}

$result = $dbConn->fetchRow('SELECT id, age FROM names WHERE name = :name', array('name' => 'Peter'));

var_dump($result); // ['id' => '1', 'age' => '22'] || false

$result = $dbConn->fetchRowMany('SELECT id, age FROM names WHERE name = :name', array('name' => 'Peter'));

var_dump($result); // [ ['id' => '1', 'age' => '22'],  ['id' => '15', 'age' => '40'], ... ] || false

$result = $dbConn->fetchRowMany('SELECT id, age FROM names WHERE name = :name', array('name' => 'Peter'));

foreach ($cursor as $result)
{
    var_dump($result); // ['id' => '1', 'age' => '22']
}

$data = array(
    'id'   => false,
    'name' => 'Peter',
    'age'  => 45,
);

$id = $dbConn->insert('names', $data);

var_dump($id); // 50 || bool

$data = array(
    array(
        'id'   => false,
        'name' => 'Peter',
        'age'  => 45,
    ),
    array(
        'id'   => false,
        'name' => 'Peter',
        'age'  => 16,
    ),
);

$id = $dbConn->insertMany('names', $data);

var_dump($id); // 50 || bool

$conds = array(
    'id' => 50,
);

$data = array(
    'name' => 'Peter',
    'age'  => 50,
);

$result = $dbConn->update('names', $conds, $data);

var_dump($result); // true || false

$conds = array(
    'id'   => 50,
    'name' => 'Peter',
);

// custom conditions query
$condsQuery = 'id = :id OR name =: name';

$data = array(
    'name' => 'Peter',
    'age'  => 50,
);

$result = $dbConn->update('names', $conds, $data, $condsQuery);

var_dump($result); // true || false

$data = array(
    'id'   => 5,
    'name' => 'Peter',
    'age'  => 16,
);

$result = $dbConn->replace('names', $data);

var_dump($result); // 1 || false

$data = array(
    array(
        'id'   => 5,
        'name' => 'Peter',
        'age'  => 16,
    ),
    array(
        'id'   => 10,
        'name' => 'John',
        'age'  => 22,
    ),
);

$result = $dbConn->replaceMany('names', $data);

var_dump($result); // [5, 10]  || false

$result = $dbConn->delete('names', array('id' => 50));

var_dump($result); // true || false

$conds = array(
    'id'   => 50,
    'name' => 'John',
);

// custom conditions query
$condsQuery = 'id = :id OR name =: name';

$result = $dbConn->delete('names', $conds, $condsQuery);

var_dump($result); // true || false

$result = $dbConn->executeSql('TRUNCATE names');

var_dump($result); // true

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

$result = $pgSqlManager->fetchColumn($sqlBuilder);

// result
var_dump($result); // '1' || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

$result = $pgSqlManager->fetchColumnMany($sqlBuilder);

// result
var_dump($result); // ['1', '15', '30', ...] || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

foreach ($pgSqlManager->fetchColumnMany($sqlBuilder) as $result)
{
    var_dump($result); // '1'
}

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id, age FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

$result = $pgSqlManager->fetchRow($sqlBuilder);

var_dump($result); // ['id' => '1', 'age' => '22'] || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id, age FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

$result = $pgSqlManager->fetchRowMany($sqlBuilder);

var_dump($result); // [ ['id' => '1', 'age' => '22'],  ['id' => '15', 'age' => '40'], ... ] || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setQuery('SELECT id, age FROM names WHERE name = :name')
    ->setConditions(array('name' => 'Peter'));

foreach ($pgSqlManager->fetchRowManyCursor($sqlBuilder) as $result)
{
    var_dump($result); // ['id' => '1', 'age' => '22']
}

$data = array(
    'id'   => false,
    'name' => 'Peter',
    'age'  => 45,
);

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setData($data);

$id = $pgSqlManager->insert($sqlBuilder);

var_dump($id); // 50 || false

$data = array(
    array(
        'id'   => false,
        'name' => 'Peter',
        'age'  => 45,
    ),
    array(
        'id'   => false,
        'name' => 'Peter',
        'age'  => 16,
    ),
);

$sqlBuilder = (new \Simplon\Postgres\Manager\PgSqlQueryBuilder())
    ->setTableName('names')
    ->setData($data);

$result = $pgSqlManager->insert($sqlBuilder);

var_dump($id); // [50, 51, ...] || false

$data = array(
    'name' => 'Peter',
    'age'  => 50,
);

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setConditions(array('id' => 50))
    ->setData($data);

$result = $pgSqlManager->update($sqlBuilder);

var_dump($result); // true || false

$data = array(
    'name' => 'Peter',
    'age'  => 50,
);

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setConditions(array('id' => 50))
    ->setConditionsQuery('id = :id OR name =: name')
    ->setData($data)

$result = $pgSqlManager->update($sqlBuilder);

var_dump($result); // true || false

$data = array(
    'id'   => 5,
    'name' => 'Peter',
    'age'  => 16,
);

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setData($data);

$result = $pgSqlManager->replace($sqlBuilder);

var_dump($result); // 1 || false

$data = array(
    array(
        'id'   => 5,
        'name' => 'Peter',
        'age'  => 16,
    ),
    array(
        'id'   => 10,
        'name' => 'John',
        'age'  => 22,
    ),
);

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setData($data);

$result = $pgSqlManager->replaceMany($sqlBuilder);

var_dump($result); // [5, 10]  || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setConditions(array('id' => 50));

$result = $pgSqlManager->delete($sqlBuilder);

var_dump($result); // true || false

$sqlBuilder = new \Simplon\Postgres\Manager\PgSqlQueryBuilder();

$sqlBuilder
    ->setTableName('names')
    ->setConditions(array('id' => 50, 'name' => 'Peter'))
    ->setConditionsQuery('id = :id OR name =: name');

$result = $pgSqlManager->delete($sqlBuilder);

var_dump($result); // true || false

$ids = array(1,2,3,4,5);
$query = "SELECT * FROM users WHERE id IN (" . join(',', $ids) . ")";

$emails = array('[email protected]', '[email protected]');
$query = "SELECT * FROM users WHERE email IN (" . join(',', $emails) . ")";

// integers
$conds = array('ids' => array(1,2,3,4,5));
$query = "SELECT * FROM users WHERE id IN (:ids)";

// strings
$conds = array('emails' => array('[email protected]', '[email protected]'));
$query = "SELECT * FROM users WHERE email IN (:emails)";

class UserVo extends \Simplon\Postgres\Crud\PgSqlCrudVo
{
    protected $id;
    protected $name;
    protected $email;
    protected $createdAt;
    protected $updatedAt;

    // ... here goes getter/setter for the above variables
} 

/**
* construct it with an instance of your simplon/Postgres
*/
$PgSqlCrudManager = new \Simplon\Postgres\Crud\PgSqlCrudManager($postgresInstance);

$userVo = new UserVo();

$userVo
    ->setName('Johnny Foobar')
    ->setEmail('[email protected]');

/** @var UserVo $userVo */
$userVo = $PgSqlCrudManager->create($userVo);

// print insert id
echo $userVo->getId(); // 1

// conditions: where id = 1
$conds = array('id' => 1);

/** @var UserVo $userVo */
$userVo = $PgSqlCrudManager->read(new UserVo(), $conds);

// print name
echo $userVo->getName(); // Johnny Foobar

// conditions: where id = 1
$conds = array('id' => 1);

/** @var UserVo $userVo */
$userVo = $PgSqlCrudManager->read(new UserVo(), $conds);

// set new name
$userVo->setName('Hansi Hinterseher');

// update
/** @var UserVo $userVo */
$userVo = $PgSqlCrudManager->update($userVo, $conds);

// print name
echo $userVo->getName(); // Hansi Hinterseher

// conditions: where id = 1
$conds = array('id' => 1);

/**
* UserVo::crudGetSource() is the name of the table
* based on the value object's name
*/
$PgSqlCrudManager->update(UserVo::crudGetSource(), $conds);

class PersonVo extends \Simplon\Postgres\Crud\PgSqlCrudVo
{
    /**
    * @return string
    */
    public static function crudGetSource()
    {
        return 'people';
    }

    // ... here goes the rest
}

class UserVo extends \Simplon\Postgres\Crud\PgSqlCrudVo
{
    protected $id;
    protected $name;
    protected $email;
    protected $createdAt;
    protected $updatedAt;

    /**
    * @return array
    */
    public function crudColumns()
    {
        return array(
            'id'        => 'xx_id',
            'name'      => 'xx_name',
            'email'     => 'xx_email',
            'createdAt' => 'xx_created_at',
            'updatedAt' => 'xx_updated_at',
        );
    }

    // ... here goes the rest
}

class UserVo extends \Simplon\Postgres\Crud\PgSqlCrudVo
{
    protected $id;
    protected $name;
    protected $email;
    protected $createdAt;
    protected $updatedAt;

    // helper property: not part of the people table
    protected $isOffline;

    /**
    * @return array
    */
    public function crudIgnore()
    {
        return array(
            'isOffline',
        );
    }

    // ... here goes the rest
}