Install

It's powerfull lib to work with DB, based on PDO. It doesn't have dependency to a framework. So, you can use it for any PHP project - actually, that's why the lib was created. Also, it support multi-connection logic, in case needs to connect to different DBs in a script.

Install

Run composer require webxid/edmo
Set DB congif

$default_config = [
    WebXID\EDMo\DB::DEFAULT_CONNECTION_NAME => [
        'host' => 'localhost',
        'port' => '3306',
        'user' => 'root',
        'pass' => '',
        'db_name' => 'db_name',
        'use_persistent_connection' => false,
	'charset' => 'utf8mb4',
    ],
];

WebXID\EDMo\DB::addConfig($default_config);

Feel free to use the lib 😉

HOW TO USE

DB Request

If needs to connect to custom DB, use method connect('connection_name'). Without connect('connection_name'), it will connect to DB::DEFAULT_CONNECTION_NAME . It works for all query types (query, update, insert, replace, delete, last_insert_id)

SELECT Query

$rules = [
    1, 2, 3, 4, 5,
];
$query = '    
    SELECT id, name
    FROM users
    WHERE id = :id
        AND rule IN (:rule)
    LIMIT 10
';

//Connecting to default DB
$users = WebXID\EDMo\DB::query($query)
    ->binds([
        ':id' => $id,
        ':rule' => $rules,
    ])
    ->execute()
    ->fetchArray();

//Connecting to custom DB
$old_users = WebXID\EDMo\DB::connect('archive')
    ->query($query)
    ->binds([
        ':id' => $id,
        ':rule' => $rules,
    ])
    ->execute()
    ->fetchArray();

UPDATE Query

//Connecting to default DB
WebXID\EDMo\DB::update('users')
    ->values([
        'rule' => 6,
    ])
    ->where('rule IN (:rule)')
    ->binds([
        ':rule' => $rules,
    ])
    ->execute();

INSERT Query

//Connecting to default DB
WebXID\EDMo\DB::insert('users')
    ->values([
        'rule' => 6,
    ])
    ->execute()
    ->lastInsertId();

WebXID\EDMo\DB::lastInsertId();

//Get last ID from custom DB
WebXID\EDMo\DB::lastInsertId('archive');

//Insert new and Update duplicated row
WebXID\EDMo\DB::insert('users', WebXID\EDMo\DB::DUPLICATE_UPDATE)
    ->values([
        'rule' => 6,
    ])
    ->execute();

REPLACE Query

//Connecting to default DB
WebXID\EDMo\DB::replace('users')
    ->values([
        'rule' => 6,
    ])
    ->execute();

DELETE Query

//Connecting to default DB
WebXID\EDMo\DB::delete('users')
    ->where('rule IN (:rule)')
    ->binds([
        ':rule' => $rules,
    ])
    ->execute();

CLEAN / destroy connections

//Destroy all connections
WebXID\EDMo\DB::clean();

//Destroy one connection, for example, with connection name 'archive'
WebXID\EDMo\DB::clean('archive');

WebXID\EDMo\DB::connect('archive')
    ->delete('users')
    ->where('rule IN (:rule)')
    ->binds([
        ':rule' => $rules,
    ])
    ->execute()
    ->clean();

SQL Transaction

//Open Transaction
$db = WebXID\EDMo\DB::connect()
    ->beginTransaction();

//Revert all changes, which happens inside Transaction
$db->rollbackTransaction();

//Commit all changes, which happens inside Transaction
$db->commitTransaction();

Query Builder

To get DB rows with conditions, passed to `where()` method

$rows = WebXID\EDMo\DB\Build::select([
        'column_1',
        'column_2',
    ])
    ->from('table_name')
    ->where(' column_1 = :column_1 ')
    ->binds([':column_1' => 123]) // pass here `where` condition value placeholders
    ->groupBy('column_1')
    ->having('column_2')
    ->orderBy('column_3', 'DESC')
    ->limit(5, 3)
    ->execute();

// This query is equals next string:
'
    SELECT column_1, column_2
    FROM table_name
    WHERE column_1 = :column_1
    GROUP BY column_1
    HAVING column_2
    ORDER BY column_3 DESC
    LIMIT 5, 15
';

To get DB rows with conditions, passed to `find()` method

$rows = WebXID\EDMo\DB\Build::select([
        'column_1',
        'column_2',
    ])
    ->from('table_name')
    ->find(['column_name' => 'column_value']) // You don't have to use method `binds()` - logic will do it automatically
    ->groupBy('column_1')
    ->having('column_2')
    ->orderBy('column_3', 'ASC')
    ->limit(5, 2)
    ->execute();

// This query is equals next string:
'
    SELECT column_1, column_2
    FROM table_name
    WHERE column_name = :column_name
    GROUP BY column_1
    HAVING column_2
    ORDER BY column_3 ASC
    LIMIT 5, 10
';

Using `where()` and `find()` in a query

