README

Inspiration

Having been introduced to learning Laravel Framework; Over the past yr(s), Coming back to vanilla PHP, was pretty tough. So i decided to create a much more easier way of communicating with Database, using native PHP PDO:: Driver.

Documentation

Requirements
Installation
Instantiate
Init.php File
BootLoader
Database Connection
Database Disconnect
App Debug ENV
More Database Connection Keys
Usage
- Table
- Insert
- Insert Or Ignore
- Update
- Update Or Ignore
- Delete
- Increment
- Decrement
Fetching Data
- Get
- First
- First or Create
- First or Fail
- Count
- Paginate
- Exist
- Table Exists
Collections
- Collection Methods
- Collection Usage
Pagination
- Global Configuration
- Pagination Query
- Pagination Data
- Pagination Links
- Pagination Links Config
- Pagination Showing
- Pagination Showing Config
- Pagination Foreach Numbers
- Get Pagination
Clause
- Query
- select
- selectRaw
- orderBy
- orderByRaw
- latest
- oldest
- inRandomOrder
- random
- limit
- offset
- join
- leftJoin
- where
- orWhere
- whereRaw
- whereColumn
- whereNull
- orWhereNull
- whereNotNull
- orWhereNotNull
- whereBetween
- whereNotBetween
- whereIn
- whereNotIn
- groupBy
Database Migration
- Create Table Schema
- Default String Length
- Update Column Default Value
- Run Migration
- Drop Migration
- Drop Table
- Drop Column
Get Database Config
Get Database Connection
Get Database Name
Get Database PDO
Get Database TablePrefix
Database Import
Update Env Variable
Autoload Register
Collation And Charset
Extend Model Class
Helpers Functions
Error Dump
Error Status
Useful links

Requirements

>= php 8.0+

Installation

Prior to installing database package get the Composer dependency manager for PHP because it'll simplify installation.

composer require tamedevelopers/database

Instantiate

Step 1 — Require composer autoload:

require_once __DIR__ . '/vendor/autoload.php';

Step 2 — Call the below method() and Run once in browser

This will auto setup your entire application on a go!

use Tamedevelopers\Database\AutoLoader;

AutoLoader::start();

or -- Helpers Function

autoloader_start();

Init.php File

[optional] This will extends the composer autoload and other setup

BootLoader

If you do not want to include or use the init.php file
- All you need do is call the bootloader, to start your application.

use Tamedevelopers\Database\Capsule\AppManager;

AppManager::bootLoader();

or -- Helpers Function

app_manager()->bootLoader();

Database Connection

You have the options to connect to multiple database
- First navigate to [config/database.php] file and add a configuration
- Takes two (2) params key as string and array [optional]

DB::connection('connName', [optional]);

Database Disconnect

If you want to connect to already connected database, You first need to disconnect
- Takes one param as string

DB::disconnect('connName');

Database Reconnect

same as Database Connection

DB::reconnect('connName', [optional]);

App Debug Env

The .env file contains a key called APP_DEBUG
- It's mandatory to set to false in Production environment
- This helps to secure your applicaiton and exit error 404
- instead of displaying entire server errors.

More Database Connection Keys

All available connection keys
- The DB_CONNECTION uses only mysql
- No other connection type is supported for now.

Usage

All Methods of usage

Table

Takes a parameter as string table_name

$db->table('users');

Insert

Takes one parameter as assoc array column_name => value
- It returns an object on success or error

$db->table('users')->insert([
    'user_id'    => 10000001,
    'first_name' => 'Alfred',
    'last_name'  => 'Pete',
    'wallet_bal' => 0.00,
    'registered' => strtotime('now'),
]);

-- To see data, you need to save into a variable

Insert Or Ignore

Same as insert() method
- It returns an object of created data or false on error

$db->table('users')->insertOrIgnore([
    'user_id'    => 10000001,
    'first_name' => 'Alfred',
]);

Update

Takes one parameter as assoc array column_name => value
- Returns an int numbers of affected rows or error

$db->table('users')
    ->where('user_id', 10000001)
    ->update([
        'first_name' => 'Alfred C.',
    ]);

Update Or Ignore

Same as update() method
- Returns an int numbers of affected rows or 0 on error

$db->table('users')
    ->where('user_id', 10000001)
    ->updateOrIgnore([
        'first_name' => 'Alfred C.',
    ]);

Delete

Returns an int

$db->table('users')
    ->where('user_id', 10000001)
    ->delete();

Increment

Takes three parameter
- Only the first param is required

1 By default if the the second param not passed, this will increment by 1

