CodeIgniter 4 SQL-Server Simple CRUD Step-by-Step Tutorial

CodeIgniter 4 SQL-Server Simple CRUD Step-by-Step Tutorial

CodeIgniter is an open-source web framework that is used for rapid web development. CodeIgniter follows the MVC (Model-View-Controller) architectural pattern. It is noted for its speed compared to other PHP web frameworks.

Application Flow Chart

1. The index.php serves as the front controller, initializing the base resources needed to run CodeIgniter.
2. The Router examines the HTTP request to determine what should be done with it.
3. If a cache file exists, it is sent directly to the browser, bypassing the normal system execution.
4. Security. Before the application controller is loaded, the HTTP request and any user submitted data is filtered for security.
5. The Controller loads the model, core libraries, helpers, and any other resources needed to process the specific request.
6. The finalized View is rendered then sent to the web browser to be seen. If caching is enabled, the view is cached first so that on subsequent requests it can be served.

CRUD is an acronym for CREATE, READ, UPDATE, DELETE. The four basic functions in any type of programming. 

CRUD typically refers to operations performed in a database.

• Create -Generate new record/s.
• Read – Reads or retrieve record/s.
• Update – Modify record/s.
• Delete – Destroy or remove record/s.

Before proceeding on this tutorial, you must have an environment already setup to run and install CodeIgniter 4. 

If you haven’t, please do read my blog: https://interesting-homemade-projects.heliohost.us/post.php?slug=installing-codeigniter-php-framework-in-iis

Set Database Configuration

Run this SQL query to create the crud_projects database 

✂️ Copy and 📋 Paste 👇 

CREATE DATABASE crud_projects;

Run this SQL query to create the crud_data table:

✂️ Copy and 📋 Paste 👇 

CREATE TABLE crud_data (

    id INT IDENTITY(1,1) PRIMARY KEY,

    name NVARCHAR(255) NOT NULL,

    phone NVARCHAR(15) NOT NULL,

    address NVARCHAR(255) NOT NULL,

    created_date DATETIME NULL DEFAULT GETDATE(),

    updated_date DATETIME NULL DEFAULT NULL

);

Install PHP SQLSRV Driver:

✅ How to Install Microsoft Drivers for PHP and SQL-SERVER

⚠️ Important Note:

⚙️ Don't forget that before continuing, SQL-SERVER must have network access enabled.

Open the app folder and configure your database on this file “Config/database.php“.

✂️ Copy and 📋 Paste 👇 

public array $default = [

    'DSN'        => '',

    'hostname'   => 'localhost', //e.g., 'localhost' or '192.168.1.100'

    'username'   => 'your_sql_server_username',

    'password'   => 'your_sql_server_password',

    'database'   => 'crud_projects',

    'schema'     => 'dbo',

    'DBDriver'   => 'SQLSRV',   //Specify the SQLSRV driver

    'DBPrefix'   => '',

    'pConnect'   => false,

    'DBDebug'    => true,

    'charset'    => 'utf8',

    'swapPre'    => '',

    'encrypt'    => false,

    'failover'   => [],

    'port'       => 1433,       //Default SQL-Server port, modify if yours is different

    'dateFormat' => [

        'date'     => 'Y-m-d',

        'datetime' => 'Y-m-d H:i:s',

        'time'     => 'H:i:s',

    ],

];

Update Config/App.php

Open the file Config/App.php and find and update the base_url value.

Remove "index.php" in Config/App.php

Update Register Routes

And now we will register the routes for our CRUD app.

Callable Syntax (Example):

$routes->get('/', [\App\Controllers\Home::class, 'index']);

Open the Config/routes.php file and register these route:

✂️ Copy and 📋 Paste 👇 

 

<?php

use CodeIgniter\Router\RouteCollection;

/**

 * @var RouteCollection $routes

 */

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

$routes->get('crud', 'Crud::index');

$routes->get('crud/create', 'Crud::create_page');

