romagny13/micro-db

MicroPHP DB library

Micro Db

• MicroPHP

Installation

composer require romagny13/micro-db

Usage

Configure the database connection.

Example:, _(*1)

$settings = [
    'dsn' =>"mysql:host=localhost;dbname=blog",
    'username'=>'root',
    'password' =>''
];
Db::setConnectionStringSettings($settings['dsn'],$settings['username'],$settings['password']);

Sql tables/columns Strategy

By default the columns are wrapped with back ticks, _(*2)

Example:, _(*3)

select `posts`.`id`,`title`,`content`,`users`.`id`,`users`.`username` from `posts`,`users` order by `title`

Change the strategy. Example:, _(*4)

Db::setTableAndColumnStrategy('[',']');

select [posts].[id],[title],[content],[users].[id],[users].[username] from [posts],[users] order by [title]

With the Query Builder:

• select
• insert_into
• update
• delete

Examples:, _(*5)

Select

$posts = Db::getInstance()
    ->select('id','title','content','user_id')
    ->from('posts')
    ->where(Condition::op('user_id',1))
    ->orderBy('title')
    ->limit(10)
    ->fetchAll();

Other example, fetch a class, _(*6)

class Post { }

$posts = Db::getInstance()
    ->select('posts.id','title','content','user_id','users.id','users.username')
    ->from('posts','users')
    ->where('user_id=1')
    ->orderBy(Sort::desc('title'),'content desc')
    ->limit(2,10)
    ->fetchAll(Post::class);

Get the querystring :, _(*7)

$queryString = Db::getInstance()->select('posts.id','title','content','user_id','users.id','users.username')
    ->from('posts','users')
    ->where('user_id=1')
    ->orderBy(Sort::desc('title'),'content desc')
    ->limit(2,10)
    ->build();

var_dump($queryString);

select `posts`.`id`,`title`,`content`,`user_id`,`users`.`id`,`users`.`username` from `posts`,`users` where user_id=1 order by `title` desc,`content` desc limit 2,10

Insert

$success = Db::getInstance()
    ->insert_into('posts')
    ->columns('title','content','user_id')
    ->execute(['my title','my content',1]);

Or, _(*8)

$success = Db::getInstance()
    ->insert_into('posts')
    ->columns('title','content','user_id')
    ->values('my title','my content',1)
    ->execute();

and get the last inserted id, _(*9)

$id = Db::getInstance()->lastInsertId();

Update

$success = Db::getInstance()
    ->update('posts')
    ->set([
        'title'=>'new title',
        'content' => 'new content'
    ])
    ->where(Condition::op('id',1))
    ->execute();

Delete

$success = Db::getInstance()
    ->delete_from('posts')
    ->where(Condition::op('id',1))
    ->execute();

Condition helper

• op
• in
• between
• like

plus chaining conditions with:, _(*10)

• and
• or

PDO

Simple query, _(*11)

$posts = Db::getInstance()
    ->query('select * from posts')
    ->fetchAllWithClass(Post::class);

Query with params, _(*12)

$posts = Db::getInstance()
    ->prepare('select * from posts where id=:id')
    ->setParam(':id',1)
    ->fetchObject(Post::class);

Other example, _(*13)

$success = Db::getInstance()
    ->prepare('insert into posts (title,content,user_id) values (?,?,?)')
    ->execute(['My title', 'My content',2]);

$id = Db::getInstance()->lastInsertId();

Or with named params, _(*14)

$success = Db::getInstance()
    ->prepare('insert into posts (title,content,user_id) values (:title,:content,:user_id)')
    ->setParam(':title','My title')
    ->setParam(':content','My content')
    ->setParam(':user_id',2)
    ->execute();

$id = Db::getInstance()->lastInsertId();

Model

Create a model and define the db table. By default all columns will be filled ['*']., _(*15)

use \MicroPHP\Db\Model;

class PostModel extends Model
{
    public function __construct()
    {
        $this->table = 'posts';
    }
}

Define the columns to fill, _(*16)

use \MicroPHP\Db\Model;

class PostModel extends Model
{
    public function __construct()
    {
        $this->table = 'posts';
        $this->columns = ['id','title','content','user_id'];
    }
}

All

get all the records of the table, _(*17)

$posts = PostModel::all();

With limit, _(*18)

Example: only 10 posts (maximum) will be returned, _(*19)

$posts = PostModel::all(10);

With offset + limit, _(*20)

Example: only 10 posts (maximum) will be returned after 2 posts, _(*21)

$posts = PostModel::all(2,10);

Where

Allow to select the records to return (array), _(*22)

$posts = PostModel::where(Condition::op('user_id',1)->_or_(Condition::op('user_id',2)));

or with a string, _(*23)

$posts = PostModel::where('user_id=1 or user_id=2');

With offset + limit, _(*24)

$posts = PostModel::where('user_id=1 or user_id=2',2,10);

Find

Returns only one item., _(*25)

$post = PostModel::find(Condition::op('id',1));

Create

Example:, _(*26)

$success = PostModel::create([
    'title' => 'My title',
    'content' => 'My content',
    'user_id' => 1
]);

Update

Example:, _(*27)

$success = PostModel::update([
    'title' => 'My new title',
    'content' => 'My new content'
],Condition::op('id',1));

Delete

Example:, _(*28)

$success = PostModel::delete(Condition::op('id',1));

Query and prepare

Are shortcuts to Db functions., _(*29)

Relations

Add relations (0-1) or (1-1) to other models, _(*30)

class UserModel extends Model
{
    public function __construct()
    {
        $this->table = 'users';
    }
}

class CategoryModel extends Model
{
    public function __construct()
    {
        $this->table = 'categories';
    }
}

class PostModel extends Model
{
    public function __construct()
    {
        $this->table = 'posts';
        $this->columns = ['title','content'];

        // relations
        $this->addRelation('users',['user_id' => 'id'],UserModel::class, 'user');
        $this->addRelation('categories',['category_id' => 'id'],CategoryModel::class, 'category');
    }
}

addRelation parameters:, _(*31)

• foreign key table name
• foreign key => primary key pairs
• model to fill
• property name to add

User and category properties will be added to the post model., _(*32)

Example:, _(*33)

$post = PostModel::find(Condition::op('id',1));