bertugfahriozer / ci4commonmodel
With the codeigniter 4 database builder, I combined the general methods I used for myself into a single model.
Requires
- php: ^7.4||^8.0
README
CommonModel
is a versatile and reusable model for CodeIgniter 4, designed to simplify common database operations such as selecting, inserting, updating, and deleting records. This library provides methods that support common SQL features like JOINs, WHERE conditions, LIKE filters, ordering, and more.
Features
- Multiple Database Connection Groups
- Select records with flexible conditions (
WHERE
,OR WHERE
,LIKE
) - Insert single or multiple records into the database
- Update and delete records based on conditions
- Join tables for more complex queries
- Supports ordering and pagination
- Easy counting and existence checks for records
- Built-in support for like queries and batch operations
- Table and column management (add, remove, modify)
- Database creation and deletion
Table of Contents
- CommonModel Library for CodeIgniter 4
- Features
- Table of Contents
- Installation
- Usage
- Troubleshooting: Multiple Database Connection Groups
- License
- Author
Installation
To use CommonModel
in your CodeIgniter 4 project, follow these steps:
-
Install with Composer:
composer require bertugfahriozer/ci4commonmodel
-
Load the model in your controller:
use ci4commonmodel\Models\CommonModel; class ExampleController extends BaseController { protected $commonModel; public function __construct() { $this->commonModel = new CommonModel(); } }
Usage
1. Retrieving Records (lists
)
Fetch records from a database table with flexible filters such as WHERE
, OR WHERE
, LIKE
, JOIN
, and ordering. Supports limit and pagination.
// Simple usage $users = $this->commonModel->lists('users', '*', ['status' => 1], 'id DESC', 10); // Advanced usage with JOIN and LIKE $joins = [ ['table' => 'roles', 'cond' => 'users.role_id = roles.id', 'type' => 'left'] ]; $like = ['name' => 'John']; $users = $this->commonModel->lists('users', 'users.*, roles.name as role', ['status' => 1], 'users.id DESC', 10, 0, $like, [], $joins);
2. Inserting Records (create
)
Insert a single record into the database and return the newly inserted ID.
$data = [ 'name' => 'John Doe', 'email' => 'john@example.com', 'status' => 1 ]; $insertId = $this->commonModel->create('users', $data);
3. Batch Insert (createMany
)
Insert multiple records at once.
$data = [ ['name' => 'Alice', 'email' => 'alice@example.com'], ['name' => 'Bob', 'email' => 'bob@example.com'] ]; $this->commonModel->createMany('users', $data);
4. Updating Records (edit
)
Update existing records by specifying the WHERE conditions and the new data.
$data = ['status' => 2]; $where = ['id' => 1]; $this->commonModel->edit('users', $data, $where);
5. Deleting Records (remove
)
Delete records from a table based on WHERE conditions.
$where = ['id' => 1]; $this->commonModel->remove('users', $where);
6. Count Records (count
)
Count the number of records that match a given condition.
$where = ['status' => 1]; $count = $this->commonModel->count('users', $where);
7. Check Record Existence (isHave
)
Check whether a record exists in a table with a specified condition.
$where = ['id' => 1]; $isExist = $this->commonModel->isHave('users', $where);
8. Complex Queries (research
)
Search records using LIKE queries and filtering by conditions.
$like = ['name' => 'John']; $where = ['status' => 1]; $results = $this->commonModel->research('users', $like, '*', $where);
9. Table and Database Management
Get Table List
$tables = $this->commonModel->getTableList();
Create a New Table
$fields = [ 'id' => [ 'type' => 'INT', 'constraint' => 5, 'unsigned' => true, 'auto_increment' => true, ], 'title' => [ 'type' => 'VARCHAR', 'constraint' => '100', 'unique' => true, ] ]; $this->commonModel->newTable('blog', $fields);
Remove a Table
$this->commonModel->removeTable('blog');
Add Column to Table
$fields = [ 'preferences' => ['type' => 'TEXT', 'after' => 'email'], ]; $this->commonModel->addColumnToTable('users', $fields);
Remove Column from Table
$this->commonModel->removeColumnFromTable('users', ['preferences']);
Rename Table
$this->commonModel->updateTableName('old_table', 'new_table');
Modify Column Info
$fields = [ 'old_name' => [ 'name' => 'new_name', 'type' => 'TEXT', 'null' => false, ], ]; $this->commonModel->modifyColumnInfos('users', $fields);
Truncate Table
$this->commonModel->emptyTableDatas('users');
Get Table Fields
$fields = $this->commonModel->getTableFields('users');
Create a New Database
$this->commonModel->newDatabase('new_db');
Remove a Database
$this->commonModel->removeDatabase('old_db');
Drop Primary Key
$this->commonModel->drpPrimaryKey('users');
Drop Key
$this->commonModel->drpKey('users', 'my_key_name');
Drop Foreign Key
$this->commonModel->drpForeignKey('orders', 'fk_orders_users');
Troubleshooting: Multiple Database Connection Groups
When working with multiple database connections in CodeIgniter 4, defining them in the .env
file is not sufficient on its own. Here's an example configuration in .env
:
database.default.hostname=127.0.0.1 database.default.database=giritliadali database.default.username=root database.default.password=root database.default.DBDriver=MySQLi database.default.port=3306 database.secondDB.hostname=127.0.0.1 database.secondDB.database=giritliadali_bac database.secondDB.username=root database.secondDB.password=root database.secondDB.DBDriver=MySQLi database.secondDB.port=3306
Attempting to connect using:
$this->db = \Config\Database::connect('secondDB');
Will result in the error:
"secondDB" is not a valid database connection group.
Root Cause
CodeIgniter 4 does not auto-load database groups other than default
from the .env
file. You must explicitly define custom groups like secondDB
in the app/Config/Database.php
file.
Solution
To fix this, add a new public array in your app/Config/Database.php
:
public array $secondDB = [ 'DSN' => '', 'hostname' => 'localhost', 'username' => '', 'password' => '', 'database' => '', 'DBDriver' => 'MySQLi', 'DBPrefix' => '', 'pConnect' => false, 'DBDebug' => true, 'charset' => 'utf8mb4', 'DBCollat' => 'utf8mb4_general_ci', 'swapPre' => '', 'encrypt' => false, 'compress' => false, 'strictOn' => false, 'failover' => [], 'port' => 3306, 'numberNative' => false, 'foundRows' => false, 'dateFormat' => [ 'date' => 'Y-m-d', 'datetime' => 'Y-m-d H:i:s', 'time' => 'H:i:s', ], ];
Once added, \Config\Database::connect('secondDB')
will work properly.
License
This project is licensed under the MIT License. See the LICENSE file for more details.