Simple Database connection in CI4 with select query example

We can simply configure the database credentials in the file app/Config/Database.php as below. Edit the credentials as per your settings for database.

/**
     * The default database connection.
     */
    public array $default = [
        'DSN'          => '',
        'hostname'     => 'localhost',
        'username'     => 'ci4_user',
        'password'     => 'ci4123',
        'database'     => 'ci4',
......

This database is automatically accessible in CI4 through any model. Let us look at an example.Create a table and insert some random records to read.

CREATE TABLE Persons (
    id int NOT NULL AUTO_INCREMENT,
    firstname varchar(50),
    lastname varchar(50),
    age int,
    address varchar(255),
    pincode int(11),
    created_date datetime,
    PRIMARY key(id)
);

INSERT INTO `Persons` (`firstname`,`lastname`,`age`,`address`,`pincode`)
VALUES
  ("Quamar","Avila","23","Bình Phước, Germany","773499"),
  ("Yoko","Clarke","40","Bellstown South Africa","361356")

We will create a Model file under app/Models/UserModel.php with the contents as below.

<?php

namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model{
    protected $table      = 'Persons';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;  //whether the mentioned primaryKey is an autoincrement field

    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_date';
}

?>

We set the table name in the $table variable.; primary key column name with $primaryKey variable. These are inbuilt variables that CI4 will use to perform actions automatically.

As you can see we need not write a select query or function. we can simply use the inbuilt function findAll to fetch all records from the table. We will write a controller for this.

<?php

namespace App\Controllers;

use App\Models;

class Home extends BaseController
{
    public function index()
    {
        $userModel =  new \App\Models\UserModel();
        $allPersons = $userModel->findAll();
        
        $data["users"] = $allPersons;
        return view('users/home',$data);

    }
}

The findAll function automatically fetches the records from the table mentioned in the model.
Finally we will write a view file to print our records under app/Views/users/home.php .

<?php
echo "Printing user names:";
foreach($users as $u) {
    echo  "<br/>".$u["firstname"]." ".$u["lastname"];
}
?>

When we run on the local machine we can see that the user names are printed.