PHP Classes

Dframe Database: Access a MySQL database using PDO

Recommend this page to a friend!
  Info   View files Example   View files View files (29)   DownloadInstall with Composer Download .zip   Reputation   Support forum (2)   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 216 This week: 1All time: 8,300 This week: 560Up
Version License PHP version Categories
dframe-database 2.1.3Custom (specified...7Databases, PHP 7
Description 

Author

This package can access a MySQL database using PDO.

It can connect to a given MySQL database server using given configuration parameters.

The package provides several classes to build and execute SQL queries by defining condition clauses and retrieving the results.

Picture of Slawomir Kaleta
  Performance   Level  
Name: Slawomir Kaleta <contact>
Classes: 15 packages by
Country: Poland Poland
Age: ???
All time rank: 233054 in Poland Poland
Week rank: 34 Up2 in Poland Poland Up
Innovation award
Innovation award
Nominee: 3x

Winner: 1x

Recommendations

PDO Class
I need a class to connect to my db (mysql). Now my code is a old

Example

<?php

// include pdo helper class to use common methods
require_once '../src/Helper/PDOHelper.php';
// include pdo class wrapper
require_once '../src/class.pdowrapper.php';

// database connection setings
$dbConfig = ['host' => 'localhost', 'dbname' => 'sampledb', 'username' => 'root', 'password' => ''];
// get instance of PDO Wrapper object
$db = new PdoWrapper($dbConfig);

// get instance of PDO Helper object
$helper = new PDOHelper();

// set error log mode true to show error on screen or false to log in log file
$db->setErrorLog(true);

/**
 * run simple mysql query.
 *
 * showQuery = display executed query
 * results = get array results
 */
$q = $db->pdoQuery('select * from customers limit 5;')->showQuery()->results();
// print array result
$helper->PA($q);

/**
 * run simple mysql query with where clause
 * pass where value as an parametrised array.
 *
 * ? presenting place holder here for where clause values
 */
$q = $db->pdoQuery('select * from customers where (customernumber = ? OR customernumber = ?) ;', [103, 119])->showQuery()->results();
// print array result
$helper->PA($q);

/**
 * run simple mysql query and get third row of array results.
 *
 * result(2) = will return 3rd row of array data
 */
$q = $db->pdoQuery('select * from customers;')->showQuery()->result(2);
// print array result
$helper->PA($q);

/**
 * run mysql select query with where clause and or using parametrise array param.
 */
$q = $db->pdoQuery('select * from customers where (customernumber = ? OR contactLastName = ?) ;', [112, 'Schmitt'])->showQuery()->results();
// print array result
$helper->PA($q);

/**
 * run mysql select query with where clause and or using parametrise array param.
 */
$innerJoinSql = 'select p.checknumber, p.amount, p.paymentdate, c.customernumber, c.customerName, c.contactLastName, c.contactFirstName, c.phone, c.addressLine1, c.addressLine2, c.city, c.state, c.postalCode, c.country from payments as p inner join customers as c on p.customernumber = c.customernumber order by p.amount desc limit 2;';

$q = $db->pdoQuery($innerJoinSql)->showQuery()->results();
// print array result
$helper->PA($q);


Details

Dframe/Database

Build Status Latest Stable Version Total Downloads Latest Unstable Version License

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

Original author

neerajsinghsonu/PDO_Class_Wrapper [^neerajsinghsonu/PDO_Class_Wrapper]

[^neerajsinghsonu/PDO_Class_Wrapper]: neerajsinghsonu/PDO_Class_Wrapper


  Files folder image Files  
File Role Description
Files folder image.github (1 file, 1 directory)
Files folder imagedocs (2 files)
Files folder imageexample (6 files, 1 directory)
Files folder imagesrc (5 files, 2 directories)
Files folder imagetests (4 files, 1 directory)
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Read me

  Files folder image Files  /  .github  
File Role Description
Files folder imageISSUE_TEMPLATE (2 files)
  Accessible without login Plain text file PULL_REQUEST_TEMPLATE.md Data Auxiliary data

  Files folder image Files  /  .github  /  ISSUE_TEMPLATE  
File Role Description
  Accessible without login Plain text file bug_report.md Data Auxiliary data
  Accessible without login Plain text file feature_request.md Data Auxiliary data

  Files folder image Files  /  docs  
File Role Description
  Accessible without login Plain text file PHP_PDO_Class_Wrapper.pdf Data Auxiliary data
  Accessible without login Plain text file README.markdown Doc. Documentation

  Files folder image Files  /  example  
File Role Description
Files folder imagesample_db (1 file)
  Accessible without login Plain text file delete_demo.php Example Example script
  Accessible without login Plain text file insert_demo.php Example Example script
  Accessible without login Plain text file query_demo.php Example Example script
  Accessible without login Plain text file result_demo.php Example Example script
  Accessible without login Plain text file select_demo.php Example Example script
  Accessible without login Plain text file update_demo.php Example Example script

  Files folder image Files  /  example  /  sample_db  
File Role Description
  Accessible without login Plain text file sampledb.sql Data Auxiliary data

  Files folder image Files  /  src  
File Role Description
Files folder imageChunk (1 file)
Files folder imageHelper (1 file)
  Plain text file Database.php Class Class source
  Plain text file HavingStringChunk.php Class Class source
  Plain text file PdoWrapper.php Class Class source
  Plain text file WhereChunk.php Class Class source
  Plain text file WhereStringChunk.php Class Class source

  Files folder image Files  /  src  /  Chunk  
File Role Description
  Plain text file ChunkInterface.php Class Class source

  Files folder image Files  /  src  /  Helper  
File Role Description
  Plain text file PDOHelper.php Class Class source

  Files folder image Files  /  tests  
File Role Description
Files folder imagedata (1 file)
  Accessible without login Plain text file Bootstrap.php Aux. Auxiliary script
  Plain text file ConnectionTest.php Class Class source
  Plain text file QueryTest.php Class Class source
  Plain text file TestSetUp.php Class Class source

  Files folder image Files  /  tests  /  data  
File Role Description
  Accessible without login Plain text file db.sql Data Auxiliary data

 Version Control Unique User Downloads Download Rankings  
 100%
Total:216
This week:1
All time:8,300
This week:560Up