Select Data
Step by Step process to Fetch data from database

1- Set database detail in configuration file

Just goto
application->config->database.php

2- Initializing Database class in controller constructor
$this->load->database();

3- To fetch record from database, there are two way

(A)- Standard Query
(B)- Query Builder Class

4- Create Controller

5- Create Model

6- Then create HTML page to display data

1- Standard Query

$query = $this->db->query("write query here");

For example
$query = $this->db->query("select * from info");

foreach ($query->result() as $row)
{
echo $row->name;
echo $row->email;
echo $row->address;
}

2- Query Builder Class

$this->db->get('tablename');

This will generate query
select * from info

$query = $this->db->get('info');

foreach ($query->result() as $row)
{
echo $row->name;
echo $row->email;
echo $row->address;
}

Create Controller

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class User extends CI_Controller {

public function __construct() {
parent::__construct();
$this->load->database();
$this->load->model('user_model');
}

public function index()
{
$data['user']=$this->user_model->fetch_data();
$this->load->view('display',$data);
}

}

Create Model

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class User_Model extends CI_Model {

public function fetch_data()
{
$this->db->select('*');
$q = $this->db->get('tbl_user');
return $q->result();
}

}

HTML page to Display Data

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<html>
<head>
<title>Display Data</title>
</head>
<body>
<table border="1">
<tr>
<td><b>Name</b></td>
<td><b>Email</b></td>
<td><b>City</b></td>
</tr>
<?php
foreach($user as $row){
?>
<tr>
<td><?php echo $row->name; ?></td>
<td><?php echo $row->email; ?></td>
<td><?php echo $row->city; ?></td>
</tr>
<?php
}
?>
</table>
</body>
</html>

More Query Builder Class

Some usefull query Query Builder Class

$query=$this->db->get('info', $limit, $offset)

$query=$this->db->get_where('info', array('id' => $id), $limit, $offset);

$this->db->select('name, email, address');
$query=$this->db->get('info');

$query = $this->db->get('tablename', $limit, $offset);
To get record with limit and offset.

$query = $this->db->get_where('tablename', array('id' => $id), $limit, $offset);
To get record with limit and offset with where clause.

$this->db->get_compiled_select('tablename')
To print sql query like select * from tablename

$this->db->select('name, email, address');
$query = $this->db->get('tablename');

$this->db->select_max('salary');
$query = $this->db->get('tablename');

List of more Query Builder Class you can see in list
$this->db->select_min()
$this->db->select_avg()
$this->db->select_sum()
$this->db->from()
$this->db->join()
$this->db->where()
$this->db->or_where()
$this->db->where_in()
$this->db->or_where_in()
$this->db->where_not_in()
$this->db->or_where_not_in()
$this->db->like()
$this->db->or_like()
$this->db->not_like()
$this->db->or_not_like()
$this->db->group_by()
$this->db->distinct()
$this->db->having()
$this->db->or_having()
$this->db->order_by()
$this->db->limit()
$this->db->count_all_results()
$this->db->count_all()
$this->db->group_start()
$this->db->or_group_start()
$this->db->not_group_start()
$this->db->or_not_group_start()
$this->db->group_end()

for details of these query visit
https://www.codeigniter.com/userguide3/database/query_builder.html


Previous
Next