nhrdev / nhr_db
This is a PDO based MySQL or MariaDB database helper library
Requires
- php: >=7.4
README
This PHP library will help you to create database connetion using PDO and creating tables,inserting,updating,fetching,deleting data from database without writing any SQL code.Just create an object of the "DB" class and then the power is yours! :)
How to install :
[username@host]$ composer require nhrdev/nhr_db
How to use :
- To connect
YOU MUST PASS AN ARRAY WITH THESE KEYS AND YOUR SPECIFIC VALUES TO CONNECT TO THE DATABASE.
driver
host
port
and charset
are optional.
driver
is set to mysql
and
host
is set to localhost
by default
use NhrDev\NHR_DB\DB; $db = new DB(`DB_USERNAME`, `DB_PASSWORD`, `DB_NAME`, [`DB_HOST`, `DB_PORT`, `DB_CHARACTERSET`]);
- To disconnect :
$db->disconnect();
- To connect if disconnected :
$db->connect();
- To check if connected or not :
if($db->is_connected()){ echo "Connected!"; }
- To create a new table :
$table = $db->table( 'table_name' ); $table->id() ->int( 'id', 255 ) ->unsigned_int( 'id', 255 ) ->unsigned_bigint( 'id', 255 ) ->col( 'id', DB::int(1), true ) ->str( 'username', 255) ->col( 'username', DB::str(100) ) ->text( 'details' ) ->col( 'details', DB::text() ) ->float( 'amount' ) ->col( 'amount', DB::float() ) ->enum( ['true', 'false'] ) ->col( 'enum', DB::enum(['value1', 'value2']) ) ->col( 'date', DB::date() ) ->col( 'datetime', DB::datetime() ) ->timestamp(); $table->create();
To add columns after creating the table, after calling the create()
method
$table->add(string $name, string $type_and_length, bool $is_primary = false, $is_auto_increment = false, bool $is_not_null = false, bool $is_unique = false);
- To drop any table
$table->drop("COLUMN_NAME");
- To drop all the columns or to drop the whole
TABLE_NAME
$table->drop_all();
NOTE : col
,add
,drop
,drop_all
these methods will return the table object
$table
, so in this case so you can do method chaining like
$table->col()->add->drop->drop_all();
- To insert row into the table
$table->insert([ 'column_name' => 'value' ]);
- To get the last inserted id This will return false if no insertion is made.
$table->last_insert_id()
- To update specific rows
$table->update([ 'column_name' => 'value' ])->where("active", DB::eq(true)) ->where("user", "=", "true") ->where("amount", DB::between(10, 2001)) ->or("referrer", "=", -1) ->or("roll", DB::between(10, 59)) ->where("username", DB::begins_like("ra")) ->execute(); Returns the number of rows affected or false on failure.
- To update all rows
$table->update([ 'column_name' => 'value' ]);
- To delete specific rows
$table->delete() ->where("active", DB::eq(true)) ->where("user", "=", "true") ->where("amount", DB::between(10, 2001)) ->or("referrer", "=", -1) ->or("roll", DB::between(10, 59)) ->where("username", DB::begins_like("ra")) ->order_by('id', 'DESC') ->limit(5) ->execute(); Returns the number of rows affected or false on failure.
- To delete all rows
$table->delete();
- To fetch rows
This function will return \NhrDev\NHR_DB\Src\Result
object with some functions to access the fetched data.
$rows = $table->select([], DB::OBJ) ->where("active", DB::eq('true')) ->or("amount", DB::between(10, 500)) ->order_by("id", 'DESC') ->limit(1) ->offset(1) ->execute();
Here DB::OBJ
for object DB::ASSOC
for associative array and DB::IND
for indexed array
- To get all the rows from the fetched data
$rows->all();
- To get the first row
$rows->first();
- To get the last row
$rows->last();
- To loop through the rows
The second parameter is false
by default. If you set this true
then the loop will be in reverse order.
$rows->each(function($row, $index){ # your code }, false);
- To get a single row by INDEX
This will return false
if you pass an index less than 0
or greater than the number of rows fetched
$rows->get(5);