PHP code example of popphp / pop-db

1. Go to this page and download the library: Download popphp/pop-db library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.
    
        
<?php
require_once('vendor/autoload.php');

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

    

popphp / pop-db example snippets


use Pop\Db\Db;

$db = Db::connect('mysql', [
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS',
    'host'     => 'localhost'
]);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS',
    'host'     => 'localhost'
]);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$users = $db->select('SELECT * FROM `users`');
print_r($users);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db->insert(
    'INSERT INTO `users` (`username`, `password`, `email`) VALUES (?, ?, ?)',
    ['testuser1', 'password1', '[email protected]']
);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db->query('SELECT * FROM `users`');
$users = $db->fetchAll();
print_r($users);

use Pop\Db\Db;
use Pop\Db\Record;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

class Users extends Record {}

Record::setDb($db);

$users = Users::findAll();
print_r($users->toArray());

$user = Users::findById(1);
print_r($user->toArray());

$user = Users::findById(1);
$user->username = 'testuser2';
$user->email    = '[email protected]'; 
$user->save();
print_r($user->toArray());

$user = new Users([
    'username' => 'newuser',
    'password' => 'somepassword',
    'email'    => '[email protected]'
]);
$user->save();
print_r($user->toArray());

$user = Users::findById(1);
$user->delete();

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db = Db::pgsqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db = Db::mysqlConnect([
    'database' => '/path/to/my_database.sqlite',
]);

