If you are a beginner and just thinking about creating a simple web application using CodeIgniter 3, this topic is right for you. This topic is about how to display data from MySQL database in an html table using CodeIgniter 3.
All we will be doing is selecting all the products from a database table and displaying them in an html table. This is also an example of how you can pass data from the Controller to the view in CodeIgniter.
You have to create a Model to select all products from the database, create a Controller function that will get details from the database and send them to a view. Then you need to create the view and display the data.
Below is a screenshot of the page in which products are displayed.
Step 1 - Create a MySQL table to store product details
Create the table named 'product' in MySQL database as given below:
Table: product
This table stores details of all products, such as product id, product name, price etc.
Below are the columns in this table.
- product_id - Primary key and auto incremented id of the product
- product_name - Name of the product
- price - Price of the product
- 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.
CREATE TABLE `product` (
`product_id` int(11) NOT NULL,
`product_name` varchar(200) NOT NULL,
`price` decimal(12,2) NOT NULL,
`stock` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `product` (`product_id`, `product_name`, `price`, `stock`) VALUES
(1, 'Sofa', '10000.00', 34),
(2, 'Dining Table', '6000.00', 100),
(3, 'Dining Chair(set of 4)', '5000.00', 30),
(4, 'Mattress', '8000.00', 111);
ALTER TABLE `product`
ADD PRIMARY KEY (`product_id`);
ALTER TABLE `product`
MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
After you create the table, verify if table structure is same as above. Also make sure all rows are populated. I have just inserted few rows in the table for displaying purpose. We are not going to update any data in this table. Below are the rows in this table:
Step 2 - Model (ModelProduct.php)
We will simply select all rows from product table. So, our model will be very simple with a method to select data from product table and return the result set. Below is the model code, keep ModelProduct.php in application/models folder.
application/models/ModelProduct.php
<?php
class ModelProduct extends CI_Model {
function __construct() {
parent::__construct();
}
function get_products() {
$this->db->select('*');
$this->db->from('product');
$this->db->order_by('product_id');
$query = $this->db->get();
return $query->result();
}
}
?>
It just selects all rows from product table with an order by clause using product_id. It returns the result set to the controller.
Step 3 - Controller (ProductController.php)
Our controller method will simply call the get_products() method of the model. Controller will get product data and pass this data to the view. Let's see the code.
application/controllers/ProductController.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class ProductController extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->model('ModelProduct');
}
public function index() {
$data['product_data'] = $this->ModelProduct->get_products();
$this->load->view('product',$data);
}
}
In index() method we are just calling the get_products() method of the model.
Step 4 - View (product.php)
In the view we will create an html table and use the data sent by the controller to display them as table rows using a foreach loop.
application/views/product.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Display Products</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="<?php echo base_url('assets/css/style.css') ?>">
</head>
<body>
<div class="container">
<h2>List of Products</h2>
<div class="table-responsive">
<table class="table table-bordered table-hover">
<thead>
<tr><th>Product Id</th><th>Product Name</th><th>Price (INR)</th><th>Stock</th>
</tr>
</thead>
<?php if (!empty($product_data)) {
foreach ($product_data as $row){ ?>
<tr>
<td><?php echo $row->product_id; ?></td>
<td><?php echo $row->product_name; ?></td>
<td><?php echo $row->price; ?></td>
<td><?php echo $row->stock;?></td>
</tr>
<?php }
}
else
echo '<td colspan = "5"> No Products Available</td><tr>'; ?>
</table>
</div>
</div>
</body>
</html>
You can see we have an html table and we are displaying data in it using a foreach loop on $product_data which was sent by the controller.
Let me give you the stylesheet also below:
h2{
text-align:center;
margin-top: 20px;
}
table>thead{
font-size:13px;
background-color:#154360;
color:#fff;
}
table{
text-align: center;
}
We just added few simple styles here, you can always add more and better styles.
Step 5 - Update CodeIgniter configuration files and Test the Application
We need to update some setup files under config folder.
- Update
autoload.php (application/config/autoload.php)
Add 'database' in autoload libraries.
$autoload['libraries'] = array('database');
- Update config file (application/config/config.php)
Our project root folder name is "products". So, set 'base_url' and 'index_page' as below in this file:
$config['base_url'] = 'http://localhost/products/';
$config['index_page'] = '';
- Update database setup file (application/config/database.php)
Update database name and userid/password in this file.
- Update routes file (application/config/routes.php)
Update
routes.php to use the default controller which is ProductController.
$route['default_controller'] = 'ProductController';
Here I only updated default_controller as ProductController.
- Update
.htaccess (hypertext access) file to remove index.php from the URL
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?/$1 [L]
Conclusion
This was an example of a simple CodeIgniter application using MySQL database. Those who are new to CodeIgniter, can use this as starting point to use a database in CodeIgniter. Hope it was useful for you.
Development Steps