PHP Classes
elePHPant
Icontem

PHP PDO database class: Access databases using PDO

Recommend this page to a friend!
  Info   View files Documentation   View files View files (38)   DownloadInstall with Composer Download .zip   Reputation   Support forum (4)   Blog (1)    
Last Updated Ratings Unique User Downloads Download Rankings
2017-06-05 (18 days ago) RSS 2.0 feedNot enough user ratingsTotal: 940 This week: 3All time: 3,704 This week: 255Up
Version License PHP version Categories
php-database-class 2.0.9GNU General Publi...5.3.0PHP 5, Databases
Description Author

This class can access databases using PDO.

It uses the singleton pattern to establish a single database connection to a given database using PDO and executes several types of operations to store and retrieve information in that database.

Currently it can execute execute arbitrary or action queries composed from parameters that are included in prepared statements.

The class can also composed and execute queries to INSERT, UPDATE, DELETE records, or get the first or last rows of a SELECT query.

  Performance   Level  
Name: mohammad anzawi <contact>
Classes: 2 packages by
Country: Jordan Jordan
Age: 2
All time rank: 19862 in Jordan Jordan
Week rank: 192 Up1 in Jordan Jordan Up

Details

PDO Database Class

A database class which uses the PDO extension. * Allows one connection with the database and deny duplicate connection, * this speeds up to use the database and reduces the load on the server. * supports many drivers (mysql, sqlite, PostgreSQL, mssql, sybase, Oracle Call Interface -oci-)

If you have any issue please open issue to fix it.

any suggestions would you like added or modified write to us at team@phptricks.org

install via composer

{
	"require" : 
	{
		"phptricks/database_class" : "dev-master"
	}
}

to use class :

very importanst note : in this relase we change namespace of pakage from PHPtricks\Database to PHPtricks\Orm

(config) :

  • go to (Config/database_config.php) file
  • config class as your project need

describe configuration :

- fetch : PDO Fetch Style By default, database results will be returned as instances of the PHP stdClass object. - default : Default Database Connection Name (driver) by default (mysql) - connections : Database Connections (drivers).

<<<<<< set database connection information..! >>>>>>

how to use :

step 1 :

- Include the class in your project

    <?php
    // if installation via composer
    include_once('vendor/autoload.php');

    // otherwise
    include_once('phptricks/vendor/autoload.php');

step 2 :

  • Create the instance (connect with database) `php use PHPtricks\Orm\Database; $db = Database::connect(); `

how it work (methods):

select($fields = ['*'], $last = false) :

very important (select, first, find, paginate) methods __return Collection object__ you can use ->results(); to convert to array or object as you config a "fetch"

- select all data from test table :

```php
$allData = $db->table('test')->select();

var_dump($allData);

// try 
var_dump($allData->results()); // but you cant use any more methods
```
  • select id, name, email for all users from users table `php $coustomFields = $db->table('users')->select(['id', 'name', 'email']);

    var_dump($coustomFields);

    // if configure to return object echo $coustomFields->name; echo $coustomFields->email;

    // if configure to return array echo $coustomFields['name']; echo $coustomFields['email'];

    // or you can foreach the returned values foreach($coustomFields as $fields) { // ... } `

  • select latest posts (select from last to first) `php $latestPosts = $db->table('posts')->select(true);

    // or

    $latestPosts = $db->table('posts')->select(['title', 'content', 'publish_date'], true); `

  • select post where its id is equal 5 `php $post = $db->table('posts')->where('id', '=', 5)->select(); // or $post = $db->table('posts')->where('id', 5)->select(); // Custom fields $post = $db->table('posts')->where('id', 5)->select(['id', 'title', 'body']); `
  • multi where : `php $post = $db->table('posts') ->where('vote', '>', 5) ->where('visetors', '>', 200) ->select(); // Custom fields $post = $db->table('posts') ->where('vote', '>', 5) ->where('visetors', '>', 200) ->select(['id', 'title', 'body']); ` you can use where method an infinite :)

get last records (from last) :

to get last records in table just send seconde parameter to select method, or just first method to true

$fromLast = $db->table('users')->select(true);

or 

$fromLast = $db->table('users')->select(['id', 'name', 'email'], true);