$routes->post('crud/save', 'Crud::save_record');

$routes->get('crud/show_page/(:num)', 'Crud::show_page/$1');

$routes->get('crud/edit/(:num)', 'Crud::edit_page/$1');

$routes->put('crud/update/(:num)', 'Crud::update_record/$1');

$routes->post('crud/update/(:num)', 'Crud::update_record/$1');

$routes->get('crud/delete/(:num)', 'Crud::delete_record/$1');

$routes->delete('crud/delete/(:num)', 'Crud::delete_record/$1');

Create Controller

Go to this path “application/controllers/” and create a controller named Crud.php. This will handle all our users actions. 

The controller will have these following methods

• index()
• show_page($id)
• create_page()
• save_record()
• edit_page($id)
• update_record($id)
• delete_record($id)

After creating the controller, insert the codes below:

application/controllers/Crud.php

✂️ Copy and 📋 Paste 👇 

<?php

/*-----------------------------------------------------------------------------------------------------

  ----------------------- This PHP Script was created by Luis A. Sierra -------------------------------

  -----------------------------------------------------------------------------------------------------*/

namespace App\Controllers;

use App\Models\CrudModel;

use CodeIgniter\Controller;

class Crud extends Controller

{

    protected $crud;

    protected $session;

    protected $validation;

    public function __construct()

    {

        $this->crud = new CrudModel();

        $this->session = session();

        $this->validation = \Config\Services::validation();

    }

    public function index()

    {

        $data = [

            'cruds' => $this->crud->getAll(),

            'title' => 'CodeIgniter 4 Crud for SQL-SERVER',

        ];

        return view('layout/header_view')

             . view('crud/index', $data)

             . view('layout/footer_view');

    }

    public function show_page($id)

    {

        $data = [

            'crud' => $this->crud->getById($id),

            'title' => 'Show Crud',

        ];

        return view('layout/header_view')

             . view('crud/show_view', $data)

             . view('layout/footer_view');

    }

    public function create_page()

    {

        $data['title'] = 'Create Crud';

        return view('layout/header_view')

             . view('crud/create_view', $data)

             . view('layout/footer_view');

    }

    public function save_record()

    {

        $rules = [

            'name' => 'required',

            'phone' => 'required',

            'address' => 'required',

        ];

        if (!$this->validate($rules)) {

            $this->session->setFlashdata('errors', $this->validator->listErrors());

            return redirect()->to(base_url('crud/create'));

        }

        $this->crud->saveRecord([

            'name' => $this->request->getPost('name'),

            'phone' => $this->request->getPost('phone'),

            'address' => $this->request->getPost('address'),

        ]);

        $this->session->setFlashdata('success', 'Saved Successfully!');

        return redirect()->to(base_url('crud'));

    }

    public function edit_page($id)

    {

        $data = [

            'crud' => $this->crud->getById($id),

            'title' => 'Edit Crud',

        ];

        return view('layout/header_view')

             . view('crud/edit_view', $data)

             . view('layout/footer_view');

    }

    public function update_record($id)

    {

        $rules = [

            'name' => 'required',

            'phone' => 'required',

            'address' => 'required',

        ];

        if (!$this->validate($rules)) {

            $this->session->setFlashdata('errors', $this->validator->listErrors());

            return redirect()->to(base_url("crud/edit/$id"));

        }

        $this->crud->updateRecord($id, [

            'name' => $this->request->getPost('name'),

            'phone' => $this->request->getPost('phone'),

            'address' => $this->request->getPost('address'),

        ]);

        $this->session->setFlashdata('success', 'Updated Successfully!');

        return redirect()->to(base_url('crud'));

    }

    public function delete_record($id)

    {

        $this->crud->deleteRecord($id);

        $this->session->setFlashdata('success', 'Deleted Successfully!');

        return redirect()->to(base_url('crud'));

    }

}

Create Model

Go to this path “application/models/” and create a model named CrudModel.php. This will handle all our query to the database. The model will have this following methods.

