PHP Classes

YSPDO: Manipulate table records with PDO using arrays

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStarStar 72%Total: 370 All time: 6,813 This week: 455Up
Version License PHP version Categories
yspdo 1.1.0MIT/X Consortium ...7Databases, PHP 7
Collaborate with this project 

Author

YSPDO - github.com

Description

This class can manipulate table records with PDO using arrays.

It can connect to a given database using PDO and perform several types of operations using a fluent interface. Currently it can:

- Execute prepared queries from parameter arrays
- Fetch query results into arrays
- Build SELECT, INSERT, UPDATE and DELETE queries from parameters that define tables, fields, values, conditions, etc..
- Create and drop databases
- Manage transactions

Picture of Gabriel Almeida
  Performance   Level  
Name: Gabriel Almeida <contact>
Classes: 4 packages by
Country: Brazil Brazil
Age: 26
All time rank: 2189146 in Brazil Brazil
Week rank: 170 Up12 in Brazil Brazil Up

Example

<?php


include __DIR__."/../dist/YSPDO.php";


$db = new YSPDO([
 
'mysql',
 
'host' => 'localhost',
 
'dbname' => 'generatedata',
 
'port' => 3306,
 
'charset' => 'utf8',
],
'root','');



$table = 'peoples';


// Selection all columns
//
// $query = $db->select( $table )->fetchAll();


// Selecting columns
//
$query = $db->select( $table ,['name','email','phone'])->fetchAll();


// Fetch
//
// $query = $db->select( $table ,'*',[
// 'email' => 'commodo@sem.edu'
// ])->fetch();

// Fetch all
//
// $query = $db->select( $table )->fetchAll();
//
// Fetch, with type of return
//
// $query = $db->select( $table ,'*',[
// 'email' => 'commodo@sem.edu'
// ])->fetch('OBJ');
//
// Fetch all, with type of return
//
// $query = $db->select( $table )->fetchAll('OBJ');


// DISTINCT
//
// $query = $db->select( $table ,[
// // 'DISTINCT' => 'name'
// // OR
// // 'DISTINCT' => ['name','phone','date']
// ])->fetchAll();


// ALIASES
//
// $query = $db->select( $table ,[
// 'AS' => [
// 'name' => 'yourName',
// 'date' => 'birthday',
// 'email' => 'contact'
// ]
// ])->fetchAll();


// Operators
//
// $query = $db->select( $table ,'*',[
// 'id{>=}' => 100
// ])->fetchAll();



// Row count
//
// $query = $db->select( $table ,'*',[
// 'email' => 'dolor.sit@ametdiam.ca'
// ])->rowCount();


// ORDER BY
//
// $query = $db->select( $table ,'*',[
// // 'ORDER' => 'city'
// // OR
// // 'ORDER' => 'name DESC'
// // OR
// // 'ORDER' => ['name DESC','city ASC']
// ])->fetchAll();


// NOT BETWEEN
//
// $query = $db->select( $table ,'*',[
// '!BETWEEN' => [
// 'id' => [1,275]
// // OR
// // 'name' => ['a','b']
// ]
// ])->fetchAll();


// BETWEEN
//
// $query = $db->select( $table ,'*',[
// 'BETWEEN' => [
// // 'id' => [1,25]
// // OR
// // 'name' => ['a','b']
// ]
// ])->fetchAll();


// NOT LIKE
//
// $query = $db->select( $table ,'*',[
// '!LIKE' => [
// 'name' => 'g%'
// ]
// ])->fetchAll();

// LIKE
//
// $query = $db->select( $table ,'*',[
// 'LIKE' => [
// 'city' => '%es%'
// ]
// ])->fetchAll();


// LIMIT
//
// $query = $db->select( $table ,'*',[
// 'LIMIT' => 10
// ])->fetchAll();


// IN
//
// $query = $db->select( $table ,'*',[
// 'IN' => [
// 'city' => ['Acoz','Pietraroja','Martelange','Relegem']
// ]
// ])->fetchAll();

// NOT IN
//
// $query = $db->select( $table ,'*',[
// '!IN' => [
// 'city' => ['Acoz','Pietraroja','Martelange','Relegem']
// ]
// ])->fetchAll();



