How to build a CodeIgniter 4 application using MySQL database

If you are a beginner in CodeIgniter 4 and want to create a simple application to start with, this this topic will help you. We will just display data from database in a html table using CodeIgniter 4.

In this topic, we will develop a CodeIgniter 4 application to get data from MySQL table and display them. This is the simplest example of developinhg a CodeIgniter 4 application using MySQL database. We are not going to show how to install CodeIgniter 4 as I have already covered it in another topic. If you want you can read the topic How to download and Install CodeIgniter 4 with setup for XAMPP server

CodeIgniter 4 and MySQL

How to use MySQL DB in CodeIgniter 4Folders and Files

I have created a folder named 'projects' in D: drive and installed CodeIgniter4 in product folder under projects. So, the root directory of this project is D:/projects/product/.

display data from database in CodeIgniter 4

The folder 'assets' is created under public to keep all stylesheets, JavaScripts and image files. Here I have style.css which is kept under assets/css folder. Just make sure you give correct path for the css file in the view. For our case it is added in the view product.php as below:

If you are using XAMPP and want to use Apache Web Server instead of PHP built-in server, then you can install CodeIgniter 4 under xampp/htdocs/<your folder name>. In our case this folder name is product. I will show necessary configuration changes and testing the application for both the web servers.

Step 1 - Create a MySQL table to store product details

Let us create the table named 'product' in MySQL database. Table structures is given below:

Table: product

This table stores details of all products, such as product id, product name, price etc.

codeigniter 4 database query

Below are the columns in this table.

  1. product_id - It is the primary key and auto incremented id of the product
  2. product_name - Name of the product
  3. price - Price of the product
  4. stock - Stock available for the product
Create table script and insert statements for this table are given below; you can use this code to create the table and populate data in it.

product.sql

After you create the table, just verify if table structure is same as above. Also make sure all rows are populated. I have inserted few rows in the table for displaying purpose. Below are the rows in this table:

codeigniter 4 get data from database

Step 2 - Write Model code (ModelProduct.php)

We will simply select all rows from product table. So, our model will be very simple with a function to select data from product table and return the result set. Below is the model code, keep ModelProduct.php in app/Models folder.

app/Models/ModelProduct.php

I have connected to MySQL database in the constructor using db_connect(). Database setup and other configuration changes are discussed in step 5.

ModelProduct is the class where we write all methods to do database operations. Here, I created a class named ModelProduct with a method named getProducts(). getResult() gives all the rows from the table as a collection. It returns the result set to the controller.

Step 3 - Write Controller code (Product.php)

Our controller function will simply call the getProducts() method of the model. Controller gets product data and loads the view with this data. Let's see the code.

app/Controllers/Product.php

We need to create an instance of the model which is done in the constructor. Then in index() method I am just calling the getProducts() method of the model. Next, we will see our view product.php.

Step 4 - Write View (product.php)

In the view I will define a html table and use the data sent by the controller to display them as table rows using a foreach loop.

app/Views/product.php

You can see I have a html table and I am displaying data in it using a foreach loop on $product_data which was sent by the controller. Here esc() function is used to prevent XSS attacks.

Note that custom stylesheet style.css is in public/assets folder.

Below is the custom stylesheet used:

public/assets/css/style.css

I just added few simple styles here, you can always add more with better styles.

Step 5 - Update CodeIgniter 4 Configuration files and Test the Application

Let us now update few setup files before we run the application.

We will update below files in Config folder and also, we will have .env file updated in the root directory. See below:

connect CodeIgniter 4 to database

  1. Update config file (app/Config/App.php)

    Below is a section of App.php, set $baseURL and $indexPage as given below:

  2. Update database setup file (app/Config/Database.php)

    As I mentioned before, I am using a database named 'demo' in localhost with user 'root', so below is our database connection setup in Database.php. Update $default as below:

  3. Update Routes file (app/Config/Routes.php)

    In Routes.php change default controller as Product. So below code is for Routes.php

    Also change below line to include Product controller.

    To
  4. Update .env to set the environment

    We need to set the environment to development in the environment (.env) file. This file is in root directory.

    connect to MySQL database in CodeIgniter

    If you have not already copied env file to .env file, make a copy of env to .env. Open .env file in notepad and go to the line containing "# CI_ENVIRONMENT = production". Now copy this line and paste it as next line, then update the environment from production to development and remove # from the beginning of the line. See below:

Test The Application

You can test the application using built-in PHP server or you can use Apache web server if you are using xampp.

Test using PHP built-in server

Open command prompt and go to your CI4 project root. In my case it is D:/projects/product. Then type php spark serve and enter. This will start the server in port 8080.

fetch data from database in CodeIgniter 4 and display in table

Now open the browser and run http://localhost:8080/. This will open the product list page. All products will be displayed in the page as below:

codeigniter 4 tutorial

You can verify from the product table if all products are displayed correctly.

Test using Apache web server

If you are using XAMPP make sure your root folder is xampp/htdocs/product, i.e., you have CI4 installed in this folder. Open httpd-vhosts.conf file in notepad from xampp\apache\conf\extra folder.

codeigniter 4 database example

Open the file in notepad and add below line at the end of it.

DocumentRoot "D:/xampp/htdocs/product/public"

use MySQL DB in CodeIgniter 4

Save it and restart the Apache service from xampp controller.

Now in app/Config/App.php, update base url as below:

Open the browser and run http://localhost. You will see the home page as displayed below:

display data from database in CodeIgniter 4

You will see the list of products displayed in a html table. Check with database table 'product' if all products are displayed.

That's it! Our development and testing are done for this simple CodeIgniter4 application. Hope you could successfully develop and test it.

Read Data from Database with CodeIgniter 4 ModelDownload Source Code

I have put codes for controller, model and view in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can use the code as is or you can modify them as per your requirements.

an application in CodeIgniter 4 and MySQLConclusion

This was an example of simple CodeIgniter 4 application using MySQL database. If you are new to CodeIgniter, you can use this as starting point to use a database in CodeIgniter 4. Hope it was useful topic for you.