• getAll()
• saveRecord()
• getById($id)
• updateRecord($id)
• deleteRecord($id)

After creating the model, insert the code below:

application/models/CrudModel.php

✂️ Copy and 📋 Paste 👇 

<?php

/*-----------------------------------------------------------------------------------------------------

  ----------------------- This PHP Script was created by Luis A. Sierra -------------------------------

  -----------------------------------------------------------------------------------------------------*/

namespace App\Models;

use CodeIgniter\Model;

class CrudModel extends Model

{

    protected $table = 'crud_data';       // Database table name

    protected $primaryKey = 'id';         // Primary key column

    protected $allowedFields = ['name', 'phone', 'address']; // Editable fields

    protected $useTimestamps = false;     // Set true if you use created_at / updated_at fields

    protected $returnType = 'object';     // Return results as objects

    /*

        Get all records

    */

    public function getAll()

    {

        return $this->findAll();

    }

    /*

        Get a specific record by ID

    */

    public function getById($id)

    {

        return $this->find($id);

    }

    /*

        Insert new record

    */

    public function saveRecord(array $data)

    {

        return $this->insert($data);

    }

    /*

        Update record by ID

    */

    public function updateRecord($id, array $data)

    {

        return $this->update($id, $data);

    }

    /*

        Delete record by ID

    */

    public function deleteRecord($id)

    {

        return $this->delete($id);

    }

}

Create View Files

We will now create the view files. The Views are the codes that what the user see and the code that the user can interact. We will create all these files:

• layout/header_view.php
• layout/footer_view.php
• crud/index_view.php
• crud/create_view.php
• crud/edit_view.php
• crud/show_view.php

application/views/layout/header_view.php

✂️ Copy and 📋 Paste 👇 

<!DOCTYPE html>

<html>

<head>

    <title>CodeIgniter CRUD</title>

    <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.4.0/js/bootstrap.min.js" rel="stylesheet">

    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" rel="stylesheet"

integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">

</head>

<body>

    <div class="container">

application/views/layout/footer_view.php

✂️ Copy and 📋 Paste 👇 

    </div>

    <center>CodeIgniter CRUD Created by <b>Luis A. Sierra</b></center>

    </body>

</html>

application/views/crud/index.php

✂️ Copy and 📋 Paste 👇 

<h2 class="text-center mt-5 mb-3"><?php echo $title; ?></h2>

<div class="card">

    <div class="card-header">

        <a class="btn btn-outline-primary" href="<?php echo base_url('crud/create/');?>">

            Create New Record

        </a>

    </div>

    <div class="card-body">

        <?php if (session()->getFlashdata('success')) {?>

            <div class="alert alert-success">

                <?php echo session()->getFlashdata('success'); ?>

            </div>

        <?php } ?>

 

        <table class="table table-bordered">

            <tr>

                <th>Name</th>

                <th>Phone</th>

                <th>Address</th>

                <th width="240px">Action</th>

            </tr>

 

            <?php foreach ($cruds as $crud) { ?>      

            <tr>

                <td><?php echo $crud->name; ?></td>

                <td><?php echo $crud->phone; ?></td>  

                <td><?php echo $crud->address; ?></td>                  

                <td>

                    <a

                        class="btn btn-outline-info"

                        href="<?php echo base_url('crud/show_page/'. $crud->id) ?>">

                        Show

                    </a>

                    <a

                        class="btn btn-outline-success"

                        href="<?php echo base_url('crud/edit/'.$crud->id) ?>">

                        Edit

                    </a>

                    <a

                        class="btn btn-outline-danger"

                        href="<?php echo base_url('crud/delete/'.$crud->id) ?>">

                        Delete

                    </a>

                </td>    

            </tr>

            <?php } ?>

        </table>

    </div>

</div>

application/views/crud/create_view.php

✂️ Copy and 📋 Paste 👇 

<h2 class="text-center mt-5 mb-3"><?php echo $title; ?></h2>

