PHP code example of mreschke / dbal

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

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

    

mreschke / dbal example snippets

use Mreschke\Dbal\Builder;
use Mreschke\Dbal\Mssql as Dbal;

class User extends Builder
{
    /**
     * Database connection
     * @var Dbal
     */
    protected $db;

    /**
     * Create a new User instance
     * @param Dbal $db
     */
    public function __construct(Dbal $db)
    {
        $this->db = $db;
        $this->db->connection('sso');
        $this->configureBuilder();
    }

    /**
     * Configure the dbal query builder
     * @return void
     */
    public function configureBuilder()
    {
        $this->key = 'user_id';
        $this->select = ['tbl_user.*'];
        $this->from = 'tbl_user';
        $this->where = null;
        $this->groupBy = null;
        $this->having = null;
        $this->orderBy = 'email';
    }

    /**
     * Return the dbal database instance
     * @return DbalInterface
     */
    public function dbInstance() {
        return $this->db;
    }

    /**
     * Get one user by email address
     * Automatically appends @dynatronsoftware.com if no domain specified
     * @param  string $email
     * @return dbal resource
     */
    public function byEmail($email)
    {
        if (!str_contains($email, "@")) $email .= "@dynatronsoftware.com";
        $this->where("email = '$email'");
        $this->orderBy(null);
        return $this->execute();
    }

    /**
     * Get dealer server manager(s)
     * @param  int $dealerID dp dlr id
     * @return dbal resource
     */
    public function serviceManagers($dealerID)
    {
        $this->from('tbl_user
            INNER JOIN tbl_perm_group_link pgl on tbl_user.user_id = pgl.user_id
            INNER JOIN tbl_user_dealer_link udl on tbl_user.user_id = udl.user_id
        ');
        $this->where('tbl_user.disabled', false);
        $this->where('udl.dp_dlr_id', $dealerID);
        $this->where('pgl.group_id', 10); #10 = Service Manager
        $this->distinct();
        return $this->execute();
    }
}

use Mreschke\Dbal\Mssql;

function __construct(Mssql $db)
{
    // Dependency injection.  Facades are also available.
    $this->db = $db;
}

function rawQueries()
{
    // Get all as collection of objects
    $customers = $this->db->query("SELECT * FROM customers")->get(); // or all()

    // Get all as collection of arrays
    $customers = $this->db->query("SELECT * FROM customers")->getArray(); // or getAssoc()

    // Get all as key/value array
    $customers = $this->db->query("SELECT * FROM customers")->pluck('name', 'id');

    // Get first record as object
    $customers = $this->db->query("SELECT * FROM customers")->first();

    // Get first record as array
    $customers = $this->db->query("SELECT * FROM customers")->firstArray(); // or firstAssoc()

    // Get first column from first record (great for scalar queries)
    $customers = $this->db->query("SELECT TOP 1 name FROM customers")->pluck();

    // Get defined column from first record
    $customers = $this->db->query("SELECT TOP 1 * FROM customers")->value('adddress');

    // Count number of results
    // NOTICE: This will actually RUN the full query, so inefficient...
    // A SELECT count(*) is far more efficient.
    // So if you want the results too, get results, then count them yourself
    $count = $this->db->query("SELECT * FROM customers")->count(); // runs full query
    $count = $this->db->query("SELECT count(*) FROM customers")->value(); // db level, very efficient
    $customers = $this->db->query("SELECT * FROM customers")->get();
    count($customers) //or because collection, $customers->count();

    // Count number of columns
    $columnCount = $this->db->query("SELECT * FROM customers")->fieldCount();

    // Escape data for raw input
    $input = $this-db->escape($input);
    $this->db->query("INSERT INTO customers $input");
}

function queryBuilder()
{
    // Query build table does NOT work with databsae or schema names like DB.dbo.my_table
    // Much like the raw ->query() function above, the terminators are ->get,
    // ->all(), ->getAssoc(), ->getArray(), ->first()...
    // Most query builder methods allow RAW entries too, like ->where('raw = this or raw = that')...

    // TABLE and SELECT
        // Build can do basic ->table(), ->select() and ->addSelect().  If you want
        // complex joins, use a full RAW ->query() or add RAW to the ->table() method.
        // By default select is set to *

        // Get all records
        $customers = $this->db->table('customer')->get(); // or all()

        // Get all records, limited columns
        $customers = $this->db->select('id', 'name')->table('customer')->get();

        // Select as RAW, either as one parameter per column, or as one big string
        $customers = $this->db->select('id as UID', 'name as Customer')->table('customer')->get();
        $customers = $this->db->select('id as UID, name as Customer')->table('customer')->get();

        // Complex Raw table and select, but still using build style, not pure ->query()
        $customers = $this->db->select('c.*, r.name as Role')
            ->table('customer c INNER JOIN roles r on c.role_id = r.role_id');
        $customers->addSelect('r.ID');
        $customers->distinct();
        $customers = $customers->get();


    // WHERE, ORDER GROUP, HAVING
        // By default, the ->where() method has 2 params, and the = opreator is assumed
        // But like eloquent, you can override the operator ->where('name', 'like', 'bob')...
        // Chaining ->where() is by default AND...but you can alter to OR...but it won't
        // do compled nested AND/OR combinations...for that I just use RAW queries.

        // Get one with WHERE statement
        $customers = $this->db->table('customer')->where('name', 'Bob')->first();

        // Multiple wheres (AND)
        $customers = $this->db->table('customer')->where('zip', 75067)->where('disabled', false)->first();

        // Multiple wheres (OR)
        $customers = $this->db->table('customer')->where('zip', '=', 75067, 'or')->where('zip', 75068)->get();
        $customers = $this->db->table('customer')->where('zip', '=', 75067)->orWhere('zip', 75068)->get();

        // Mixed in RAW where
        $customers = $this->db->table('customer')->where('(zip = 1 or zip = 2)')->where('disabled', false)->get();

        // Mixed RAW complex
        $customers = $this->db
            ->table('customer')
            ->select('name', 'count(*) as cnt')
            ->where('(x = y AND a = b) OR (c = d)')
            ->groupBy('name')
            ->orderBy('cnt desc')
            ->having('cnt > 1')

}

function procedures()
{
    // No params
    $customers = $this->db->procedure('GetAllCustomers')->get()

    // Params
    $customers = $this->db->procedure('GetCustomersByState', [
        ['name' => 'state', 'value' => 'TX'],
        ['name' => 'zip', 'value' => 75067],
    ]);

    // No return
    $this->db->procedure('DeleteAllCustomers');
}