$db->table('users')
    ->where('user_id', 10000001)
    ->increment('wallet_bal');

$db->table('users')
    ->where('user_id', 10000001)
    ->increment('wallet_bal', 10);

You can also pass in a second or third parameter to update additional columns

$db->table('users')
    ->where('user_id', 10000001)
    ->increment('wallet_bal', 100.23, [
        'first_name' => 'F. Peterson',
        'status'     => 1,
    ]);

You can ommit the second param and it'll be automatically seen as update param (If an array)

$db->table('users')
    ->where('user_id', 10000001)
    ->increment('wallet_bal', [
        'first_name' => 'F. Peterson',
        'status'     => 1,
    ]);

Decrement

Same as Increment

$db->table('users')
    ->where('user_id', 10000001)
    ->decrement('wallet_bal', [
        'first_name' => 'F. Peterson',
        'status'     => 1,
    ]);

Fetching Data

GET

$db->table('users')->get();

First

$db->table('users')->first();

First or Create

Take two param as an array
- Mandatory $conditions param as array
- [optional] $data param as array
First it checks if codition to retrieve data. If fails, then it merge the $conditions to $data value to create new records

$db->table('users')->firstOrCreate(
    ['email' => 'example.com']
);

or -- Example 2

$db->table('users')->firstOrCreate(
    ['email' => 'example.com'],
    [
        'country'   => 'Nigeria',
        'age'       => 18,
        'dob'       => 2001,
    ]
);

First or Fail

Same as first() method but exit with error code 404, if data not found

$db->table('users')->firstOrFail();

Count

$db->table('users')->count();

Paginate

Takes param as int $per_page
- By default if no param is given, then it displays 10 per page

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


$users // this will return the data objects
$users->links() // this will return the paginations links view
$users->showing() // Display items of total results

Exists

Returns boolean true \| false

$db->table('users')
    ->where('email', 'email@gmail.com')
    ->orWhere('name', 'Mandison')
    ->exists();

Table Exists

Takes param as string $table_name

$db->tableExists('users');

Collections

You can directly use methods of Collections Instance on any of the below
- All the below methods are received by Collection class
1. get()
2. find()
3. first()
4. firstOrIgnore()
5. firstOrCreate()
6. firstOrFail()
7. insert()
8. insertOrIgnore()

Collection Methods

Collection Usage

Colections are called automatically on all Database Fetch Request
- With this you can access data as an object\|array key property
- If no data found then it returns null on ->first() method only

$user = $db->tableExists('users')
            ->first();

if($user){
    $user->first_name
    $user['first_name']
}

$user->toArray()
$user->getAttributes()

Example two(2) ->get() \| ->paginate() Request

$users = $db->tableExists('users')
            ->where('is_active', 1),
            ->random(),
            ->get();

if($users->isNotEmpty()){
    foreach($users as $user){
        $user->first_name
        $user['first_name']
        $user->toArray()
        $user->getAttributes()
    }
}

Pagination

Configuring Pagination

Global Configuration

1 Setup global pagination on ENV autostart most preferred method

AutoLoader::configPagination([
    'allow' => true, 
    'prev'  => 'Prev Page', 
    'last'  => 'Last Page', 
    'next'  => 'Next Page', 
    'view'  => 'bootstrap',
    'class' => 'Custom-Class-Css-Selector', 
]);

or -- Helpers Function

config_pagination([
    'allow' => true,
]);

Pagination Query

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

Pagination Data

$users
// This will return `Collections` of pagination data

Pagination Links

$users->links();
// This will return pagination links view

Pagination Links Config

You can directly configure pagination links
- Note: If configPagination() allow is set to true
- It'll override every other settings

$users->links([
    'first' => 'First Page',
    'last'  => 'Last Page',
    'prev'  => 'Previous Page',
    'next'  => 'Next Page',
])

Pagination Showing

$users->showing();

// This will create a span html element with text
<span class='page-span'>
    Showing 0-40 of 500 results
</span>

Pagination Showing Config

You can configure showing text directly as well

$users->showing([
    'showing'  => 'Showing',
    'of'       => 'out of',
    'results'  => 'Results',
    'span'     => 'css-selector',
])

Pagination Foreach Numbers

Page numbering starts counting from 1
- This will format all pagination items collections
- On each page, it starts counting from last pagination item number

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

foreach($users as $user){
    echo $user->numbers();
}

Get Pagination

Returns pagination informations

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

$users->getPagination();

Clause

Multiple clause

Query

Allows the use direct sql query SQL query syntax
- Or direct query exec()

$db->query("SHOW COLUMNS FROM users")
    ->limit(10)
    ->get();


