In this tutorial, I will show you how we can select, insert, update, and delete a record from the MySQL database in the CodeIgniter 4 project.

Contents

  1. Database configuration
  2. Create Table
  3. Model
  4. Route
  5. Controller
  6. View
  7. Run

1. Database configuration

  • Open the .env file found at the root of the project.

NOTE - If a dot (.) is not added at first then rename the file to .env.

  • Remove # from the beginning of the database.default.hostname, database.default.database, database.default.username, database.default.password, and database.default.DBDriver.
  • Review the suspension and save it.
database.default.hostname = 127.0.0.1
database.default.database = testdb
database.default.username = root
database.default.password = 
database.default.DBDriver = MySQLi


2. Create Table

  • Create a new table subjects using migration.
php spark migrate:create create_subjects_table

Now, navigate to the app/Database/Migration/ folder from the root of the project.

Find the PHP file ending in create_subjects_table and open it.

Describe the structure of the table in an up().

Using the down() method, delete subjects table which calls when undoing migration.

<?php namespace AppDatabaseMigrations;

use CodeIgniterDatabaseMigration;

class CreateSubjectsTable extends Migration
{
    public function up() {
       $this->forge->addField([
         'id' => [
            'type' => 'INT',
            'constraint' => 5,
            'unsigned' => true,
            'auto_increment' => true,
         ],
         'name' => [
            'type' => 'VARCHAR',
            'constraint' => '100',
         ],
         'description' => [
            'type' => 'TEXT',
            'null' => true,
         ],
       ]);
       $this->forge->addKey('id', true);
       $this->forge->createTable('subjects');
    }

    //--------------------------------------------------------------------

    public function down() {
       $this->forge->dropTable('subjects');
    }
}
  • Run the migration –
php spark migrate


3. Model

  • Create a Subjects.php file in the app/Models/ folder.
  • Open the file.
  • Specify the table name "subjects" for the $table variation, key "id" in the $primaryKey, Return type "array" in the $returnType .
  • In $allowedFields Array specify the field names - ['name', 'description'] can be set during installation and renewal.

Completed Code

<?php 
namespace AppModels;

use CodeIgniterModel;

class Subjects extends Model
{
    protected $table = 'subjects'; 
    protected $primaryKey = 'id';

    protected $returnType = 'array';

    protected $allowedFields = ['name', 'description'];
    protected $useTimestamps = false;

    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;

}


4. Route

  • Open the application / Config / Routes.php.
  • Define 6 -

              / - Display a list of topics.

              lessons/create - Open title view.

              courses/shop - Submit a course form to enter a record.

              lessons/edit/(: num) - Open title view with id.

              lessons/refresh/(: num) - Submit the editing form to update the record with id.

              lessons/delete/(: num) - Delete subject with id.

Completed Code

$routes->get('/', 'SubjectsController::index');

$routes->get('subjects/create', 'SubjectsController::create');
$routes->post('subjects/store', 'SubjectsController::store');

$routes->get('subjects/edit/(:num)', 'SubjectsController::edit/$1');
$routes->post('subjects/update/(:num)', 'SubjectsController::update/$1');

$routes->get('subjects/delete/(:num)', 'SubjectsController::delete/$1');


5. Controller

  • Create a SubjectsController.php file in the app/Controllers/ folder.
  • Open the file.
  • Import subjects Model.
  • Create 6 ways -

              index () - Select all records in the subjects table and provide $data['subjects']. Upload subjects/index views and pass $data.

             create () - This way upload subjects/create a view to add a new topic.

             store () - This way add a new record to the subjects table.

             edit () - This way uploads to edit the title view. Select a record from the subjects table with $id and assign to $data['subject']. Upload subjects/edit views and pass $data.

            update () - This way update the record in the subjects table.

            delete () - This way delete the record in the subjects of articles with $id.


6. View

Create layouts and subjects folder at app/Views/.

Create the following files in the folders –

  • layouts

                layout.php

  • subjects

               index.php

               create.php

               edit.php


7. Run

  • Navigate to project using Command Prompt if you are on Windows or terminal if you are on Mac or Linux, and
  • Create the command "php spark serve".
  • Run http://localhost:8080 in the web browser.