Codeigniter tutorial

Codeigniter Database Create View Update Delete (CRUD)

Views: 1700

Codeigniter Database Create View Update Delete (CRUD) Tutorial Part-1

What is CRUD?

CRUD stands for Create Read Update Delete, these are the four functions we perform with our any database query. In codeigniter we also need to perform CRUD for our applications to get and manipulate data as needed. In this tutorial we will learn how to perform CRUD with codeigniter.

mysql photo Photo by tmolini

Load database in codeigniter 

If your codeigniter is not set for auto loading the database with auto load function, then every time you make a request you will have to request codeigniter to load the database and perform your query. For auto loading database in codeigniter go through our previous tutorial First Page With CodeIgniter Tutorial. Else you can load the codeigniter database by following call

$this->load->database()

In our model before performing any database query with codeigniter.

Codeigniter Database Select Distinct

What is distinct?

MySql distinct allows you to let ignore the duplicate rows from result set. This comes handy when we need unique set of rows. For using MySql distinct with codeigniter you have to pass the following request. i.e. You will have to load the distinct method.

$this->db->distinct();

The distinct method must be loaded above your select method.

Codeigniter database select

For an instance think of your application, you will need to select and display data at any moment from your codeigniter database, the db select in codeigniter allows you to perform select operation from MySql database. You will have to additionally use the ‘from’ method to declare the table name, and then you will need to use the where method in some of your queries. Here is an example of my query for selecting name of users from users table where the id is ‘1’.

MySql Query:

Select name FROM `users` WHERE id='1';

Codeigniter Database Select Query “this->db->select”

$this->db->select('name');

$this->db->from('users');

$this->db->where('id', '1' );

Note: You can also use ‘*’ for select method to perform SELECT * FROM users. If you want more where clause you can add as much needed, the where method uses AND for your multiple where requests. i.e. WHERE id=’1’ AND id=’2’ … so on.

Codeigniter Database Regular Queries “this->db->query()”

You can perform even your regular queries with codeigniter, this allows you to build your own custom MySql query and use them in codeigniter. The query() function returns database result object when “select” queries are performed. See the example below:

$query=this->db->query('SELECT * FROM table_name');
$result = $query->result();
return $result;

Codeigniter Database Result “result()”

The result method in codeigniter returns sql results as an array object after the query is performed. Results can be fetched with foreach loop. Result() function returns a blank array on error.

Performing codeigniter database select query

Now we will perform codeigniter database query and display the result on our view page. If you are not aware of models views and controllers then go through the following tutorials first.

Create a table inside the codeigniter database, with the following SQL query.

CREATE TABLE IF NOT EXISTS `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`employee_id` int(11) DEFAULT NULL,

`user_type` varchar(50) DEFAULT NULL,

`username` varchar(100) NOT NULL,

`password` varchar(100) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `user` (`id`, `employee_id`, `user_type`, `username`, `password`) VALUES

(2, 1, 'NORMAL', 'robin', 'robin'),

(3, 2, 'ADMIN', 'taylor', 'taylor'),

(4, 3, 'ADMIN', 'vivian', 'vivian'),

(5, 4, 'NORMAL', 'harry', 'harry'),

(6, 7, 'ADMIN', 'melinda', 'melinda'),

(7, 8, 'NORMAL', 'harley', 'harley');

 

Now create a new model inside your application/models directory, name it Database_select_model, add the following chunk code inside your newly created model:

Codeigniter select from database model

<?php
class Database_select_model extends CI_Model{
public function __construct(){
parent :: __construct();
}
function get_database_results()
{
}
}

Now we will add codes to our codeigniter database function which will select from MySql user table and provide us the result object. Append the get_database_results() function with following code

$this->db->select('*');

$this->db->from('user');

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

$result=$query->result();

return $result;

Note:The above syntax says codeigniter database method to select ‘*’ from user table and then store the returned set of result in the $result variable, then return $result array.

Now create a new controller in codeigniter inside your application/controllers directory and name it View_results also add a view inside your application/views directory name it select_results, then add the following codes in your controller’s index function:

codeigniter controller view results

public function index()

{

$data=array();

$this->load->model('database_select_model');

$data['result']=$this->database_select_model->get_database_results();

$this->load->view('select_results',$data);

}

Note: With the above code we are declaring $data as an array and loading our newly created model then in $data[‘result’] key we are storing the returned result from model and passing it to our newly created view.

Now open your view file i.e. select_results we will now print the returned results in our view page add the following HTML and PHP codes inside the view file between opening and closing body clause.

<table>

<tr>

<th>id</th>

<th>employee id</th>

<th>user type</th>

<th>username</th>

<th>password</th>

</tr>

<?php

foreach($result as $row)

{

echo '<tr>';

echo "<td>{$row->id}</td><td>{$row->employee_id}</td><td>{$row->user_type}</td><td>{$row->username}</td><td>{$row->password}</td></tr>";

echo '</tr>';

}

?>

</table>

Note: In the above code we are echoing the returned database result from our model.

 

Now load the controller in your browser, you should see your table printed with all the data in it, if you can see the output without any error then you have successfully displayed your database results in your view file. For reference check the output below, sample project model view and controller and the table is attached below you can download and check them.

Codeigniter database select output

Sample Files

In next tutorials we will learn about the other CRUD properties, performing insert, delete and update operations with codeigniter. If you have any suggestions comment below and if you find this tutorial helpful then share it with your friends also like us on social media.

Next Part

 

Comments: 2

Your email address will not be published. Required fields are marked with *