// IN | BETWEEN | NOT LIKE | ORDER BY
//
// $query = $db->select( $table ,'*',[
// 'IN' => [
// 'city' => ['Zeist','Stafford','Remagne','Istres','Warwick','Urbe','Chatillon','Rawalpindi']
// ],
// 'BETWEEN' => [
// 'id' => [1,250]
// ],
// '!LIKE' => [
// 'city' => 's%'
// ],
// 'ORDER' => 'city ASC'
// ])->fetchAll();


// CREATE DATABASE
//
// $query = $db->createDB('dbname');


// DELETE DATABASE
//
// $query = $db->deleteDB('dbname');


// INSERT INTO
//
// $query = $db->insert( $table ,[
//
// 'id' => 301,
// 'name' => 'Gabriel',
// 'phone' => '+0000000000',
// 'date' => '00/00/0000',
// 'email' => 'name@test.com',
// 'address' => 'Street teste address nš 1000 Av test',
// 'city' => 'Araguaina',
// 'country' => 'Brazil',
// 'company' => 'No Company',
//
// ]);


// UPDATE
//
// $query = $db->update( $table ,[
//
// 'phone' => '+1111111111',
// 'date' => '11/11/1111',
// 'email' => 'name@server.co',
// 'address' => 'Street teste address nš 1000 Av test'
//
// ],[ 'id' => 301 ]);


// DELETE
//
// $query = $db->delete( $table ,[
// 'id' => 301
// ]);
//
// Delete all data (Use ->rowCount() to know how many rows have been affected)
// $query = $db->delete( $table );
// OR
// $query = $db->delete( $table , '*' );


// COUNT
//
// $query = $db->count( $table, '*', [
// // 'LIKE' => [
// // 'address' => '%Avenue%'
// // ]
// //
// // 'id{>}' => 275
// ]);
//
// var_dump( $query );


// DELETE TABLE
//
// $query = $db->deleteTable( $table );


// TRUNCATE TABLE
//
// $query = $db->truncate( $table );


print_r( $query );


Details

YSPDO

Class in PHP YSPDO is a PDO helper to manipulate dynamically database records using arrays

Initialize class

$yspdo = new YSPDO([
  'mysql',  // Driver
  'host'    => 'localhost',
  'port'    => 3306,
  'dbname'  => 'generatedata'
],'root','');

CREATE | READ | UPDATE | DELETE

CREATE

$stmt = $yspdo->insert('peoples',[
  'name'    => 'Gabriel Almeida',
  'email'   => 'gabrieel@email.com',
  'country' => 'Brazil'
]);

echo $stmt->lastInsertId();

READ

$stmt = $yspdo->select('peoples',['name','email','address','company'],[
  'id{>=}' => 100
]);

print_r( $stmt->fetchAll('COLUMN') );

UPDATE

$stmt = $yspdo->update('peoples',[
  'phone' => '(00) 0000-0000',
  'address' => '4129 Magna. Avenue',
],[
  'id' => 100
]);

DELETE

$stmt = $yspdo->delete('peoples',[
  'id' => 100
]);

FUNCTION SELECT

fetch

$yspdo->select('peoples','*',[
    'email' => 'commodo@sem.edu'
])->fetch();

fetchAll

$yspdo->select('peoples')->fetchAll();

fetch setting type of return

$yspdo->select('peoples','*',[
    'email' => 'commodo@sem.edu'
])->fetch('OBJ');

fetch all, defining type of return

$yspdo->select('peoples')->fetchAll('OBJ');

Selecting columns

$yspdo->select('peoples',['name','email','phone'])->fetchAll();

DISTINCT

$yspdo->select('peoples',[
    'DISTINCT' => 'name'
    // OR
    'DISTINCT' => ['name','phone','date']
])->fetchAll();

ALIASES

$yspdo->select('peoples',[
    'AS' => [
        'name' => 'yourName',
        'date' => 'birthday',
        'email' => 'contact'
    ]
])->fetchAll();

Operators

$yspdo->select('peoples','*',[
    'id{>=}' => 100
])->fetchAll();

rowCount

$yspdo->select('peoples','*',[
    'email' => 'dolor.sit@ametdiam.ca'
])->rowCount();

ORDER BY

$yspdo->select('peoples','*',[
    'ORDER' => 'city'
    // OR
    'ORDER' => 'name DESC'
    // OR
    'ORDER' => ['name DESC','city ASC']
])->fetchAll();

BETWEEN

