Database Handling in CodeIgniter
In this article, we will configure and work with database in same application “cidemo” as created in previous article of codeigniter, for that let’s create a new database and a table in MySql. Run the below command in your MySql Server:
Step 1: Creation a database and table
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`address` varchar(45) NOT NULL,
`salary` float NOT NULL,
`status` enum(‘0′,’1’) NOT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `mydb`.`user` (`id`, `name`, `address`, `salary`, `status`) VALUES (NULL, ‘Albert’, ‘D55’, ‘85000’, ‘1’);
Step 2: Database configuration
Edit database configuration file of your application i.e. application/config/database.php, find $db[‘default’] array and set the array items as below:
‘dsn’ => ”,
‘hostname’ => ‘localhost’,
‘username’ => ‘root’,
‘password’ => ”,
‘database’ => ‘mydb’,
‘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
);
In this array we have set ‘hostname’ => ‘localhost’,’username’ => ‘root’,’password’ => ”,’database’ => ‘mydb’.
Step 3: Create the Usermodel Model
Models are classes that contain functions which work with information from the database. So create a “Usermodel.php” in “application/models/” folder.
class Usermodel extends CI_Model {
function __construct(){
// Call the Model constructor
parent::__construct();
$this->load->database();
}
function get_all(){
$this->db->select(“*”);
$this->db->from(“user”);
$rows = $this->db->get();
return $rows->result();
}
}
?>
In the above model, constructor is used to load database library i.e “$this->load->database()” and get_all() function is used to write the query to get records from user table. get_all function will return all the rows from user table.
Step 4: Create the Controller
Now create a controller to load the model and call the view.
In the folder /application/controllers, create a file called Hellouser.php and a class which has the same name as the file.
Inside Hellouser class, constructor is defined to load the usermodel and index function is used to call the get_all function of usermodel and also to load view welcome_user, that we will create in next step.
defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);
class Hellouser extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->model(‘Usermodel’);
}
public function index() {
$data[‘users’] = $this->Usermodel->get_all();
$this->load->view(‘welcome_user’,$data);
}
}
?>
Now, as in previous article of codeigniter, we have set default controller “hellouser”, hellouser controller will called automatically with base url, If it’s not set, please edit the “cidemo\application\config\routes.php” file, Find the line:
Change it to
Step 5: Create a view
Now create a view “welcome_user.php” in “application/views/” folder, in which we will write the html code to display the data of user table. See below:
<head>
<meta charset=”utf-8″>
<title>Welcome List</title>
</head>
<body>
<?php foreach($users as $user) {
echo “<b>Name</b> : “.$user->name;
echo “<br/><b>Address : </b>”.$user->address;
echo “<br/><b>Salary : </b>”.$user->salary;
echo “<hr/><br/>”;
} ?>
</body>
</html>
Output:
About Author
bestonetechnologies
I am a software developer.
