File: inc/classes/Libs/Phptricks_Database/README.md

Recommend this page to a friend!
  Classes of mohammad anzawi  >  PHP Multilingual Support Library  >  inc/classes/Libs/Phptricks_Database/README.md  >  Download  
File: inc/classes/Libs/Phptricks_Database/README.md
Role: Example script
Content type: text/markdown
Description: Example script
Class: PHP Multilingual Support Library
Translate texts for Web sites from JSON or DB
Author: By
Last change:
Date: 4 years ago
Size: 9,438 bytes
 

Contents

Class file image Download

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 isseu to fix it.

  • To Arabic latst version go to : http:/www.phptricks.org/PDO-class-v-2-xp : ????? ?????? ??????? ??????? ?????? ???? ???
  • To Arabic v-2 go to : http://www.phptricks.org/PDO-class-v-2 : ????? ?????? ??????? ??????? 2 ???? ???
  • To Arabic v-1 go to : http://www.phptricks.org/PDO-class : ????? ????? ??????? ??????? 1 ???? ???

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 conection 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)
    use PHPtricks\Database\Database;
    $db = Database::connect();
    

how it work (methods):

select() :

- select all data from test table :

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

print_r($allData);
```
  • select `id`, `name`, `email` fro all users from `users` table
    $coustomFields = $db->table('users')->select(['id', 'name', 'email']);
    
    print_r($coustomFields);
    
  • select `post` where its `id` is equal 5
    $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 :
    $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() :
    $db->table('posts')
        ->whereBetween('data', [$start, $end])
        ->select();
    
  • likeWhere() :
    $db->table('users')
        ->likeWhere('name', 'mohammad')
        ->select();
    
  • orWhere() :
    $db->table('posts')
        ->where('id', 5)
        ->orWhere('id', 3)
        ->select(['title', 'body']);
    
    ### get first row :
    
    $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 method :

find where id

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

insert :

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 :

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'
    ]);

delete :

delete user has id 105

$db->table('users')
    ->where('id', 105)
    ->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();

limit :

get first 10 rows

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

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

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

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

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

recommended TEST Code :


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

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

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


For more information send a message to info at phpclasses dot org.