$rows = WebXID\EDMo\DB\Build::select([
        'colunm_1',
        'column_2',
    ])
    ->from('table_name')
    ->where(' column_3 = 1 ')
    ->find(['column_4' => 123])
    ->execute();

// This query is equals next string:
'
    SELECT column_1, column_2
    FROM table_name
    WHERE (column_3 = 1) AND (column_4 = 123)
';

Query Request Data Container

This class uses to prepare SQL query data for request

$request = new WebXID\EDMo\DB\Request();

$request->relation = Request::RELATION_AND;
$request->operator = Request::MORE_EQUAL; //this is default value of condition operator
$request->column_conditions = [
    'taxonomy_id' => [
        Request::NOT_IN => 1,
        Request::IN => '2'

        Request::IS_NULL,
        Request::NOT_IS_NULL,
    ],
    'parent_id' => 1, // will use default condition operator: $request->operator
];

$request->order = [
    'parent_id' => Request::ORDER_BY_ASC,
    'type' => Request::ORDER_BY_DESC,
];

$request->execute();

print_r([
    $request->getWhere(),
    $request->getBinds(),
    $request->getOrderBy(),
    $request->getLimit(),
    $request->getRequestHash(),
]);

Models

Model with single Primary key

use WebXID\EDMo\Rules;

// Implement entity class `User`
class User extends WebXID\EDMo\AbstractClass\SingleKeyModel
{
    // Uses for update, insert and delete DB queries
    const TABLE_NAME = 'user';
    // Uses for `select` queries
    const JOINED_TABLES = 'user u
        LEFT JOIN role r ON r.role_id = u.role_id';

    protected static $pk_column_name = 'user_id';

    // Uses for update and insert queries
    protected static $columns = [
        'first_name' => 'string|50',
        'last_name' => 'string|50',
        'role_id' => 'int|11',
    ];
    // Uses for `select` queries
    protected static $joined_columns_list = [
        'u.user_id',
        'u.first_name',
        'u.last_name',
        'r.role_id',
        'r.title AS role_name',
    ];

    protected static $db_connection = false;

    // Fill this property to allow an object property on read
    protected static $readable_properties = [
        // 'readable_property_name' => true,
    ];
    // Fill this property to allow an object property on write
    protected static $writable_properties = [
        // 'writable_property_name' => true,
    ];
    
    /**
    * @inheritDoc
     */
    public static function getRules(): Rules
    {
        return Rules::make([
            'first_name' => Rules\Field::string([
                Rules\Type::itRequired(1),
                Rules\Type::minLen(1),
                Rules\Type::maxLen(50),
            ]),
            'role_id' => Rules\Field::int([
                Rules\Type::minLen(1),
                Rules\Type::maxLen(10),
            ]),
        ]);
    }
}

$user_id = User::addNew([
    'first_name' => 'Tony',
    'group_id' => 1,
]);

// To get data of single entity by Primary Key
$user = User::get($user_id);

$user->first_name = 'Jeck';
$user->group_id = 2;

$user->save();

// To get entity data by class `Entity`
$user_list_group_2 = User::find(['group_id' = 2]);
$full_user_list = User::all();

$user->delete();

// To get entity data by class `Entity`
$entity = WebXID\EDMo\DataProcessor::init(User::class);

$entity->find()->extract();
$entity->search()->extract();
$entity->all()->extract();

Model with Multiple Primary key

A entity does not have single primary key, it could has multi primary key or no one

use WebXID\EDMo\Rules;

// Implement entity class `Option`
class Option extends WebXID\EDMo\AbstractClass\MultiKeyModel
{
    // Uses for update, insert and delete DB queries
    const TABLE_NAME = 'option';
    // Uses for `select` queries
    const JOINED_TABLES = 'option o
        LEFT JOIN config c ON c.key = o.config_key';

    // Uses for update and insert queries
    protected $columns = [
        'key' => 'string|50',
        'value' => 'string|50',
    ];
    // Uses for `select` queries
    protected $joined_columns_list = [
        'o.key',
        'o.value',
        'u.last_name',
        'c.key AS config_key',
    ];
    protected $db_connection = false;
    // Fill this property to allow an object property on read
    protected static $readable_properties = [
        // 'readable_property_name' => true,
    ];
    // Fill this property to allow an object property on write
    protected static $writable_properties = [
        // 'writable_property_name' => true,
    ];
    
    /**
    * @inheritDoc
    */
    protected function getUniqueKeyConditions(): array
    {
        return [
            'key' => $this->key,
            'value' => $this->value,
        ];
    }
    
    /**
    * @inheritDoc
     */
    public static function getRules(): Rules
    {
        return Rules::make([
            'key' => Rules\Field::string([
                Rules\Type::itRequired(1),
                Rules\Type::minLen(1),
                Rules\Type::maxLen(50),
            ]),
            'value' => Rules\Field::string(),
        ]);
    }
}

