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 (5)   DownloadInstall with Composer Download .zip   Reputation   Support forum (3)   Blog (1)    
Last Updated Ratings Unique User Downloads Download Rankings
2017-01-17 (1 month ago) RSS 2.0 feedNot enough user ratingsTotal: 843 This week: 11All time: 3,973 This week: 88Up
Version License PHP version Categories
php-database-class 2.0.5GNU 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: 20802 in Jordan Jordan
Week rank: 79 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 :

(config) :

  • go to (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
    include_once('phptricks/Database.php');

step 2 :

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

how it work (methods):

select() :

very important (select, first, find, paginate) methods __return Database 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 yo can foreach the returned values foreach($coustomFields as $fields) { // ... } `

  • 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 :)

where types :

  • whereBetween() : `php $db->table('posts') ->whereBetween('data', [$start, $end]) ->select(); `
  • likeWhere() : `php $db->table('users') ->likeWhere('name', 'mohammad') ->select(); `
  • orWhere() : `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 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\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\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 :

get first 10 rows

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

offset :

get first 10 rows offset 3

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

in :

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

notIn :

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

paginate :

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\Database\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)

link :

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\Database\Database::connect();
$posts = $db->table("blog")->paginate();
echo $posts->dataView();
echo $posts->link();

New V.3.1.0

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)
{
    // ..
}

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());

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 :

$db = Database::connect();

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

EX :

$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

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
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file config_function.php Aux. Auxiliary script
Accessible without login Plain text file Database.php Class Class source
Accessible without login Plain text file database_config.php Aux. Auxiliary script
Accessible without login Plain text file README.md Doc. README.md

 Version Control Unique User Downloads Download Rankings  
 100%
Total:843
This week:11
All time:3,973
This week:88Up