dframe/database

Database pdo wrapper

v2.3.0 2023-12-07 11:44 UTC

Requires

• php: >=5.6
• ext-json: *
• ext-pdo: *

Requires (Dev)

• phpunit/phpunit: ^5.7 || ^6.5

MIT

• Sławomir Kaleta

• database
• mysql
• query
• pdo
• update
• query-builder
• pdowraper
• wraper

README

Dframe Documentation

Installation Composer

$ composer require dframe/database

What's included?

• MySQL PDO
• MySQL Query Builder
• MySQL WHERE Builder

Methods

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 ¹