$yspdo->select('peoples','*',[
    'BETWEEN' => [
        'id' => [1,25]
        // OR
        'name' => ['a','b']
    ]
])->fetchAll();

NOT BETWEEN

$yspdo->select('peoples','*',[
    '!BETWEEN' => [
        'id' => [1,25]
        // OR
        'name' => ['a','b']
    ]
])->fetchAll();

LIKE

$yspdo->select('peoples','*',[
    'LIKE' => [
        'name' => 'g%'
    ]
])->fetchAll();

NOT LIKE

$yspdo->select('peoples','*',[
    '!LIKE' => [
        'name' => 'g%'
    ]
])->fetchAll();

LIMIT

$yspdo->select('peoples','*',[
    'LIMIT' => 10
])->fetchAll()

IN

$yspdo->select('peoples','*',[
    'IN' => [
        'city' => ['Acoz','Pietraroja','Martelange','Relegem']
    ]
])->fetchAll();

NOT IN

$yspdo->select('peoples','*',[
    '!IN' => [
        'city' => ['Acoz','Pietraroja','Martelange','Relegem']
    ]
])->fetchAll();

Using IN | BETWEEN | NOT LIKE | ORDER BY

$yspdo->select('peoples','*',[
    'IN' => [
        'date' => ['02/11/1985','11/27/1997','09/24/1969','01/15/1985','09/12/1986']
    ],
    'BETWEEN' => [
        'id' => [1,50]
    ],
    '!LIKE' => [
        'city' => 's%'
    ],
    'ORDER' => 'city ASC'
])->fetchAll();

CREATE DATABASE

$yspdo->createDB('dbname');

DELETE DATABASE

$yspdo->deleteDB('dbname');

INSERT INTO

$yspdo->insert('peoples',[
    'id'        => 301,
    'name'      => 'Gabriel',
    'phone'     => '+0000000000',
    'date'      => '00/00/0000',
    'email'     => 'name@test.com',
    'address'   => 'Street teste address nš 1000 Av test',
    'city'      => 'an_city',
    'country'   => 'Brazil',
    'company'   => 'No Company',
]);

UPDATE

$yspdo->update('peoples',[
    'phone' => '+1111111111',
    'date' => '11/11/1111',
    'email' => 'name@server.co',
    'address' => 'Street teste address nš 1000 Av test'
],[ 'id' => 301 ]);

DELETE

$yspdo->delete('peoples',[
    'id' => 301
]);

COUNT

$yspdo->count('peoples','*',[
    'id{>}' => 125
]);

DELETE TABLE

$yspdo->deleteTable('peoples');

TRUNCATE TABLE

$yspdo->truncate('peoples');

*

Attention

Do not use the (select|insert|update|delete|count|prepare) functions like this:

$statement1 = $yspdo->(select|insert|update|delete|count|prepare)('table_one' ...);
$statement2 = $yspdo->(select|insert|update|delete|count|prepare)('table_two' ...);

print_r( $statement1->fetchAll() );
print_r( $statement2->fetchAll() );

// YSPDO will replace the query from $statement1 with $statement2

*

Available Functions