where types :

  • whereBetween($field, $values = []) : `php $db->table('posts') ->whereBetween('data', [$start, $end]) ->select(); `
  • likeWhere($field, $value) : `php $db->table('users') ->likeWhere('name', 'mohammad') ->select(); `
  • orWhere($field, $operator, $value = false) : `php $db->table('posts') ->where('id', 5) ->orWhere('id', 3) ->select(['title', 'body']); ` ### get first row : `php $db->table('posts') ->where('id', 5) ->orWhere('id', 3) ->first(); ` all examples above you can replace select with first or paginate to get only first row selected.

find($id = 0) method :

find where id

$db->table('users')->find(1);
// SELECT * FROM `users` where `id` = 1

please note : change $_idColumn variable to id name in table if the table have no id set it to null. you can user idName() method or edit from Database class file direct

setIdName($id = 'id')

change id column name | by default is id

 $db->table('test')->idName('id_name');

insert($values = []) :

insert new user to users table:

$db->table('users')
    ->insert([
        'name' => 'mohammad',
        'email' => 'mohammad@email.com',
        'password' => 'secret',
    ]);

insert new post to posts table:

$db->table('posts')
    ->insert([
        'title' => 'my post title',
        'body' => 'post body and description',
        // ....
    ]);

update($values = []) :

if we need to update user name to 'ali' where his id is 5 :

$db->table('users')
    ->where('id', 5)
    ->update([
        'name' => 'ali'
    ]);

update all posts title like (test) to (this is a test post)

$db->table('posts')
    ->likeWhere('title', 'test')
    ->update([
        'title' => 'this is a test post'
    ]);

save()

if you select row and you want to update direct

is this example we configure "fetch" to object

use PHPtricks\Orm\Database;
$db = Database::connect();
$user = $db->table('users')->find('1');
$user->name = 'Mohammad';
$user->email = 'team@phptricks.org';
$user->save();

is this example we configure "fetch" to array

use PHPtricks\Orm\Database;
$db = Database::connect();
$user = $db->table('users')->find('1');
$user['name'] = 'Mohammad';
$user['email'] = 'team@phptricks.org';
$user->save();

but you cant use __save__ with multi rows

WRONG WAY :

    $multiUsers = $db->table('users')
        ->where('name', 'mohammad')
        ->select();
    
    $multiUsers->name = 'Mohhamed'; // ERROR
    $multiUsers->save(); // ERROR

RIGHT WAY :

    $multiUsers = $db->table('users')
        ->where('name', 'mohammad')
        ->select();
    
    foreach($multiUsers as $user)
    {
        $user->name = 'Mohhamed';
        $user->save();
    }

delete() :

delete user has id 105

$db->table('users')
    ->where('id', 105)
    ->delete();

// or
$db->table('users')->find(105)->delete();

// or 
$user = $db->table('users')->find(105);

if($user->active === 0)
{
    $user->delete();
}

// or

$allUsers = $db->table('users')->select();

foreach($allUsers as $user)
{
    if($user->active === 0)
    {
        $user->delete();
    }
}

delete all posts voted < 2 and visetors < 200 or id is 2

$db->table('posts')
    ->where('vote', "<", 2)
    ->where('visetors', '<', 200)
    ->orWhere('id', 2)
    ->delete();
    
// or 

$unnessoryPosts = $db->table('posts')
                      ->where('vote', "<", 2)
                      ->where('visetors', '<', 200)
                      ->orWhere('id', 2);
                      
$unnessoryPosts->delete();

limit($from = 0, $to = 0) :

get first 10 rows

$justTenRows = $db->table('posts')
    ->where('vote',">", 3)
    ->limit(10)
    ->select();

offset($offset) :

get first 10 rows offset 3

$db->table('posts')
    ->where('vote',">", 3)
    ->limit(10)
    ->offset(3)
    ->select();

in($field, $values = []) :

$db->table('posts')
    ->in('id', [1, 2, 3, 4, 5])
    ->select();

notIn($field, $values = []) :

$db->table('posts')
    ->notIn('id', [1, 2, 3, 4, 5])
    ->select();

paginate($recordsCount = 0, $last = false) :

to paginate results

paginate($recordsCount = 0) $recordsCount => default value take from database_config.php file

"pagination" => [
		"no_data_found_message" => "Oops, No Data Found to show ..",
		"records_per_page"      => 10,
		"link_query_key"        => "page"
	]

