What is a dynamic site without databases, right? Yesterday someone asked me to make a tutorial about retrieving data from a database using CodeIgniter. As a self-respecting framework, CodeIgniter is adhering to the Model-View-Controller philosophy, i.e. is using the Models to interact with the database. For starters I will assume that you already have installed CodeIgniter, and you have a MySQL database installed on your webserver.
1. Connect the application to the database
If you know how to make the settings to connect CodeIgniter to the database you can skip this step…
If you don’t know, you must first edit the file database.php which can be found in application/config/ folder. The important lines are these…
$db[‘default’][‘hostname’] = ‘localhost’;
$db[‘default’][‘username’] = ‘admin’;
$db[‘default’][‘password’] = ‘pass’;
$db[‘default’][‘database’] = ‘my_database’;
$db[‘default’][‘dbdriver’] = ‘mysql’;
You should make the changes to the ‘localhost’, ‘admin’, ‘pass’, ‘my_database’, according to your database settings.
2. Autoload the database (optional)
If you know you will use the database alot, you should also set the autoloader so that it will always load the database class. For this, you need to change the autoload.php which can also be found inside the application/config/ folder. In there you will find a line that should be similar to this…
$autoload[‘libraries’] = array(‘database’,’session’,’form_validation’);
It’s important that the ‘database’ element is found inside that array.
3. The Model
As I said earlier, if you want to do the job the right way, use models to make the CRUD operations.
For this lesson I will create a model named Users_model.php. I also named the database table users. The file goes like this…
<?php
class Users_model extends CI_Model
{
/* we will use the function getUsers */
function get_users()
{
/* all the queries relating to the data we want to retrieve will go in here. */
/* after we've made the queries from the database, we will store them inside a variable called $data, and return the variable to the controller */
if($q->num_rows() > 0)
{
foreach ($q->result() as $row)
{
$data[] = $row;
}
return $data;
}
}
}
In the code I made two comments between which you can insert the queries. Now, the question is: how do you make the queries…
3.a. The first and the simplest query is as follows:
$q = $this->db->query(“SELECT * FROM users WHERE id=7 AND usr_mail = ‘mail@mail.org'”);
3.b. But if you want to be protected from SQL injections, I will advise you to use placeholders, as below…
$sql = “SELECT usr_name FROM users WHERE id = ? AND usr_mail = ?”;
$q = $this->db->query($sql, array(7,”mail@mail.org”));
3.c. And… if you also want to make the query “database proof”, it’s best to use the Active Record:
$this->db->where(array(‘id’=>7,’usr_mail’=>’mail@mail.org’)); // the ‘where’ clause
$this->db->select(‘usr_name’); // the select statement
$q = $this->db->get(‘users’); // the table
In conclusion, the Users_model.php (which will have to be saved in the application/models/ folder) would look something like this…
<?php
class Users_model extends CI_Model
{
/* we will use the function getUsers */
function get_users()
{
/* all the queries relating to the data we want to retrieve will go in here. */
$this->db->where(array('id'=>7,'usr_mail'=>'mail@mail.org'));
$this->db->select('usr_name,usr_mail');
$q = $this->db->get('users');
/* after we've made the queries from the database, we will store them inside a variable called $data, and return the variable to the controller */
if($q->num_rows() > 0)
{
foreach ($q->result() as $row)
{
$data[] = $row;
}
return $data;
}
}
}
Of course, you could ask: Why not send the where clause from the controller? If you want to do this, the method will have to receive the ‘where’ array from the controller as shown below…
<?php
class Users_model extends CI_Model
{
/* we will use the function getUsers */
function get_users($where_arr)
{
/* all the queries relating to the data we want to retrieve will go in here. */
$this->db->where($where_arr);
$this->db->select('usr_name,usr_mail');
$q = $this->db->get('users');
/* after we've made the queries from the database, we will store them inside a variable called $data, and return the variable to the controller */
if($q->num_rows() > 0)
{
// we will store the results in the form of class methods by using $q->result()
// if you want to store them as an array you can use $q->result_array()
foreach ($q->result() as $row)
{
$data[] = $row;
}
return $data;
}
}
}
4. The Controller
What would be the model without the controller?
<?php
if(!defined("BASEPATH")) exit("No direct script access allowed");
class Users extends CI_Controller
{
public function index()
{
//if you decided NOT to autoload the database library you would have to add this line: $this->load->library('database');
$this->load->model("users_model"); // we load the model that we saved in the application/models folder, so that the controller will know where to get the methods from
$data["users"]=$this->users_model->get_users(); // we will load the result of the query inside the $data array having the key 'users'
// if you decided to send the conditions for the select to the method, then you would have to write something like this...
// $data["users"]=$this->users_model->get_users(array('id'=>7,'usr_mail'=>'mail@mail.org'));
$this->load->view("users_view",$data);// after you stored the query results inside the $data array, you will have to send the array to the view which will display it inside the browser.
}
}
5. The View
What would the controller show without the views? To display the work of the controller we need a view, which we’ll name as users_view.php and we will save it inside application/views/ folder. The code will go something like this…
<html>
<head>
<title>Users</title>
</head>
<body>
<h1>Users</h1>
<ul>
<?php
/* first we will make sure we have data to display. $users variable is actually the $data['users'] that we sent from the controller to the view... */
if(!empty($users))
{
foreach($users as $user) // user is an object.
{
echo "<li>Name: ".$user->usr_name."; Email: ".$user->usr_mail."</li>";
}
}
?>
</ul>
</body>
</html>
If you have any questions, I am always willing to help….
I make a login system in codeigniter , as you say its admin panel now i need to show data from same database but another table data to show in admin panel, i read your code but still confuse.
I am sorry, but I am confused about your english. This tutorial is for beginners – those who are just at the phase of learning the MVC philosophy. If you’ve managed to make an admin panel I don’t understand what is the problem you’re confronted with. What do you mean with “but another table data to show in admin panel”? Regarding the authentification problem (or rather the admin panel problem), I would recommend you to use IonAuth, a library which is fast and well documented.
I would need to make a login system for an organisation where only admin can create user accounts by entering a user’s name, email and selecting a privilege (user or admin). Upon creation, the new user would be emailed a link to create a password for their account.
Would you recommend I modify an existing login library like IonAuth, or would you recommend I code this from scratch?
Thanks.
Yes, you can. Take a look at these tutorials: http://avenir.ro/authentication-system-with-ion-auth-and-ci3/ In episode three of the tutorials you will notice a title named GOD MODE…
I have one doubt that in the model you are returning $data like this
foreach ($q->result() as $row)
{
$data[] = $row;
}
return $data;
and in the controller
$this->load->model(“users_model”);
$data[“users”]=$this->users_model->get_users();
So how would a controller know that data array is called “users” because you defined array as $data[] ?
Hello,
Let me start by thanking you for reading this post.
First of all, let’s begin with the model. In the model, you could store the rows that are returned from the database with whatever variable you like, it doesn’t matter what variable you choose to use. Instead of $data you could have written $whatever, and then return $whatever.
Now, in the controller the story goes the same. You could name the array whatever you like, you can even name the key whatever you like, as long as you transmit the array to the view so that it is shown on the webpage. For example, you could have said: $ihaveit[‘theusers’] = $this->users_model->get_users(); and then, when you call the view, you call it with $this->load->view(‘users_view’,$ihaveit); and the view would than know that it can use the variable $theusers to get the results the controller sent.
I hope you understood what I just told you. My english is really not that good…
Thanks for the reply,I have one more question regarding this.If I want to fetch data in the Page load event how do I do it.Suppose there is a form which has to be filled from the database when page is loaded, how to do it?
That would be a subject for another post. But I will try to explain it here. OK. Let’s suppose you retrieved data from the model. After you retrieve the row you are interessed in ($data[‘row’]=$this->model->get_row()…), you transfer it to the view that holds the form ($this->load->view(‘form_view’,$data). Using the form helper to create the form you simply populate the form inputs like so “echo form_input(‘name_of_input_field’,set_value(‘name_of_input_field’,$row->column);
Thanks for providing nice tutorials
Hi admin, am a beginner in codeigniter technology. And am trying to learn it myself with the help of tutorials and like references. And when i strated doing with it, i have many doubts. Here i was trying to fetch and display data from database. When am doing it, i got the following error. Could you please help me to correct it? “An Error Was Encountered
Unable to load the requested class: database”
Hello. Simply put, that error tells you that you didn’t load the library (class) that deals with the database. I think you missed the second step: Autoloading the database library. You should look for $autoload[‘libraries’] = array(‘database‘,’session’,’form_validation); inside the autoload.php file and make sure you have ‘database’ in the array.
It didn’t work for me initially as retrieving data through the model by
$this->db->select(‘usr_name’);
makes the select statement only select ‘usr_name’ field but we were trying to echo out ‘usr_mail’ in the view also.
Changing it to
$this->db->select(‘*’); did it!
Please make the corrections if same problem persists for you.
Well, you might as well avoid doing $this->db->select(‘*’);. if no select() is done, but the get() is done, every single column is retrieved. 🙂
Hi Admin
I’d like to see clarification on the Active Records
You mentioned that to “database proof” the queries, it is recommended to use Active Records. I’m going to assume this is an abstraction to the results table. Correct me if I’m wrong.
On the other hand
$this->db->where(array(‘id’=>7,’usr_mail’=>’mail@mail.org’)); // the ‘where’ clause
$this->db->select(‘usr_name’); // the select statement
$q = $this->db->get(‘users’); // the table
I’m assuming this is equivalent to the above posted select queries. My question here would be
1. Does sequence of the statement matters? I’m ‘trained” to think Select first before Where clause.
2. The last assignment statement is the actual assignment of the result table to $q. Am I right?
Thanks!
1. yes; 2. yes. This one here is a bit older. Being written in 2013, it regards CodeIgniter 2.x. Now, with CodeIgniter 3, we are not talking about active record, but Query Builder. What I found very good about CI is the fact that it has a great manual. Being a chain method, there has to be an order in which the methods are executed. You can however put the where before or after select, but the get() method must be the last one as this one will return the query result and not the object. You can find everything regarding the database query builder here: http://www.codeigniter.com/user_guide/database/index.html
hello.
Im getting an error like undefined variable users when i try to print the values in view.
That may mean that you have no users returned… But if I could see the code I would be more useful
Hi, thanks for the tutorials.
I have a question. I’m creating a single web page. (the one with only one page and looong scroll) and i need to store the content in database.
Which view that i should load in the controller part? Since i only have one long page. I suppose load the single index file in every controller is wrong, right?
Should I separate each section and load them separately? Or do you have any other suggestion?
Thanks 🙂
Maybe I didn’t understand you correctly, but CodeIgniter is not a javascript framework. You can’t have one “looong” page and many controllers to deal with parts of that page.
Hi, me again XD
I think I found a bug in your code…
In view, you call echo “Name: “.$user->usr_name.”; Email: “.$user->usr_mail.””;
but in model, you only select $this->db->select(‘usr_name’);
This returns an error. Quick fix $this->db->select(‘usr_name, usr_mail’);
Thanks for giving me a challenge 😀
Thank you for pointing that out.
Really awesome, wonderful, excellent and Brilliant Blog, All articles are great. Thanks a lot for writing all these tutorials May you live long and be happy always.
hi,
My question is a little far fetched but based on this tutorials and the Admin Series of tutorials and the authentication tutorial as well. I am in the processing of creating a web system that will enable users share information with both an admin side and User side. How do i implement saving of data into a database and attach this data only to a specific user (Only this user can delete and edit this info). this information is not in the Users Database.
user_view.php
// user is a class, because we decided in the model to send the results as a class.
Huh?
Also could you please write the result of 3b+3c, please?
Thanks for a realy great tut!
Thank you for noticing…