README

Retrieves MySQL database instances SIMPLESTly in OOP style

Initialize

Set the database name

\DbModel\Model::$database = 'database';

Retrieve All Instances

Get all the instances

$users = \DbModel\Model::all('users')

Now you can access all attributes of User in an Object oriented fashion.

$user = $users[0];
echo $user->name;
echo $user->email;
echo $users[2]->birth_day;

Do not want to retrieve all the fields? Just add an additional array or string to all() method to denote the fields you need.

$users = \DbModel\Model::all('users', 'name, email');
$users = \DbModel\Model::all('users', ['name', 'email']); // Same as previous
echo $users[0]->birth_day // Results error

Retrieve conditionally

Want to retrieve instances on condition? Use where() method and give the condition.

$adults = \DbModel\Model::where('users', 'age > 18');
$maleAdults = \DbModel\Model::where('users', 'age > 18 AND sex = "Male"');
$maleAdultNames = \DbModel\Model::where('users', 'age > 18 AND sex = "Male"', 'name');

Retrieve single instance

Want to retrieve a single user based on id? Use find() method to simplify the action.

$user = \DbModel\Model::find('users', '1');
$user = \DbModel\Model::find('users', 5);

Note that where() and all() method returns array of instances where find() method returns a single instance.

If your primary key is not named 'id', then you have to mention it.

$user = \DbModel\Model::find('users', '1', 'user_id');

And obviously the columns as the last parameter.

$user = \DbModel\Model::find('users', '1', 'user_id', 'first_name, last_name');

$columns = ['first_name', 'last_name'];
$user = \DbModel\Model::find('users', '1', 'user_id', $columns);

Retrieve using relation

DB-Model Supports One to Many, Many to One and Many to Many relationship. However, you can use these methods to retrieve your One to One relation as well.

Note that oneToMany(), manyToOne() and manytoMany() are instance method. Where all(), where() and find() are static method.

One to Many

Say, your User has many Posts. So, Posts must have a user_id field to store primary key of User. The user_id field in Post is said Foreign Key. Again, id of User is said Referenced Key as it is referenced by user_id in Post. You can easily retrieve the Posts of User using oneToMany() method. The simplest form is

$user = \DbModel\Model::find('users', '1');
$posts = $user->oneToMany('posts', 'user_id');

If primary key of User is not id, then you should guess what to pass next.

$user = \DbModel\Model::find('users', '1', 'user_primary_key');
$posts = $user->oneToMany('posts', 'user_id', 'user_primary_key');

// To retrieve Post that contains 'ghost' in title

$posts = $user->oneToMany('posts', 'user_id', 'user_primary_key',
    'title LIKE "%ghost%"');

// To retrieve title and body only
$posts = $user->oneToMany('posts', 'user_id', 'user_primary_key',
    'TRUE', 'title, body');

If you are clever enough, you should understand that, oneToMany() can also be used to store one to one relationship. If Settings has an one to one relationship with User, then each User will have exactly one Settings. So what are you waiting for?

$user = \DbModel\Model::find('users', '1');
$settings = $user->oneToMany('settings', 'user_id')[0]; //Retrieves the only settings user have.

Of course, you can use the rest of parameters al well.

Many to One

What if you need to get the corresponding User instance from a Post post instance? manyToOne() is here for you with same signature.

$post = \DbModel\Model::find('posts', '1');
$user = $post->manyToOne('user', 'user_id');

If your User has a different primary key than id, then mention that too.

$post = \DbModel\Model::find('posts', '1');
$user = $post->manyToOne('user', 'user_id', 'user_primary_key');

// To specify the columns

$user = $post->manyToOne('user', 'user_id', 'user_primary_key', 'name, email');

Again, if you want to retrieve using inverse one to one relationship, use manyToOne() method as well.

$settings = \DbModel\Model::find('settings', '1');
$user = $post->manyToOne('user', 'user_id', 'user_primary_key');

Many to Many

To implement Many to Many relationship in database you need an intermediate table which contains foreign key of both the related tables. Suppose, Post has a many to many relationship with Tag. Then, you need an intermediate table, say post_tag which contains foreign key of Post and Tag, say post_id and tag_id. Now, your code will be

$post = \DbModel\Model::find('posts', '1');
$tags = $post->manytoMany('tags', 'post_tag', 'post_id', 'tag_id');

// Or inversely
$tag = \DbModel\Model::find('tags', '1');
$posts = $tag->manytoMany('posts', 'post_tag', 'tag_id', 'post_id');

If the models have different primary key than id, then mention it next.

$post = \DbModel\Model::find('posts', '1', 'post_primary_key');
$tags = $post->manytoMany('tags', 'post_tag', 'post_id', 'tag_id',
    'post_primary_key', 'tag_primary_key');

// Or inversely
$tag = \DbModel\Model::find('tags', '1', 'tag_primary_key');
$posts = $tag->manytoMany('posts', 'post_tag', 'tag_id', 'post_id',
    'tag_primary_key', 'post_primary_key');
    
// Select conditionally
$posts = $tag->manytoMany('posts', 'post_tag', 'tag_id', 'post_id',
    'tag_primary_key', 'post_primary_key', 'title LIKE "%ghost%"');

// Select columns as well
$posts = $tag->manytoMany('posts', 'post_tag', 'tag_id', 'post_id',
    'tag_primary_key', 'post_primary_key',
     'title LIKE "%ghost%"', ['name', 'body']);

Insertion

To insert a Model into database, simple just create a Model instance. Set the properties. Call the store() method.

$user = new Model();
$user->name = 'Dennis Ritchie';
$user->email = 'dennis@example.com';
$user->password = 'pa$$word';
$user->store('users');

store() method has two aliases namely insert() and save() with same signature.

Updating

Create a model. Set only properties to be updated. Call update() method with table name and the condition on which the update will be performed. And you are done!

$user = new Model();
$user->name = 'Christian Bale';
$user->update('users', 'id = 5');

To avoid mistakes, update() method does not have a default $condition parameter.

Want to update using the primary key? Then you will prefer the updateById() method.

$user = new Model();
$user->id = 5;
$user->name = 'Christian Bale';
$user->updateById('users');

When your primary key is not id then you have to pass the name of primary key as the next argument.

$user = new Model();
$user->primary_key = 5;
$user->name = 'Christian Bale';
$user->updateById('users', 'primary_key');

Customizing Connection Params

There are five public static field that are used to connect with database. These are $host, $database, $username, $password, $port. The default values for these fields are

public static $host = 'localhost';
public static $username = 'root';
public static $password = '';
public static $port = '3306';

You can change these values according to your database.

Running Raw Queries

Although, not suggested, you can run raw queries using \DbModel\Query class. Simply make a \DbModel\Query instance and run your query.

$query = "SELECT * FROM table WHERE 1 = TRUE"
$result = (new \DbModel\Query('database_name'))->run($query);

foreach($result as $row) {
    foreach($row as $key => $value) {
        echo $key, ': ', $value;
    }
    echo '<br>';
}

parvezmrobin / db-model

Maintainers

Details