$db->query("ALTER TABLE `langs` ADD COLUMN es TEXT; UPDATE `langs` SET es = en;")
    ->exec();

Select

Used to select needed columns from database

$db->table('users')
    ->where('user_id', 10000001)
    ->select(['first_name', 'email'])
    ->select('email, 'name')
    ->first();

orderBy

Takes two param $column and $direction
- By default $direction param is set to ASC

$db->table('wallet')
    ->orderBy('date', 'DESC')
    ->get();

orderByRaw

Takes one param $query

$db->table('wallet')
    ->orderByRaw('CAST(`amount` AS UNSIGNED) DESC')
    ->get();

Latest

Takes one param $column by default the column used is id

$db->table('wallet')
    ->latest('date')
    ->get();

Oldest

Takes one param $column by default the column used is id

$db->table('wallet')
    ->oldest()
    ->get();

inRandomOrder

$db->table('wallet')
    ->inRandomOrder()
    ->get();

random

Same as inRandomOrder()

$db->table('wallet')
    ->random()
    ->get();

limit

Takes one param $limit as int. By default value is 1

$db->table('wallet')
    ->limit(10)
    ->get();

offset

Takes one param $offset as int. By default value is 0

$db->table('wallet')
    ->limit(3)
    ->offset(2)
    ->get();

Example 2 (Providing only offset will return as LIMIT without error)

$db->table('wallet')
    ->offset(2)
    ->get();

join

Includes join|leftJoin|rightJoin|crossJoin

$db->table('wallet')
    ->join('users', 'users.user_id', '=', 'wallet.user_id')
    ->get();

$db->table('wallet')
    ->join('users', 'users.user_id', '=', 'wallet.user_id')
    ->where('wallet.email', 'example.com')
    ->orWhere('wallet.user_id', 10000001)
    ->paginate(10);

leftJoin

Same as join

$db->table('wallet')
    ->leftJoin('users', 'users.user_id', '=', 'wallet.user_id')
    ->where('wallet.email', 'example.com')
    ->get();

where

Takes three parameter
- Only the first param is required

$db->table('wallet')
    ->where('user_id', 10000001)
    ->where('amount', '>', 10)
    ->where('balance', '>=', 100)
    ->get();

orWhere

Same as Where clause

$db->table('wallet')
    ->where('user_id', 10000001)
    ->where('amount', '>', 10)
    ->orWhere('first_name', 'like', '%Peterson%')
    ->where('amount', '<=', 10)
    ->get();

whereRaw

Allows you to use direct raw SQL query syntax

$date = strtotime('next week');

$db->table("tb_wallet")
    ->whereRaw("NOW() > created_at")
    ->whereRaw("date >= ?", [$date])
    ->where(DB::raw("YEAR(created_at) = 2022"))
    ->where('email', 'email@gmail.com')
    ->limit(10)
    ->random()
    ->get();

whereColumn

Takes three parameter column operator column2

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereColumn('amount', 'tax')
    ->whereColumn('amount', '<=', 'balance')
    ->get();

whereNull

Takes one parameter column

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereNull('email_status')
    ->get();

whereNotNull

Takes one parameter column

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereNotNull('email_status')
    ->get();

whereBetween

Takes two parameter column as string param as array
- Doesn't support float value

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereBetween('amount', [0, 100])
    ->get();

whereNotBetween

Same as whereBetween() method

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereNotBetween('amount', [0, 100])
    ->get();

whereIn

Takes two parameter column as string param as array
- Doesn't support float value

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereIn('amount', [10, 20, 40, 100])
    ->get();

whereNotIn

Same as whereIn() method

$db->table('wallet')
    ->where('user_id', 10000001)
    ->whereNotIn('amount', [10, 20, 40, 100])
    ->get();

groupBy

Takes one param $column

$db->table('wallet')
    ->where('user_id', 10000001)
    ->groupBy('amount')
    ->get();

Database Migration

Similar to Laravel DB Migration Just to make database table creation more easier

use Tamedevelopers\Database\Migrations\Migration;

Create Table Schema

Takes param as table name
- Second parameter string jobs|sessions (optional) -If passed will create a dummy jobs|sessions table schema

Migration::create('users');
Migration::create('users_wallet');
Migration::create('tb_jobs', 'jobs');
Migration::create('tb_sessions', 'sessions'); 

Table `2023_04_19_1681860618_user` has been created successfully
Table `2023_04_19_1681860618_user_wallet` has been created successfully
Table `2023_04_19_1681860618_tb_jobs` has been created successfully
Table `2023_04_19_1681860618_tb_sessions` has been created successfully

or -- Helpers Function

migration()->create('users');

Default String Length

In some cases you may want to setup default string legnth to all Migration Tables

use Tamedevelopers\Database\Migrations\Schema;

Schema::defaultStringLength(200);

or -- Helpers Function

schema()->defaultStringLength(2000);

Update Column Default Value

In some cases you may want to update the default column value
- Yes! It's very much possible with the help of Schema. Takes three (3) params
- $tablename as string
- $column_name as string
- $values as mixed data NULL NOT NULL\|None STRING current_timestamp()

use Tamedevelopers\Database\Migrations\Schema;

Schema::updateColumnDefaultValue('users_table', 'email_column', 'NOT NULL);
Schema::updateColumnDefaultValue('users_table', 'gender_column', []);

or

schema()->updateColumnDefaultValue('users_table', 'gender_column', []);

Run Migration

This will execute and run migrations using files located at [root/database/migrations]

Migration::run();

or
migration()->run();

Migration runned successfully on `2023_04_19_1681860618_user` 
Migration runned successfully on `2023_04_19_1681860618_user_wallet`

Drop Migration

Be careful as this will execute and drop all files table located in the migration
[optional param] bool to force delete of tables

Migration::drop();

or
migration()->drop(true);

Drop Table

Takes one param as string $table_name

use Tamedevelopers\Database\Migrations\Schema;

Schema::dropTable('table_name');

or 
schema()->dropTable('table_name');

Drop Column

To Drop Column takes two param
- This will drop the column available

use Tamedevelopers\Database\Migrations\Schema;

Schema::dropColumn('table_name', 'column_name');

or 
schema()->dropColumn('table_name', 'column_name');

Get Database Config

$db->getConfig()

Get Database Connection

$db->dbConnection()

or -- Helpers Function

db_connection();

Get Database Name

$db->getDatabaseName()

Get Database PDO

$db->getPDO()

Get Database TablePrefix

$db->getTablePrefix()

Database Import

You can use this class to import .sql into a database programatically
- Remember the system already have absolute path to your project.

use Tamedevelopers\Database\DBImport;

$database = new DBImport();

// needs absolute path to database file
$status = $database->import('path_to/orm.sql');

- Status code
->status == 404 (Failed to read file or File does'nt exists
->status == 400 (Query to database error
->status == 200 (Success importing to database

or -- Helpers Function

import('path_to/orm.sql');

Update Env Variable

You can use this class to import .sql into a database programatically

use Tamedevelopers\Support\Env;

Env::updateENV('DB_PASSWORD', 'newPassword');
Env::updateENV('APP_DEBUG', false);
Env::updateENV('DB_CHARSET', 'utf8', false);

Returns - Boolean
true|false

or -- Helpers Function

env_update('DB_CHARSET', 'utf8', false);

Collation And Charset

Collation and Charset Data listing

Collation

utf8_bin
utf8_general_ci
utf8mb4_bin
utf8mb4_unicode_ci
utf8mb4_general_ci
latin1_bin
latin1_general_ci

Charset

utf8
utf8mb4
latin1

Extend Model Class

You can as well extends the DB Model class directly from other class

use Tamedevelopers\Database\Model;

class Post extends Model{
    
    // define your custom model table name
    protected $table = 'posts';

    -- You now have access to the DB public instances
    public function getPost(){
        return $this->select(['images', 'title', 'description'])->get();
    }
}

tamedevelopers / database

Maintainers

Details

README

Inspiration

Documentation

Requirements

Installation

Instantiate

Init.php File

BootLoader

Database Connection

Database Disconnect

Database Reconnect

App Debug Env

More Database Connection Keys

Usage

Table

Insert

Insert Or Ignore

Update

Update Or Ignore

Delete

Increment

Decrement

Fetching Data

GET

First

First or Create

First or Fail

Count

Paginate

Exists

Table Exists

Collections

Collection Methods

Collection Usage

Pagination

Global Configuration

Pagination Query

Pagination Data

Pagination Links

Pagination Links Config

Pagination Showing

Pagination Showing Config

Pagination Foreach Numbers

Get Pagination

Clause

Query

Select

orderBy

orderByRaw

Latest

Oldest

inRandomOrder

random

limit

offset

join

leftJoin

where

orWhere

whereRaw

whereColumn

whereNull

whereNotNull

whereBetween

whereNotBetween

whereIn

whereNotIn

groupBy

Database Migration

Create Table Schema

Default String Length

Update Column Default Value

Run Migration

Drop Migration

Drop Table

Drop Column

Get Database Config