Dframe/Database

Dframe Documentation

Installation Composer

$ composer require dframe/database

What's included?

• MySQL PDO
• MySQL Query Builder
• MySQL WHERE Builder

Methods

Description	name
MySQL query	pdoQuery()
MySQL select query	select()
MySQL insert query	insert()
MySQL insert batch	insertBatch()
MySQL update query	update()
MySQL delete query	delete()
MySQL truncate table	truncate()
MySQL drop table	drop()
MySQL describe table	describe()
MySQL count records	count()
Show/debug executed query	showQuery()
Get last insert id	getLastInsertId()
Get all last insert id	getAllLastInsertId()
Get MySQL results	results()
Get MySQL result	result()
Get status of executed query	affectedRows()
MySQL begin transactions	start()
MySQL commit the transaction	end()
MySQL rollback the transaction	back()
Debugger PDO Error	setErrorLog()

Init Connection

<?php 
use Dframe\Database\Database;
use \PDO;

try {

    
    // Debug Config 
    $config = [
        'logDir' => APP_DIR . 'View/logs/',
        'attributes' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", 
            //PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,  // Set pdo error mode silent
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // If you want to Show Class exceptions on Screen, Uncomment below code 
            PDO::ATTR_EMULATE_PREPARES => true, // Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). 
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // Set default pdo fetch mode as fetch assoc
         ]
    ];
    
    $dsn = [
        'host' => DB_HOST,
        'dbname' => DB_DATABASE,
        'dbtype' => 'mysql'
    ];
        
    $db = new Database($dsn, DB_USER, DB_PASS, $config);
    $db->setErrorLog(false); // Debug
    
}catch(\Exception $e) {
    echo 'The connect can not create: ' . $e->getMessage(); 
    exit();
}

OR

<?php 
use Dframe\Database\Database;
use \PDO;

try {

    
    // Debug Config 
    $config = [
        'log_dir' => APP_DIR . 'View/logs/',
        'attributes' => [
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", 
            //PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,  // Set pdo error mode silent
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // If you want to Show Class exceptions on Screen, Uncomment below code 
            PDO::ATTR_EMULATE_PREPARES => true, // Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). 
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // Set default pdo fetch mode as fetch assoc
         ]
    ];
    
    $db = new Database('mysql:host='.DB_HOST.';dbname=' . DB_DATABASE . ';port=3306', DB_USER, DB_PASS, $config);
    $db->setErrorLog(false); // Debug
    
}catch(\Exception $e) {
    echo 'The connect can not create: ' . $e->getMessage(); 
    exit();
}

Example - pdoQuery

Return first element array;

$result = $db->pdoQuery('SELECT * FROM table WHERE id = ?', [$id])->result();

Note: result() will select all rows in database, so if you want select only 1 row i query connection add LIMIT 1;

---

Return all result array query;

$results = $db->pdoQuery('SELECT * FROM table')->results();

Update;

$affectedRows = $db->pdoQuery('UPDATE table SET col_one = ?, col_two = ?', [$col_one, $col_two])->affectedRows();

Note: affectedRows() will return numbers modified rows;

Insert;

 
$getLastInsertId = $db->pdoQuery('INSERT INTO table (col_one, col_two) VALUES (?,?)', [$col_one, $col_two])->getLastInsertId();

Note: getLastInsertId() will return insert ID;

---

WhereChunk

Return all search result array query;

$where[] = new Dframe\Database\WhereChunk('col_id', '1'); // col_id = 1

WhereStringChunk

Return search result array query;

$where[] = new Dframe\Database\WhereStringChunk('col_id > ?', ['1']); // col_id > 1

Query builder

$query = $this->baseClass->db->prepareQuery('SELECT * FROM users');
$query->prepareWhere($where);
$query->prepareOrder('col_id', 'DESC');
$results = $this->baseClass->db->pdoQuery($query->getQuery(), $query->getParams())->results();

HavingStringChunk

$where[] = new Dframe\Database\HavingStringChunk('col_id > ?', ['1']); // col_id > 1

GroupInsertBatchHelper

/**
 * Multiple insert products with details in to tables
 */
$InsertBatchHelper = new InsertBatchHelper();

/**
* -----------------------------------------------------------------
* Prepare Product
* -----------------------------------------------------------------
*/
foreach ($data as $item) {
    
    $somePrimaryKey = md5($item->name.$item->price.$item->clientId);
    /** 
     *  First Query
     */    
    $Field = $InsertBatchHelper
        ->addRequireFields(
            [
                'id' => $somePrimaryKey,
                'name' => $item->name,
            ]
        )
        ->addField('client_id', $item->clientId, true)
        ->isCondition('available', 1, false);
    
    /**
     * Generate query string without params
     */
    $InsertBatchHelper->prepareInsert('products', $Field->getValues(), $Field->getColsForUpdate());

   /** 
    *  Second Query
    */
    $Field = $InsertBatchHelper
        ->addRequireFields(
            [
                'id' => $somePrimaryKey,
                'size' => $item->size,
                'price' => $item->price,
            ]
        )
        ->addField('client_id', $item->clientId, true)
        ->isCondition('available', 1, false);
    
    /**
     * Generate query string without params
     */
    $InsertBatchHelper->prepareInsert('products_details', $Field->getValues(), $Field->getColsForUpdate());
    
}

/** 
 * Get first and second query  
 */
$getQueriesBatchInsert = $InsertBatchHelper->getQueriesBatchInsert();

/**
 * Generate query for first and second query with params and run query
 */
foreach ($getQueriesBatchInsert as $sql => $queryBatchInsert) {

    $sqlProduct = $queryBatchInsert['sql'];
    $valuesProduct = $queryBatchInsert['data'];
    $updateColsProduct = $queryBatchInsert['updateCols'];
    
    $query = $this->baseClass->prepareBatchInsert($sqlProduct, $valuesProduct, $updateColsProduct);
    $results = $this->baseClass->db->pdoQuery($query->getQuery(), $query->getParams())->results();
 
}

Original author

neerajsinghsonu/PDO_Class_Wrapper ¹

Footnotes

1. neerajsinghsonu/PDO_Class_Wrapper ↩