Connect and fetch data from database with codeigniter

Connect-and-fetch-data-from-database-with-codeigniter

Hello World, Welcome to codeigniter tutorial series Learn codeigniter from scratchIn this tutorial we will learn that how we can connect and fetch data from database with codeigniter. We will use Model Section in MVC in this step.

In the previous tutorials, we have learn that how to setup codeigniter and how to create static web application with codeigniter and bootstrap 3.

I have divided this tutorial in following parts:

  • Models in Codeigniter
  • Create database and connect with database
  • Add data via phpmyadmin
  • Attach Model with controller and Get the data
  • Test the application

Before reading this tutorial i will request you to read the previous articles. Because i will extend the previous article or code in the next tutorial.

Models in Codeigniter

According to their official documentation

Models are optionally available for those who want to use a more traditional MVC approach.

Models are PHP classes that are designed to work with information in your database.


Create database and connect with database

With the help of following easy steps you can create and connect with database.

  • Open localhost/phpmyadmin.
  • Create todo_ci database with following table structure.

table-structure

 

  • Open application/config/database.php file in todo directory (You have created in last tutorial).
  • You can replace the following values with your database credentials. By default localhost Credentials are:
  • user:‘root’, password:‘ ‘

 connect-with-database

 

  • Once you added the values, just save the file. In the next step, we will use this database in our model.

Add data via phpmyadmin

  • In this step we will add data from phpmyadmin. In the next tutorial of this series we will add data with form submit.
  • Open localhost/phpmyadmin and add the data like following screen.

insert-data

 


Attach Model with controller and Get the data

In this step we will create model. Model will help us to perform database work.

  • Create Todo_Model.php file in application/models folder and add the following code.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Todo_Model extends CI_Model{
	function __construct(){
		parent::__construct();
		// Load Database
		$this->load->database();
	}
	// Count Total List
	function count_list(){
		return $this->db->count_all('todo_list');
	}
	// Get all list
	function get_all_list(){
		$response=array();
		$query=$this->db->get('todo_list');
		if($query->num_rows()>0){
			$response['bool']=true;
			$response['d']=$query->result_array();
		}else{
			$response['bool']=false;
		}
		return $response;
	}
}
?>
  • I have added two method in the above file. One is for counting all the todo  list and another is responsible for fetching data from database.
  • Now Open Todo.php from application/controllers file and replace the following code with previous code.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Todo extends CI_Controller {
	function __construct(){
		parent::__construct();
		// Load Helper
		$this->load->helper('url');
		// Load Model
		$this->load->model('Todo_model');
	}
	// todo list
	function index(){
		$data['page_title']="To-Do List";
		$data['totalResult']=$this->Todo_model->count_list();
		$data['content']=$this->Todo_model->get_all_list();
		$this->load->view('todo/todo.php',$data);
	}
}

In the above code, i have connected the database with $this->load->database() function, fetch the total results and data from database.

We have just replace the static code with dynamic code.

  • Open todo.html file from application/views/todo folder.
  • Replace the following code with previous one.
<!DOCTYPE html>
<html>
<head>
	<title><?php echo $page_title; ?></title>
	<link rel="stylesheet" type="text/css" href="<?php echo base_url('public/bootstrap.min.css') ?>" />
</head>
<body>
	<div class="container">
		<h1 class="page-header">To-Do List 
			<span class="badge"><?php echo $totalResult; ?></span>
			<a href="#" class="btn btn-sm btn-danger pull-right">Add Task</a>
		</h1>
		<table class="table table-striped table-bordered">
			<tr>
				<th>Task</th>
				<th>Status</th>
				<th>Action</th>
			</tr>
			<?php
			if($content['bool']==true){
				foreach($content['d'] as $task){
					?>
					<tr>
						<td><?php echo $task['task_title']; ?></td>
						<td><?php echo $task['task_status']; ?></td>
						<td>
							<a href="#" class="btn btn-xs btn-primary">Edit</a>
							<a href="#" class="btn btn-xs btn-danger">Delete</a>
						</td>
					</tr>
					<?php
				}
			}else{
				?>
				<tr>
					<td colspan="3">
						<p class="alert alert-warning">No Data Found!!</p>
					</td>
				</tr>
				<?php
			}
			?>
		</table>
	</div>
</body>
</html>

 


Test the application

Now we have settled up all the things. Its time to check our result.

  • Open the localhost/path-totodo/index.php/todo
  • You will see the following screen

todo-list


Congratulations, You have connected with database and fetched data from MySQL Database :).

In the next tutorial of this series we will submit form with codeigniter and save the data in database.

Leave a Reply