$db = PHPtricks\Orm\Database::connect();
$results = $db->table("blog")->paginate(15);
var_dump($results);

// to array or object
var_dump($results->results());

now add to url this string query (?page=2 or 3 or 4 .. etc) see (link() method to know how to generate navigation automatically)

<<<<<<< HEAD

link() :

  • get last records (from last) :

to get last records in table just send seconde parameter to paginate method, or just first method to true

$fromLast = $db->table('users')->paginate(true);

or 

$fromLast = $db->table('users')->paginate(20, true);

link :

>>>>>>> origin/master create pagination list to navigate between pages * compatible with bootstrap and foundation frameworks

 $db = PHPtricks\Database\Database::connect();
 $posts = $db->table("blog")->where("vote", ">", 2)->paginate(5);
 echo $posts->link();

dataView() :

view query results in table we need to create a simple table to view results of query

$db = PHPtricks\Database\Database::connect();
$data = $db->table("blog")->where("vote", ">", 2)->select();
echo $data->dataView();

recommended TEST Code :


$db = PHPtricks\Orm\Database::connect();
$posts = $db->table("blog")->paginate();
echo $posts->dataView();
echo $posts->link();

<<<<<<< HEAD

get last records (from last) :

to get last records in table after select or paginate

get last records
$lastRecords = $db->table('users')->select()->last();

or get last 10 records

$lastRecords = $db->table('users')->select()->last(10);

>>>>>>> origin/master

you can echo out the results directlly that convert the results to json format

$results = $db->table('table')->select();
echo $results; // return json format

or foreach results as last virsion.

$results = $db->table('table')->select();

foreach($results as $key => $value)
{
    // ..
}

orderBy()

to order results " ORDER BY "

