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.
We will select all the products from a database table and display them in an html table. This is also an example of how to pass data from the Controller to the view in CodeIgniter.
You have to create a Model to select all products from the database and write a Controller function to get details from the database to display them in a view.
Below is a screenshot of the page in which products are displayed.
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 the table structure is the same as above. Also, make sure all rows are populated. I have just inserted a few rows in the table for display purposes. We are not going to update any data in this table. Below are the rows in this table:
Model (ModelProduct.php)
We will select all rows from the product table. So, our model will be simple and have a method to select data from the 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 selects all rows from the product table with an order by clause using product_id. It returns the result set to the controller.
Controller (ProductController.php)
Our controller method will call the get_products()
method of the model. The 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.
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 for-each 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.
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 the MySQL database. Those new to CodeIgniter can use this as a starting point to use a database in CodeIgniter.
Post a Comment