romagny13/micro-db

MicroPHP DB library

0.0.2 2017-05-06 14:47 UTC

Requires (Dev)

• phpunit/phpunit: ^5.7

MIT

• Jerome Romagny

• db
• micro
• microphp

README

• MicroPHP

Installation

composer require romagny13/micro-db

Usage

Configure the database connection.

Example:

$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

Example:

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

Change the strategy. Example:

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:

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

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 :

$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

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

and get the last inserted id

$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:

• and
• or

PDO

Simple query

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

Query with params

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

Other example

$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

$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 ['*'].

use \MicroPHP\Db\Model;

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

Define the columns to fill

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

$posts = PostModel::all();

With limit

Example: only 10 posts (maximum) will be returned

$posts = PostModel::all(10);

With offset + limit

Example: only 10 posts (maximum) will be returned after 2 posts

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

Where

Allow to select the records to return (array)

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

or with a string

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

With offset + limit

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

Find

Returns only one item.

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

Create

Example:

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

Update

Example:

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

Delete

Example:

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

Query and prepare

Are shortcuts to Db functions.

Relations

Add relations (0-1) or (1-1) to other models

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:

• 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.

Example:

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