$db = Db::sqlsrvConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db = Db::pdoConnect([
    'type'     => 'mysql'
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

use Pop\Db\Db;
use Pop\Db\Record;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

class Users extends Record {}

Record::setDb($db);

use Pop\Db\Db;
use Pop\Db\Record;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$dbUsers = Db::mysqlConnect([
    'database' => 'DATABASE_FOR_USERS',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

class Users extends Record {};

Users::setDb($dbUsers); // Only the users table class uses the $dbUsers connection
Record::setDb($db);     // All other table classes will use the $db connection

class Users extends Record
{
    protected ?string $table       = 'users_table';
    protected ?string $prefix      = 'my_app_';
    protected array   $primaryKeys = ['user_id'];
}

// Fetch a single user record by ID
$user = Users::findById(1);

// Search for a single user record
$user = Users::findOne(['username' => 'testuser']);

// Search for a single user record, or create one if it doesn't exist
$user = Users::findOneOrCreate(['username' => 'testuser']);

// Search for the latest single user record
$user = Users::findLatest();

// Search for the latest single user record by 'last_login'
$user = Users::findLatest('last_login');

$user->username = 'newusername';
$user->save();

$user->delete();

$user->increment('attempts'); // Increment column by one and save
$user->decrement('capacity', 5); // Decrement column by 5 and save

// Make a new copy of the user record in the database
// The $replace parameter can be an array of new, overriding column values
$newUser = $user->copy($replace);

$user->username = 'newusername';
$user->email    = '[email protected]';

if ($user->isDirty()) {
    print_r($user->getDirty);
}

use Pop\Db\Record\Encoded

class Users extends Encoded
{
    protected array $jsonFields   = ['metadata'];
    protected array $phpFields    = ['user_info'];
    protected array $base64Fields = ['user_image'];
} 

use Pop\Db\Record\Encoded

class Users extends Encoded
{

    protected array  $hashFields    = ['password'];
    protected string $hashAlgorithm = PASSWORD_BCRYPT;
    protected array  $hashOptions   = ['cost' => 10];
}

$user = Users::findOne(['username' => 'testuser']);
if ($user->verify('password', $attemptedPassword)) {
    // The user submitted the correct password.
}

use Pop\Db\Record\Encoded

class Users extends Encoded
{
    protected array   $encryptedFields = ['sensitive_data'];
    protected ?string $cipherMethod    = 'aes-256-cbc';
    protected ?string $key             = 'YOUR_KEY';
    protected ?string $iv              = 'BASE64_ENCODED_IV_STRING';
}

// Find all users who have never logged in.
$users = Users::findBy(['logins' => 0]);

// Find a group of users
$users = Users::findIn('username', ['testuser', 'someotheruser', 'anotheruser']);

// Find all users
$users = Users::findAll();

// Returns an array
$users = Users::findBy(['logins' => 0])->toArray();

// 3rd parameter $asArray set to true; Returns an array
$users = Users::findBy(['logins' => 0], null, true);

// Get count of all users
$count = Users::getTotal();

// Get count of all users who have never logged in.
$count = Users::getTotal(['logins' => 0]);

$users = Users::findBy(['logins' => 0], [
    'select' => ['id', 'username'],
    'order'  => ['id DESC'],
    'offset' => 10
    'limit'  => 25
]);

$users = Users::findBy(['logins' => 0], [
    'select' => [
        Users::table() . '.*',
        Roles::table() . '.role',
    ],
    'join' => [
        'table'   => Roles::table(),
        'columns' => [
            Roles::table() . '.id' => Users::table() . '.role_id',
        ],
    ],
]);

$users = Users::findBy(['id' => 1]);   // WHERE id = 1
$users = Users::findBy(['id!=' => 1]); // WHERE id != 1
$users = Users::findBy(['id>' => 1]);  // WHERE id > 1
$users = Users::findBy(['id>=' => 1]); // WHERE id >= 1
$users = Users::findBy(['id<' => 1]);  // WHERE id < 1
$users = Users::findBy(['id<=' => 1]); // WHERE id <= 1

$users = Users::findBy(['%username%'   => 'test']); // WHERE username LIKE '%test%'
$users = Users::findBy(['username%'    => 'test']); // WHERE username LIKE 'test%'
$users = Users::findBy(['%username'    => 'test']); // WHERE username LIKE '%test'
$users = Users::findBy(['-%username'   => 'test']); // WHERE username NOT LIKE '%test'
$users = Users::findBy(['username%-'   => 'test']); // WHERE username NOT LIKE 'test%'
$users = Users::findBy(['-%username%-' => 'test']); // WHERE username NOT LIKE '%test%'

$users = Users::findBy(['username' => null]);  // WHERE username IS NULL
$users = Users::findBy(['username-' => null]); // WHERE username IS NOT NULL

$users = Users::findBy(['id' => [2, 3]]);  // WHERE id IN (2, 3)
$users = Users::findBy(['id-' => [2, 3]]); // WHERE id NOT IN (2, 3)

$users = Users::findBy(['id' => '(1, 5)']);  // WHERE id BETWEEN (1, 5)
$users = Users::findBy(['id-' => '(1, 5)']); // WHERE id NOT BETWEEN (1, 5)

$users = Users::findBy([
    'id>'       => 1,
    '%username' => 'user1'
]);

$users = Users::findBy([
    'id>'       => 1,
    '%username' => 'user1 OR'
]);

$users = Users::query('SELECT * FROM ' . Users::table());

$sql   = 'SELECT * FROM ' . Users::table() . ' WHERE last_login >= :last_login';
$users = Users::execute($sql, ['last_login' => '2023-11-01 08:00:00']);

$user = new Users([
    'username' => 'testuser',
    'password' => 'password',
    'email'    => '[email protected]'
]);
$user->startTransaction();
$user->save();

$user = Users::start([
    'username' => 'testuser',
    'password' => 'password',
    'email'    => '[email protected]'
]);
$user->save();

try {
    Record::start();

    $user = new Users([
        'username' => 'testuser',
        'password' => 'password',
        'email'    => '[email protected]'
    ]);
    $user->save();

    $role = new Roles([
        'role' => 'Admin'
    ]);
    $role->save();

    Record::commit();
} catch (\Exception $e) {
    Record::rollback();
    echo $e->getMessage();
}

try {
    Record::transaction(function() {
        $user = new Users([
            'username' => 'testuser',
            'password' => 'password',
            'email'    => '[email protected]'
        ]);
        $user->save();
    
        $role = new Roles([
            'role' => 'Admin'
        ]);
        $role->save();
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

try {
    Record::transaction(function() {
        $user = new Users([
            'username' => 'testuser',
            'password' => 'password',
            'email'    => '[email protected]'
        ]);
        $user->save();
        
        Record::transaction(function(){
            $role = new Roles([
                'role' => 'Admin'
            ]);
            $role->save();
        });
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

class Users extends Pop\Db\Record
{

    /**
     * Mock Schema
     *    - id
     *    - role_id (FK to roles.id)
     *    - username
     *    - password
     *    - email
     */

    // Define the 1:1 relationship of the user's role
    public function role()
    {
        return $this->hasOneOf('Roles', 'role_id');
    }

    // Define the 1:1 relationship of the info record this user owns
    public function info()
    {
        return $this->hasOne('Info', 'user_id')
    }

    // Define the 1:many relationship to all the orders this user owns
    public function orders()
    {
        return $this->hasMany('Orders', 'user_id');
    }

}

class Roles extends Pop\Db\Record
{
    /**
     * Mock Schema
     *    - id (FK to users.role_id)
     *    - role
     */
}

class Info extends Pop\Db\Record
{
    /**
     * Mock Schema
     *    - user_id (FK to users.id)
     *    - address
     *    - phone
     */
    // Define the parent relationship up to the user that owns this info record
    public function user()
    {
        return $this->belongsTo('Users', 'user_id');
    }

}

class Orders extends Pop\Db\Record
{
    /**
     * Mock Schema
     *    - id
     *    - user_id (FK to users.id)
     *    - order_date
     *    - order_total
     *    - products
     */

    // Define the parent relationship up to the user that owns this order record
    public function user()
    {
        return $this->belongsTo('Users', 'user_id');
    }

}

// The two 1:1 relationships
$user = Users::findById(1);
print_r($user->role()->toArray());
print_r($user->info()->toArray());

// The 1:many relationship
$user   = Users::findById(1);
$orders = $users->orders();

foreach ($orders as $order) {
    echo 'Order Total: $' . $order->order_total . PHP_EOL;
}

// The inverse 1:1 relationship
$userInfo = UserInfo::findOne(['user_id' => 1]);
print_r($userInfo->user()->toArray());

$users = Users::with('orders')->getById(1);
foreach ($user->orders as $order) {
    echo 'Order Total: $' . $order->order_total . PHP_EOL;
}

$users = Users::with(['role', 'info', 'orders'])->getById(1);

$user = Users::with('posts.comments')->getById(1);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$users = $db->select('SELECT * FROM `users`');
print_r($users);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$users = $db->select('SELECT * FROM `users` WHERE `id` < ?', [10]);
print_r($users);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db->query('SELECT * FROM `users`');
$users = $db->fetchAll();
print_r($users);

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$db->prepare('SELECT * FROM `users` WHERE `id` = ?');
$db->bindParams(['id' => 1]);
$db->execute();

$users = $db->fetchAll();
print_r($users);

try {
    $db->beginTransaction();
    $db->query("INSERT INTO `users` (`username`, `email`) VALUES ('testuser', '[email protected]')");
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}

try {
    $db->beginTransaction();
    $db->prepare("INSERT INTO `users` (`username`, `email`) VALUES (?, ?)")
        ->bindParam([
            'username' => 'testuser',
            'email'    => '[email protected]'
        ]);
    $db->execute();
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}

try {
    $db->transaction(function() use ($db) {
        $db->query(
            "INSERT INTO `users` (`username`, `email`) VALUES ('testuser', '[email protected]')"
        );
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

try {
    $db->transaction(function() use ($db) {
        $db->query(
            "INSERT INTO `users` (`username`, `email`) VALUES ('testuser1', '[email protected]')"
        );
        $db->transaction(function() use ($db) {
            $db->query(
                "INSERT INTO `users` (`username`, `email`) VALUES ('testuser2', '[email protected]')"
            );
        });
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$sql = $db->createSql();
$sql->select(['id', 'username'])
    ->from('users')
    ->where('id = :id');

echo $sql;

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

$sql = $db->createSql();
$sql->select(['id', 'username'])
    ->from('users')
    ->where('id = :id');

$db->prepare($sql);
$db->bindParams(['id' => 1]);
$db->execute();

$users = $db->fetchAll();
print_r($users);

$sql->insert('users')->values([
    'username' => ':username',
    'password' => ':password'
]);

echo $sql;

$sql->update('users')->values([
    'username' => ':username',
    'password' => ':password'
])->where('id = :id');

echo $sql;

$sql->delete('users')
    ->where('id = :id');

echo $sql;

$sql->select(['id', 'username', 'email'])->from('users')
    ->leftJoin('user_info', ['users.id' => 'user_info.user_id'])
    ->where('id < :id')
    ->orderBy('id', 'DESC');

echo $sql;

$sql->select()
    ->from('users')
    ->where('id > :id')->andWhere('email LIKE :email');

echo $sql;

$sql->select()
    ->from('users')
    ->where('id > :id')->orWhere('email LIKE :email');

echo $sql;

$sql->select()
    ->from('users')
    ->where->greaterThan('id', ':id')->and()->equalTo('email', ':email');

echo $sql;

$sql->select()
    ->from('users')
    ->where->greaterThan('id', ':id')
        ->nest()->greaterThan('logins', ':logins')
            ->or()->lessThanOrEqualTo('failed', ':failed');

echo $sql;

$db = Pop\Db\Db::mysqlConnect($options);

$schema = $db->createSchema();
$schema->create('users')
    ->int('id', 16)->increment()
    ->varchar('username', 255)
    ->varchar('password', 255)
    ->primary('id');

echo $schema;

$schema->create('user_info')
    ->int('user_id', 16)
    ->varchar('email', 255)
    ->varchar('phone', 255)
    ->foreignKey('user_id')->references('users')->on('id')->onDelete('CASCADE');

$schema->alter('users')
    ->varchar('email', 255)
    ->after('password');

echo $schema;

$schema->alter('users')
    ->addColumn('email', 'VARCHAR', 255)
    ->after('password');

echo $schema;

$schema->drop('users');

echo $schema;

$schema->execute();

$createTable   = $schema->create('users');
$alterTable    = $schema->alter('users');
$truncateTable = $schema->truncate('users');
$renameTable   = $schema->rename('users');
$dropTable     = $schema->drop('users');

use Pop\Db\Sql\Migrator;

Migrator::create('MyNewMigration', __DIR__ . 'migrations');



use Pop\Db\Sql\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{

    public function up()
    {

    }

    public function down()
    {

    }

}



use Pop\Db\Sql\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{

    public function up()
    {
        $schema = $this->db->createSchema();
        $schema->create('users')
            ->int('id', 16)->increment()
            ->varchar('username', 255)
            ->varchar('password', 255)
            ->primary('id');

        $schema->execute();
    }

    public function down()
    {
        $schema = $this->db->createSchema();
        $schema->drop('users');
        $schema->execute();
    }

}

use Pop\Db\Db;
use Pop\Db\Sql\Migrator;

$db = Pop\Db\Db::connect('mysql', [
    'database' => 'my_database',
    'username' => 'my_db_user',
    'password' => 'my_db_password',
    'host'     => 'mydb.server.com'
]);

$migrator = new Migrator($db, 'migrations');
$migrator->run();

use Pop\Db\Db;
use Pop\Db\Sql\Migrator;

$db = Pop\Db\Db::connect('mysql', [
    'database' => 'my_database',
    'username' => 'my_db_user',
    'password' => 'my_db_password',
    'host'     => 'mydb.server.com'
]);

$migrator = new Migrator($db, 'migrations');
$migrator->rollback();

use Pop\Db\Sql\Seeder;

Seeder::create('MyFirstSeeder', __DIR__ . '/seeds');



use Pop\Db\Adapter\AbstractAdapter;
use Pop\Db\Sql\Seeder\AbstractSeeder;

class MyFirstSeeder extends AbstractSeeder
{

    public function run(AbstractAdapter $db): void
    {

    }

}



use Pop\Db\Adapter\AbstractAdapter;
use Pop\Db\Sql\Seeder\AbstractSeeder;

class MyFirstSeeder extends AbstractSeeder
{

    public function run(AbstractAdapter $db): void
    {
        $schema = $db->createSchema();
        $schema->create('users')
            ->int('id', 16)->notNullable()->increment()
            ->varchar('username', 255)->notNullable()
            ->varchar('password', 255)->notNullable()
            ->varchar('email', 255)->nullable()
            ->primary('id');

        $db->query($schema);

        $sql = $db->createSql();
        $sql->insert('users')->values([
            'username' => 'testuser1',
            'password' => '12345678',
            'email'    => '[email protected]'
        ]);
        $db->query($sql);

        $sql->insert('users')->values([
            'username' => 'testuser2',
            'password' => '87654321',
            'email'    => '[email protected]'
        ]);
        $db->query($sql);

        $sql->insert('users')->values([
            'username' => 'testuser3',
            'password' => '74185296',
            'email'    => '[email protected]'
        ]);
        $db->query($sql);
    }

}

$db = Db::mysqlConnect([
    'database' => 'DATABASE',
    'username' => 'DB_USER',
    'password' => 'DB_PASS'
]);

Seeder::run($db, __DIR__ . '/seeds');
sql
-- MySQL
SELECT * FROM `users` WHERE ((`id` > ?) AND (`email` LIKE ?))
sql
-- MySQL
SELECT * FROM `users` WHERE ((`id` > ?) OR (`email` LIKE ?))
sql
-- MySQL
SELECT * FROM `users` WHERE ((`id` > ?) AND (`email` = ?))
sql
-- MySQL
SELECT * FROM `users` WHERE ((`id` > ?) AND ((`logins` > ?) OR (`failed` <= ?)))
text
Start:			1699246221.25475
Finish:			0.00000
Elapsed:		0.00997 seconds

Queries:
--------
INSERT INTO `users` (`username`, `password`, `email`) VALUES (?, ?, ?) [0.00674]
Start:			1699246221.25796
Finish:			1699246221.26470
Params:
	username => admin
	password => password
	email => [email protected]