<div class="card">

    <div class="card-header">

        <a class="btn btn-outline-secondary" href="<?php echo base_url('crud/');?>">

            Back

        </a>

    </div>

    <div class="card-body">

        <?php if (session()->getFlashdata('errors')) {?>

            <div class="alert alert-danger">

                <?php echo session()->getFlashdata('errors'); ?>

            </div>

        <?php } ?>

        <form method="post" action="<?php echo base_url('crud/save'); ?>">

            <div class="form-group">

                <label for="name">Name:</label>

                <input type="text" class="form-control" id="name" name="name" placeholder="Enter name" value="<?php echo old('name'); ?>">

            </div>

            <div class="form-group">

                <label for="phone">Phone:</label>

                <input type="text" class="form-control" id="phone" name="phone" placeholder="Enter phone" value="<?php echo old('phone'); ?>">

            </div>

            <div class="form-group">

                <label for="address">Address:</label>

                <textarea class="form-control" id="address" name="address" placeholder="Enter address"><?php echo old('address'); ?></textarea>

            </div>

            <button type="submit" class="btn btn-success">Save</button>

        </form>

    </div>

</div>

application/views/crud/edit_view.php

✂️ Copy and 📋 Paste 👇 

<h2 class="text-center mt-5 mb-3"><?php echo $title; ?></h2>

<div class="card">

    <div class="card-header">

        <a class="btn btn-outline-secondary" href="<?php echo base_url('crud/');?>">

            Back

        </a>

    </div>

    <div class="card-body">

        <?php if (session()->getFlashdata('errors')) {?>

            <div class="alert alert-danger">

                <?php echo session()->getFlashdata('errors'); ?>

            </div>

        <?php } ?>

        <?php if (!empty($crud)) { ?>

            <form method="post" action="<?php echo base_url('crud/update/'.$crud->id); ?>">

                <?= csrf_field() ?>

                <input type="hidden" name="_method" value="PUT">

                <div class="form-group">

                    <label for="name">Name:</label>

                    <input type="text" class="form-control" id="name" name="name" placeholder="Enter name" value="<?php echo old('name', $crud->name); ?>">

                </div>

                <div class="form-group">

                    <label for="phone">Phone:</label>

                    <input type="text" class="form-control" id="phone" name="phone" placeholder="Enter phone" value="<?php echo old('phone', $crud->phone); ?>">

                </div>

                <div class="form-group">

                    <label for="address">Address:</label>

                    <textarea class="form-control" id="address" name="address" placeholder="Enter address"><?php echo old('address', $crud->address); ?></textarea>

                </div>

                <button type="submit" class="btn btn-success">Update</button>

            </form>

        <?php } else { ?>

            <p class="alert alert-warning">Record not found.</p>

        <?php } ?>

    </div>

</div>

application/views/crud/show_view.php

✂️ Copy and 📋 Paste 👇 

<h2 class="text-center mt-5 mb-3"><?php echo $title; ?></h2>

<div class="card">

    <div class="card-header">

        <a class="btn btn-outline-secondary" href="<?php echo base_url('crud/');?>">

            Back

        </a>

    </div>

    <div class="card-body">

        <?php if (!empty($crud)) { ?>

            <div class="form-group">

                <strong>Name:</strong>

                <p><?php echo $crud->name; ?></p>

            </div>

            <div class="form-group">

                <strong>Phone:</strong>

                <p><?php echo $crud->phone; ?></p>

            </div>

            <div class="form-group">

                <strong>Address:</strong>

                <p><?php echo $crud->address; ?></p>

            </div>

        <?php } else { ?>

            <p class="alert alert-warning">Record not found.</p>

        <?php } ?>

    </div>

</div>

Finally all the components are done.

Now we can test our CodeIgniter 4 Simple CRUD App by browsing this URL:

http://localhost:8080/crud

📷 Screenshots:

CodeIgniter 4 Simple CRUD App:

Congratulations 😎👏