panada/database

A standalone Panada database package

About Panada Database Package

This is A standalone Panada database package., _(*1)

Use as Standalone Package

Install via composer, _(*2)

composer require panada/database

<?php

require_once __DIR__.'/vendor/autoload.php';

$db = new Panada\Database\SQL([
        'dsn' => 'mysql:host=127.0.0.1;dbname=panada;port=3306',
        'username' => 'root',
        'password' => '',
        'options' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_PERSISTENT => true
        ]);

$insert = $db->insert('account', [
            'user_name' => 'foo',
            'email' => 'foo@bar.com',
        ]);

Use as Panada Package

<?php

namespace Controller;

class Hello
{
    use \Panada\Resource\Controller;

    public function index()
    {
        $this->db = \Panada\Database\SQL::getInstance();

        $query = $this->db->insert('users', [
            'name' => rand(), 'email' => 'budi@budi.com', 'password' => 'password'
        ]);

        $data = $this->db->select()->from('users')->getAll();

        return 'status insert: '.var_export($query, true).' data: <pre>'.print_r($data, true).'</pre>';
    }
}

If you have more then one db connection, here's the example:, _(*3)

<?php

return [
    'default' => [
        'dsn' => 'mysql:host=127.0.0.1;dbname=mydb1;port=3306',
        'username' => 'root',
        'password' => '',
        'options' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_PERSISTENT => true
        ]
    ],
    'db2' => [
        'dsn' => 'mysql:host=127.0.0.1;dbname=mydb2;port=3307',
        'username' => 'root',
        'password' => '',
        'options' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::ATTR_PERSISTENT => true
        ]
    ],
    'db3' => [
        'dsn' => 'mysql:host=127.0.0.1;dbname=mydb3;port=3308',
        'username' => 'root',
        'password' => '',
        'options' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf9',
            PDO::ATTR_PERSISTENT => true
        ]
    ]
];

Call the db helper:, _(*4)

public function testDB()
{
    $db1 = \Panada\Database\SQL::getInstance();
    $db2 = \Panada\Database\SQL::getInstance('db2');
    $db3 = \Panada\Database\SQL::getInstance('db3');
}

APIs

Insert

$query = $this->db->insert('tableName', ['name' => 'jhon', 'email' => 'foo@bar.com']);

SQL output:, _(*5)

INSERT INTO tableName (name, email) VALUES ('jhon', 'foo@bar.com')

Update

$query = $this->db->update('tableName', ['name' => 'jhon gmail', 'email' => 'jhon@gmail.com'], ['id' => 6]);

SQL output:, _(*6)

UPDATE tableName SET name = 'budi', email = 'budi@budi.com' WHERE id = 6

Select

$data = $this->db->select()->from('users')->getAll();

SQL output:, _(*7)

SELECT * FROM users

$data = $this->db->select('id', 'name')->from('users')->getAll();
// or
$data = $this->db->select(['id', 'name'])->from('users')->getAll();

SQL output:, _(*8)

SELECT id, name FROM users

Select with SQL built in function

$data = $this->db->select('COUNT(*)')->from('users')->getVar();

SQL output:, _(*9)

SELECT COUNT(*) FROM users

Distinct

$data = $this->db->select('name')->distinct()->from('users')->limit(10)->getAll();

SQL output:, _(*10)

SELECT DISTINCT name FROM users LIMIT 10

Examples

Heres an example to insert then fatch some db data:, _(*11)

public function testDB()
{
    $this->db = \Panada\Database\SQL::getInstance();

    $query = $this->db->insert('users', [
        'name' => rand(), 'email' => 'budi@budi.com', 'password' => 'password'
    ]);

    $data = $this->db->select()->from('users')->getAll();

    return 'status insert: '.var_export($query, true).' data: <pre>'.print_r($data, true).'</pre>';
}

Run the Test

Go to project root. Run composer install to get PHPUnit Package., _(*12)

composer install

Then run the test:, _(*13)

./vendor/bin/phpunit