dframe/database

Database pdo wrapper

v2.0.1 2020-11-16 11:10 UTC

This package is auto-updated.

Last update: 2022-11-27 12:33:18 UTC


README

Build Status Latest Stable Version Total Downloads Latest Unstable Version License

Dframe Documentation

Installation Composer

$ composer require dframe/database

What's included?

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 1

Footnotes

  1. neerajsinghsonu/PDO_Class_Wrapper