/
* PDO::getAvailableDrivers
*
* @return array
*/
->getAvailableDrivers()
/
* PDO::commit
*
* @return boolean
*/
->commit()
/
* PDO::beginTransaction
*
* @return boolean
*/
->beginTransaction()
/
* PDO::rollBack
*
* @return boolean
*/
->rollBack()
/
* PDO::inTransaction
*
* @return boolean
*/
->inTransaction()
/
* PDO::exec
*
* @param string $statement
* @return integer|boolean
*/
->exec( string $statement )
/
* PDO::quote
*
* @param string $string
* @param int $parameter_type
* @return string
*/
->quote($string [, $parameter_type=\PDO::PARAM_STR])
/
* PDO::errorCode
*
* @return mixed
*/
->errorCode()
/
* PDO::errorInfo
*
* @return array
*/
->errorInfo()
/
* Query Statement
*
* @param string $sql
* @param array $parameters
* @return YSPDO
*/
->query(string $sql [, array $parameters=[]])
/
* Row count
*
* @return integer
*/
->rowCount()
/
* Last insert id
*
* @return integer
*/
->lastInsertId()
/
* Prepare Statement
*
* @param string $sql
* @return YSPDO
*/
->prepare(string $sql)
/
* bindColumn Statement
*
* @param mixed $column
* @param mixed $param
* @param int $type
* @param int $maxlen
* @param mixed $driverdata
* @return YSPDO
*/
->bindColumn(mixed $column , mixed &$param [, int $type [, int $maxlen [, mixed $driverdata ]]])
/
* bindParam Statement
*
* @param mixed $parameter
* @param mixed $value
* @param int $type
* @param int $length
* @return YSPDO
*/
->bindParam(mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]])
/
* bindValue Statement
*
* @param mixed $parameter
* @param mixed $value
* @param int $type
* @return YSPDO
*/
->bindValue(mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ])
/
* closeCursor Statement
*
* @return boolean
*/
->closeCursor()
/
* columnCount Statement
*
* @return int
*/
->columnCount()
/
* execure Statement
*
* @param array $parameters
* @return boolean
*/
->execute([array $parameters=[]])
/
* fetch Statement
*
* @param string $style
* @param int $cursor_orientation
* @param int $offset
* @return mixed
*/
->fetch([ string $style [, int $cursor_orientation = PDO::FETCH_ORI_NEXT [, int $cursor_offset = 0 ]]])
/
* fetchAll Statement
*
* @param string $style
* @param mixed $argument
* @param array $ctor_args
* @return mixed
*/
->fetchAll([ string $style [, mixed $argument = \PDO::FETCH_COLUMN [, array $ctor_args = [] ]]])
/
* fetchColumn Statement
*
* @param int $column_number
* @return midex
*/
->fetchColumn([int $column_number = 0])
/
* fetchObject Statement
*
* @param string $class_name
* @param array $ctor_args
* @return midex
*/
->fetchObject([ string $class_name = "stdClass" [, array $ctor_args = [] ]])
/
* getAttribute Statement
*
* @param int $attribute
* @return mixed
*/
->getAttribute(int $attribute)
/
* setAttribute Statement
*
* @param int $attribute
* @param mixed $value
* @return boolean
*/
->setAttribute(int $attribute , mixed $value)
/
* SELECT
*
* @param string       $table
* @param string|array $columns
* @param array|null   $where
* @return YSPDO
*/
->select(string $table [,$columns='*' [,$where=null]])
/
* INSERT
*
* @param string $table
* @param array  $data
* @return YSPDO
*/
->insert(string $table, array $data)
/
* UPDATE
*
* @param string $table
* @param array  $data
* @param array|null  $where
* @return YSPDO
*/
->update(string $table, array $data [, $where=null])
/
* DELETE
*
* @param string $table
* @param array  $where
* @return YSPDO
*/
->delete(string $table [, array $where=''])
/
*  COUNT
*
* @param string $table
* @param string|array $columns
* @param array $where
* @return string
*/
->count(string $table [, $columns = '*' [, array $where = []]])
/
* Create Database
*
* @param string $s Database name
* @return boolean
*/
->createDB(string $s)
/
* Drop Database
*
* @param string $s Database name
* @return boolean
*/
->deleteDB(string $s)
/
* Drop Table
*
* @param string $s Table name
* @return boolean
*/
->deleteTable(string $s)
/
* Truncate Table
*
* @param string $s Table name
* @return boolean
*/
->truncate(string $s)

*

REQUIREMENTS

PHP Version 7.x or newer

LICENCE

MIT


  Files folder image Files (8)  
File Role Description
Files folder imagedist (1 file)
Files folder imageexamples (2 files)
Files folder imagesrc (1 file)
Accessible without login Plain text file CHANGELOG.md Doc. Documentation
Accessible without login Plain text file CNAME Data Auxiliary data
Accessible without login Plain text file LICENCE.txt Lic. License
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (8)  /  dist  
File Role Description
  Plain text file YSPDO.php Class Class source

  Files folder image Files (8)  /  examples  
File Role Description
  Accessible without login Plain text file basic.php Example Example script
  Accessible without login Plain text file connections.php Aux. Auxiliary script

  Files folder image Files (8)  /  src  
File Role Description
  Accessible without login Plain text file peoples.sql Data Data and Structure used in examples.

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:370
This week:0
All time:6,813
This week:455Up
 User Ratings  
 
 All time
Utility:91%StarStarStarStarStar
Consistency:83%StarStarStarStarStar
Documentation:91%StarStarStarStarStar
Examples:91%StarStarStarStarStar
Tests:-
Videos:-
Overall:72%StarStarStarStar
Rank:183