$db->table('posts')
    ->notIn('id', [1, 2, 3, 4, 5])
    ->orderBy('id', 'DESC)
    ->select();
    
// OR


$db->table('posts')
    ->where('type', 'posts')
    ->orderBy('author', 'ASC')
    ->select();

plase note :

when you using " limit() with orderBy() " let limit() method after orderBy() for example :

``php

// OK $db->table('posts')

->orderBy('id', 'DESC)
->limit(10)
->select();

// NO $db->table('posts')

->limit(10)
->orderBy('id', 'DESC)
->select();

select(), first(), find(), paginate() methods
now return an instance of Collection class

### last()

get last record selected 

$all = $db->table('my_table')->select(); var_dump($all->last());

to get last records in table after select or paginate

get last records
$lastRecords = $db->table('users')->select()->last();

or get last 10 records

$lastRecords = $db->table('users')->select()->last(10);


### all()

all() and results() has same functionallaty

$all = $db->table('my_table')->select(); var_dump($all->all()); // var_dump($all->results());


### each(callable $callback)
to each results with callback function.

$results = $db->table('table')->select();

$results->each(function($row) {

echo $row->column_name . " !! <br>";

});

$new = []; $results->each(function($row) {

$new[] =  $row->column_name;

});

// you can chaning first(), last(), filter(), map(), each(), // toJson(), keys(), empty() with each() method

$results->each(function($row) {

$new[] =  $row->column_name;

});


### filter(callable $callback)

filter results values.

$results = $db->table('table')->select();

$filterdResults = $results->filter(function($row) {

return $row->id > 15;

});

//----

$results = $db->table('table')->select();

$filterdResults = $results->filter(function($row, $key) {

// you can use $key if you want 
return $row->id > 15;

});

//----

$results = $db->table('table')->select();

// exclude null values ! $filterdResults = $results->filter();

// you can use first(), last(), filter(), map(), each(), // toJson(), keys(), empty() with $filterdResults variable


### map(callable $callback)

$results = $db->table('table')->select(); $newResults = $results->map(function($row) {

// return ..

});

// you can use first(), last(), filter(), map(), each(), // toJson(), keys(), empty() with $newResults variable


### toJson()

convert results to json format

$results = $db->table('table')->select();

echo $results->toJson();


### merge(array|instance of Collection)

merge array with collection or 2 collections

$results = $db->table('table')->select(); $otherResults = $db->table('other_table')->select();

$merge = $results->merge($otherResults);

// you can use first(), last(), filter(), map(), each(), // toJson(), keys(), empty() with $merge variable


### keys()
get results keys

$results = $db->table('table')->select();

var_dump($results->keys());


### empty()

check if results id empty or not

$results = $db->table('table')->select();

if(empty($results)) {

echo "Oops, no results found";

} else {

var_dump($results);

}


--------------------------------

# Data Definition Language (DDL) :

### Create Table : 

use PHPtricks\Orm\Database; $db = Database::connect();

$db->table('my_new_table_name')->schema('schema as array')->create();

EX : 

use PHPtricks\Orm\Database; $db = Database::connect();

$db->table('students')->schema([

	'id' => 'increments',
	'name' => 'string:255 | not_null',
	'number' => 'int|unsigned';
])->create();
the SQL Statment for this :
CREATE TABLE students (
						id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
						name VARCHAR(255) NOT NULL,
						number INT UNSIGNED
					
					)
					
#### PLEAS NOTE: 
'id' => 'increments'
mean the id column well be integer,primary key, auto increment not null,  and unsigned

### ADD Constraints
'number' => 'int|my_constraint|other_constraint|more_constraint';

SO the first one is a column type and other well be Constraints

### Default Value

to set defualt value type :

'number' => 'int|unsigned|default:222'; 'name' => 'int|unsigned|default:hello-this-a-default-value';

// note : the charecter (-) replaced with white space

### Full Example :

$db = Database::connect();

$schema = [

'id' => 'increments',
'username' => 'string:100|not_null',
'full_name' => 'string:255|defualt:no-name',
'joined' => 'timestamp',
'user_email' => 'string:100|not_null',

];

$db->table('users')->schema($schema)->create();

# ADD Column :

$db->table('target_table')->alterSchema('condetions is array')->alter(); $db->table('table')->alterSchema(['add', 'column_name', 'type'])->alter();

#### EX:

$db->table('users')->alterSchema(['add', 'last_login', 'date'])->alter();


# RENAME Column :

$db->table('target_table')->alterSchema('condetions is array')->alter(); $db->table('table')->alterSchema(['rename', 'column_name', 'new_column_name' ,'type'])->alter();

#### EX:

$db->table('users')->alterSchema(['rename', 'last_login', 'last_session', 'date'])->alter();


# EDIT Column  type:

$db->table('table')->alterSchema(['modify', 'column_name', 'new_type'])->alter();

#### EX:

$db->table('users')->alterSchema(['modify', 'full_name', 'text'])->alter();


# DROP Column :

$db->table('table')->alterSchema(['drop', 'column_name'])->alter();

#### EX:

$db->table('users')->alterSchema(['drop', 'full_name'])->alter();



# Advanced Usage
 - `COMING SOON`

### THATS IT :) 

### I HOPE THIE HELP YOU.

=============================
# Change Log

<<<<<<< HEAD
### 4.0.0
* MODIFY : namesace to `PHPtricks\Orm`
* MODIFY : files structure
=======
### 3.2.0
* ADD    : Order By (orderBy())
* ADD    : get Latest Data inserted into table
* MODIFY : last() method to return last -n- recordes
>>>>>>> origin/master

### 3.1.0
* FIX : Duplicate connection
* ADD : Some methods
    * each() -> to each all collection values
    * map()  -> to map all results
    * all()  -> to get all results
    * last() -> to get last recored selectedw
    * filter() -> to filter values
    * keys() -> to get collection keys
    * toJson() -> to convert results to json format
* ADD : convert results to json format when use collection as string automaticlly

### 3.0.0
* ADD    : direct update functionality
* FIX    : dataView method with first method
* MODIFY : methods chaining technique
    * select, first, find, paginate NOW return Database Object
    * but you can use results as array or object
    * any time you can add ->results() to convert to array or object

### 2.1.0
* Add : pagination functionality
* Add : count method
* Add : dataView method (to display 'selected results' in table)
* FIX : in() method
* FIX : notIn() method

#### 2.0.0
* ADD : supports multi `drivers`
    * mysql
    * PostgreSQL
    * sqlite
    * msSql
    * sybase
    * Oracle Call Interface (OCI)
* ADD : multi where
* ADD : type of where
* ADD : show query
* FIX : default constraint
* ADD : limit function
* ADD : offset function
* rebuilt 80% of methods
* change License terms
#### 1.1.0

* ADD Some Data Definition Language (DDL) functions.
  * ADD Create New Table 
  * ADD Drop Table
  * ADD Alter Table
    * ADD new Column
    * Change Column Name
    * Drop Column
    * Rename Column

#### 1.0.1
* FIX first method -> to compatible with PHP V +5.3.0

#### 1.0.0
* First Release


=============================
# License
### MIT



  Files folder image Files  
File Role Description
Files folder image.idea (6 files, 1 directory)
Files folder imageConfig (2 files)
Files folder imagephptricksORM (2 files, 6 directories)
Files folder imagevendor (1 file, 1 directory)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file README.md Doc. README.md

  Files folder image Files  /  .idea  
File Role Description
Files folder imagecopyright (1 file)
  Accessible without login Plain text file deployment.xml Data Auxiliary data
  Accessible without login Plain text file misc.xml Data Auxiliary data
  Accessible without login Plain text file modules.xml Data Auxiliary data
  Accessible without login Plain text file mqalaty.iml Data Auxiliary data
  Accessible without login Plain text file vcs.xml Data Auxiliary data
  Accessible without login Plain text file workspace.xml Data Auxiliary data

  Files folder image Files  /  .idea  /  copyright  
File Role Description
  Accessible without login Plain text file profiles_settings.xml Data Auxiliary data

  Files folder image Files  /  Config  
File Role Description
  Accessible without login Plain text file config_function.php Aux. Auxiliary script
  Accessible without login Plain text file database_config.php Aux. Auxiliary script

  Files folder image Files  /  phptricksORM  
File Role Description
Files folder imageCollection (1 file)
Files folder imageDDL (3 files)
Files folder imageDML (6 files)
Files folder imageOperations (4 files)
Files folder imageProviders (1 file)
Files folder imageRelation (1 file)
  Accessible without login Plain text file Database.php Class Class source
  Accessible without login Plain text file Variables.php Class Class source

  Files folder image Files  /  phptricksORM  /  Collection  
File Role Description
  Accessible without login Plain text file Collection.php Class Class source

  Files folder image Files  /  phptricksORM  /  DDL  
File Role Description
  Accessible without login Plain text file Alter.php Class Class source
  Accessible without login Plain text file Base.php Class Class source
  Accessible without login Plain text file Create.php Class Class source

  Files folder image Files  /  phptricksORM  /  DML  
File Role Description
  Accessible without login Plain text file Delete.php Class Class source
  Accessible without login Plain text file Insert.php Class Class source
  Accessible without login Plain text file Main.php Class Class source
  Accessible without login Plain text file Paginate.php Class Class source
  Accessible without login Plain text file Query.php Class Class source
  Accessible without login Plain text file Update.php Class Class source

  Files folder image Files  /  phptricksORM  /  Operations  
File Role Description
  Accessible without login Plain text file Cond.php Class Class source
  Accessible without login Plain text file Main.php Class Class source
  Accessible without login Plain text file Other.php Class Class source
  Accessible without login Plain text file Where.php Class Class source

  Files folder image Files  /  phptricksORM  /  Providers  
File Role Description
  Accessible without login Plain text file Provider.php Class Class source

  Files folder image Files  /  phptricksORM  /  Relation  
File Role Description
  Accessible without login Plain text file Main.php Aux. Auxiliary script

  Files folder image Files  /  vendor  
File Role Description
Files folder imagecomposer (8 files)
  Accessible without login Plain text file autoload.php Aux. Auxiliary script

  Files folder image Files  /  vendor  /  composer  
File Role Description
  Accessible without login Plain text file autoload_classmap.php Aux. Auxiliary script
  Accessible without login Plain text file autoload_files.php Aux. Auxiliary script
  Accessible without login Plain text file autoload_namespaces.php Aux. Auxiliary script
  Accessible without login Plain text file autoload_psr4.php Aux. Auxiliary script
  Accessible without login Plain text file autoload_real.php Class Class source
  Accessible without login Plain text file autoload_static.php Class Class source
  Accessible without login Plain text file ClassLoader.php Class Class source
  Accessible without login Plain text file LICENSE Lic. License text

 Version Control Unique User Downloads Download Rankings  
 100%
Total:940
This week:3
All time:3,704
This week:255Up