Dframe/Database
, (*1)
Dframe Documentation, (*2)
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, (*3)
<?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;, (*4)
$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;, (*5)
Return all result array query;, (*6)
$results = $db->pdoQuery('SELECT * FROM table')->results();
Update;, (*7)
$affectedRows = $db->pdoQuery('UPDATE table SET col_one = ?, col_two = ?', [$col_one, $col_two])->affectedRows();
Note: affectedRows() will return numbers modified rows;, (*8)
Insert;, (*9)
$getLastInsertId = $db->pdoQuery('INSERT INTO table (col_one, col_two) VALUES (?,?)', [$col_one, $col_two])->getLastInsertId();
Note: getLastInsertId() will return insert ID;, (*10)
WhereChunk
Return all search result array query;, (*11)
$where[] = new Dframe\Database\WhereChunk('col_id', '1'); // col_id = 1
WhereStringChunk
Return search result array query;, (*12)
$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 , (*13)
Wallogit.com
