How to fetch data from the database using CodeIgniter and MySQL

  • Post author:
  • Post category:CodeIgniter
  • Post comments:0 Comments
  • Post last modified:October 5, 2020
  • Reading time:4 min(s) read

In this tutorial, we are going to learn how to fetch data from the database using CodeIgniter and MySQL.If you are a beginner please click on the CodeIgniter category and check out all the CodeIgniter related tutorials.

If you have already gone through the data insertion video in CodeIgniter then you already know the database structure, I am not going to post it again.

To fetch data you have to create a function in the controller as shown in the below code:

public function show_data()
	{

$data=$this->My_model->fetch();

$capsule = array('user_data' => $data );

$this->load->view('show',$capsule);


	}

And also a function in the model as shown in the below code:

public function fetch()
 	{
$query=$this->db->order_by('id',"DESC")->get('users')->result();

 		return $query;

 	}

$this->My_model->fetch(); in this particular line I just connected fetch() function of My_model class where I set up the SQL select query.Then I stored the return value of fetch() function in $data variable. after that, I just created an array named $capsule and set an array index user_data to make an associative array of the $data variable(this is called encapsulation). Then I just pass the value with my view file.

$this->db->order_by(‘id’,”DESC”)->get(‘users’)->result(); in this particular line I used query builder just to fetch all the data from users table by descending order.The result() function I use to get the result in associative format.Then stored the data in $query variable and returned the value.

Check the view file code given below(Example: show.php):

<!DOCTYPE html>
<html>
<head>
<title>Data fetching from database using codeigniter</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.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="card">
<div class="card-header">Data table <a style="float: right;" href="<?php echo base_url('New_con/form') ?>" class="btn btn-success">Add data</a> </div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-bordered">
<thead>
<tr>
<th>Name</th>
<th>Number</th>
<th>Created on</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php foreach ($user_data as $key) {
     ?>
<tr>
<td><?php echo $key->name; ?></td> <!-- these are the static value "jj" how to show data dynamically ?? lets see -->
<td><?php echo $key->number; ?></td>
<td><?php echo $key->created; ?></td>
<td>
<a href="<?php echo base_url('New_con/edit_data') ?>/<?php echo $key->id ; ?>" class="btn btn-info btn-xs">Edit</a>

<a href="" class="btn btn-danger btn-xs">Delete</a>
</td>                
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>        
</div>
</div>
</body>
</html>

To show data dynamically I used foreach loop

<?php 
foreach ($user_data as $key) {
 } 
?>

$user_data which has been sent from show_data() function then we set is as $key then 

<?php echo $key->name; ?>

In the above code name(generally column-name of the table).

Here is the complete code of controller and model fetch data from the database using CodeIgniter and MySQL.

New_con.php(controller)

<?php
/**
 * 
 */
class New_con extends CI_Controller
{
	
	public function __construct()
	{
		parent:: __construct();

		$this->load->helper('url');
		$this->load->database();
		$this->load->model('My_model');
	}


	public function index()
	{
		$this->load->view('a');
	}


	public function form()
	{
		$this->load->view('form');
	}
	public function data_insert()
	{
		$name=$this->input->post('name');
		$phone=$this->input->post('phone');

		$capsule = array('name' =>$name ,'number'=>$phone );

		$msg=$this->My_model->save($capsule);

		echo $msg;

		$this->show_data();
	}


	public function show_data()
	{

			$data=$this->My_model->fetch();

			$capsule = array('user_data' => $data );

		$this->load->view('show',$capsule);


	}

}
	?>

My_model.php(model)

<?php 

/**
 * 
 */
class My_model extends CI_Model
{
	
 	public function save($capsule)
 	{
 		$insert=$this->db->insert('users',$capsule);
 		if ($insert) {
 			return $msg="Data Inserted Successfully";
 		}
 	}




 	public function fetch()
 	{
 		$query=$this->db->order_by('id',"DESC")->get('users')->result();

 		return $query;

 		

 	}
 }
 ?>

Leave a Reply