select data from database in codeigniter

Select Data From Database in Codeigniter

Select and Fetch Data from database in Codeigniter

in This lesson we will learn how to Select and Fetch Data from database in Codeigniter.
Follow below step to Select data from database in Codeigniter.
=>Database and Table Structure:
Database Name: test
Table Name : user

SQL Commands for Table user

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

SQL Commands for inserting some rows

INSERT INTO `user` (`id`, `username`, `email`) VALUES
(2, 'ANJAN KUMAR', 'Anjankumardhali@gmail.com'),
(48, 'ANJAN', 'biswasshiuli608@gmail.com'),
(10, 'Supriya Gain', 'SupriyaStar@Yahoo.com'),
(42, 'Priyoshi Debi', 'priya6@gmail.coma'),
(47, 'ANJAN KUMAR', 'Anjankumardhali@gmail.com'),
(49, 'ANJAN', 'biswasshiuli608@gmail.com'),
(51, 'ANJANBD', 'biswasshiuli608@gmail.com');

Step 1: Database Configuration
Go to Codeigniter/application/config/ and open database.php file.
and Configure Like Below..

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'test',
 'dbdriver' => 'mysqli',
 'dbprefix' => '',
 'pconnect' => FALSE,
 'db_debug' => (ENVIRONMENT !== 'production'),
 'cache_on' => FALSE,
 'cachedir' => '',
 'char_set' => 'utf8',
 'dbcollat' => 'utf8_general_ci',
 'swap_pre' => '',
 'encrypt' => FALSE,
 'compress' => FALSE,
 'stricton' => FALSE,
 'failover' => array(),
 'save_queries' => TRUE
);

Note: hostname =Your Host Name
username=mysql username
password=mysql password
database= Your database name.
Step 2:URI Route
Go to Codeigniter/application/config/ and open routes.php file configure like below

$route['default_controller'] = 'blog';
$route['404_override'] = '';
$route['translate_uri_dashes'] = FALSE;

Step 3: Models:
Codeigniter/application/models/ and create user.php file to run query in database.

<?php
class User extends CI_Model {
 public function info(){
  //Loading Database
  $this->load->database();
  //Run Query in Database
  $query=$this->db->get('user');
  return $query->result();
 }
}
?>

Step 4: Controllers:
Go to Codeigniter/application/controllers/ and create blog.php file to get Data From Model.

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

class Blog extends CI_Controller {
             
  public function index()
 {
   $this->load->model('user');
         $data['result']= $this->user->info();
   $this->load->view('welcome_user',$data);
 }
 public function about(){
  echo "This is About Page";
 }
 
}

Step 5: Views:
Go to Codeigniter/application/views/ and create welcome_user.php file to View data in html codes.

<html>
  <head>
   <title>Codeigniter Controller Examples--codenair.com</title>
  </head>
  <h1>User List's</h1>
  <table cellspacing="0" border="1">
    <tr>
    <th>ID</th>
    <th>UserName</th>
    <th>Email</th>
 </tr>
  
  <?php foreach($result as $row){?>
    <tr>
   <td><?php echo $row->id;?></td>
   <td><?php echo $row->username;?></td>
   <td><?php echo $row->email;?></td>
 </tr>
  <?php }?>
  </table>
</html>

Rseult:
Run Web Browser and Type http://localhost/codeigniter/ or
http://localhost/codeigniter/index.php/blog
and Hit Enter.
Learn How to Remove index.php from Codeigniter

Once You run it You will see the below result.