Generate HTML Table from MySQL database using Codeigniter

Here is an example showing you how to generate HTML table from MySQL database using Codeigniter. We will write here an example to create a table from a multi-dimensional array. The Table Class provides functions that enable you to auto-generate HTML tables from arrays or database result sets.

Here we will create a web application to generate HTML table from MySQL database using Codeigniter. We will create a table in MySQL database and retrieve the records from the table and display on a view in table format.

Prerequisites

Knowledge of PHP & Codeigniter 3
Knowledge of MySQL or any other database

Now we will implement the example on generate html table from MySQL database using Codeigniter using the following steps

First thing is to create a database table in MySQL database. In this example the database table will contain date wise sales information for products. Therefore we will create a table called products in MySQL database.

CREATE TABLE `products` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`price` double NOT NULL,
	`sale_price` double NOT NULL,
	`sales_count` int(10) unsigned NOT NULL,
	`sale_date` varchar(10) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Run the above table creation script into the MySQL server and a table products will be created. You may have multiple tables with normalization but here for example purpose the above table would be enough to show an idea how to generate HTML table from MySQL database using Codeigniter.

In order to test the example, we need some data in the above table. So insert some dummy data into the products table.

insert into products(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','04-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34040','58','05-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-04-2018');
insert into products(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-04-2018');

Now we will move to Codeigniter part and for this either you may use any editor like notepad or notepad++ or you may use any IDE to develop your application code.

If you want to use Netbeans IDE for working with Codeigniter then please read here how to configure Codeigniter, XAMPP with Netbeans.

Now auto-load database library and few helper functions because these are one time activity and you may not want to load every time you want to query database or working with URL, file, form, text etc. We have also auto-loaded table library in order to generate table from MySQL database using Codeigniter. Edit the appropriate sections in the file <project directory>/application/config/autoload.php.

$autoload['libraries'] = array('database', 'table');
$autoload['helper'] = array('url', 'file', 'text', 'form');

Now configure the database section in order to connect with MySQL database for performing database activities in the file <project directory>/application/config/database.php. Please replace below values with your own values.

$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'roytuts';

We will generate HTML table from MySQL database and Codeigniter provides a built-in functionality for generating such HTML table. So we do not need to use any third party library for this.

Now we will create model class in order to fetch data from database table called products. Create a php file product_model.php under application/models directory with below code.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');
	
/**
* Description of Product_model
*
* @author https://www.roytuts.com
*/

class Product_model extends CI_Model {		
	
	private $products = 'products';

	function get_salesinfo() {
		$query = $this->db->get($this->products);

		if ($query->num_rows() > 0) {
			return $query->result();
		}
		
		return NULL;
	}
	
}

/* End of file product_model.php */
/* Location: ./application/models/product_model.php */

As you see in the above code we just query the database and return the results and if there is no data we just return NULL.

Now we would like to create controller class that handles request and response for the client. The client class also communicate with the model class to send the data to end users. Create file product.php  under application/controllers with the below code.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

/**
* Description of product
*
* @author https://www.roytuts.com
*/

class Product extends CI_Controller {
	
	function __construct() {
		parent::__construct();
		$this->load->model('product_model');
	}
	
	public function index() {
		$data['salesinfo'] = $this->product_model->get_salesinfo();
		$this->load->view('salesinfo', $data);
	}
	
}

/* End of file product.php */
/* Location: ./application/controllers/product.php */

Now we have to display the data into view. So let’s create view file salesinfo.php under application/views directory.

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Generate PDF report from MySQL database using Codeigniter</title>
	<style>
		table {
			font-family: arial, sans-serif;
			border-collapse: collapse;
			width:
			100%;
		}
		td, th {
			border: 1px solid #dddddd;
			text-align: left;
			padding: 8px;
		}
		tr:nth-child(even) {
			background-color: #dddddd;
		}
	</style>
</head>
<body>
	<div style="margin: 10px 0 0 10px;width: 600px">
		<h3>Sales Information</h3>

		<?php			
			$this->table->set_heading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
			
			foreach ($salesinfo as $sf):
				$this->table->add_row($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
			endforeach;
			
			echo $this->table->generate();
		?>
		
	</div>
</body>
</html>

In the above code we have first set the HTML table headings and then for each record we add the row, then finally we generate the HTML table.

Now we have to point the Product controller class in application/config/routes.php file otherwise we won’t be able to see the generated table.

$route['default_controller'] = 'product';

Now we got everything configured and coded. So let’s test the application. Run the application or hit the URL http://localhost/<root directory name>/index.php directly into the browser, you should see the similar screen as shown in below image.

 

generate html table from mysql database using codeigniter

Look in the above output how alternate rows are in different colors. Please check the css style in the view file above. We have applied css color according to the odd even rows in HTML table.

Thanks for reading.

Soumitra Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

Leave a Reply

Your email address will not be published. Required fields are marked *