// To get entity list
$limit = 1;
$page = 3;

$users_list = Option::all($limit, $page);

// To add new Entity into DB
$user = Option::addNew();

$user->key = 'some_option';
$user->value = 'Smitt';

$user->save();

// Or you can use bulk data loading to add the data
$user = Option::addNew()
    ->save([
        'key' => 'some_option',
        'value' => 'Smitt',
    ]);

// To get entity collection data by class `Grabber`
$entity_collection = WebXID\EDMo\DataProcessor::init(Option::class);

Entities

The class works only with classes that extend abstract class \WebXID\EDMo\AbstractClass\Collection

To init entity collection instance

// class `User` has to extend class `\WebXID\EDMo\AbstractClass\Collection`
$users = WebXID\EDMo\DataProcessor::init(User::class);

Search entity

// To get data without any condition
	$users->all()
    ->orderBy(string)
    ->groupBy(string)
    ->limit(int, int)
    ->extract();

// To find users by simple conditions
$users->find(['permission' => '10'])
    ->orderBy(string)
    ->groupBy(string)
    ->limit(int, int)
    ->extract(); // return users with `permission` = 10

// To find users by complex condition
$users->search('first_name != :first_name', [':first_name' => 'Sam'])
    ->orderBy(string)
    ->groupBy(string)
    ->limit(int, int)
    ->extract();  // return users with `first_name` != 'Sam'

Delete entity

$users->delete('id = :id', [':id' => 1]); // removes row with id = 1

Add new entity

// To add new entity data - can be used only for entities with single Primary key
$new_user = $users->addNew();

$new_user->first_name = 'John';
$new_user->last_name = 'Smitt';

$last_insert_id = $new_user->save(); // returns last_insert_id if table has autoincrement

// Or you can use bulk data loading to add the data
$last_insert_id = $users->addNew($user_id)
    ->save([
        'name' => 'Mark',
    ]);

Update entity data

$new_user = WebXID\EDMo\DataProcessor::init(User::class)
    ->update('user_id = :id')
    ->binds([':id' => $user_id]);

$new_user->name = 'Mark';

$new_user->save();

// Or you can use bulk data loading to add the data
WebXID\EDMo\DataProcessor::init(User::class)
    ->update('user_id = :id')
    ->binds([':id' => $user_id])
    ->save([
        'name' => 'Mark',
    ]);

Data Validation

// Init class instance
$data = Validation::rules();
$test_value = 'some value';

Check value with string type

// Custom string
$data->string($test_value, 'Wrong value type')
    ->required('String is required')
    ->minLen(mb_strlen($test_value), 'Invalid min len')
    ->maxLen(mb_strlen($test_value), 'Invalid max len')
    ->regexp('/^[a-zA-Z ]+$/', 'Invalid regexp')
    ->equals($test_value, 'Invalid equals')
    ->notEquals('Hello world!', 'Invalid not equals')
    ->enumValues([$test_value], 'Invalid enum');

// Check email value
$data->email($test_value, 'Invalid email');

// Check IP address
$data->ipAddress($test_value, 'Invalid IP Address');

Check value with numeric type

// Check integer value
$data->int($test_value, 'Wrong value type')
    ->required('Int is required')
    ->minLen(mb_strlen($test_value), 'Invalid min len')
    ->maxLen(mb_strlen($test_value), 'Invalid max len')
    ->minValue(100, 'Invalid min value')
    ->maxValue(200, 'Invalid max value')
    ->regexp('/^[a-zA-Z ]+$/', 'Invalid regexp')
    ->equals($test_value, 'Invalid equals')
    ->notEquals('Hello world!', 'Invalid not equals')
    ->enumValues([$test_value], 'Invalid enum');

// Check float value
$data->float($test_value, 'Wrong value type');

Check is value valid

if (!$data->isValid()) {
    print_r($data->getError()); // Print all error messages
}

Name		Name	Last commit message	Last commit date
Latest commit History 31 Commits
src		src
tests		tests
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
composer.json		composer.json
phpunit.xml.dist		phpunit.xml.dist

License

webxid/EDMo

Folders and files

Latest commit

History

Repository files navigation

Install

HOW TO USE

DB Request

SELECT Query

UPDATE Query

INSERT Query

REPLACE Query

DELETE Query

CLEAN / destroy connections

SQL Transaction

Query Builder

To get DB rows with conditions, passed to where() method

To get DB rows with conditions, passed to find() method

Using where() and find() in a query

Query Request Data Container

Models

Model with single Primary key

Model with Multiple Primary key

Entities

To init entity collection instance

Search entity

Delete entity

Add new entity

Update entity data

Data Validation

Check value with string type

Check value with numeric type

Check is value valid

About

Resources

License

Stars

Watchers

Forks

Languages

To get DB rows with conditions, passed to `where()` method

To get DB rows with conditions, passed to `find()` method

Using `where()` and `find()` in a query