CodeIgniter: How to retrieve data from a database

Created at: June 18, 2013; Last update: November 8, 2016

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…

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…

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…

4. The Controller

What would be the model without the controller?

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…

If you have any questions, I am always willing to help….

25 thoughts on “CodeIgniter: How to retrieve data from a database

  1. Fahad

    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.

    Reply
  2. avenirer Post author

    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.

    Reply
    1. Leon

      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.

      Reply
  3. Mayank Pandey

    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[] ?

    Reply
    1. avenirer Post author

      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…

      Reply
  4. Mayank Pandey

    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?

    Reply
    1. avenirer Post author

      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);

      Reply
  5. Remya

    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”

    Reply
    1. avenirer Post author

      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.

      Reply
  6. Prakhar

    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.

    Reply
    1. avenirer Post author

      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. 🙂

      Reply
  7. Jacky

    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!

    Reply
    1. avenirer Post author

      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

      Reply
  8. C.Wijaya

    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 🙂

    Reply
    1. avenirer Post author

      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.

      Reply
  9. Leon

    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 😀

    Reply
  10. Javeed Ishaq

    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.

    Reply
  11. Athuman Garisse

    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.

    Reply
  12. Mårten Hansson

    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!

    Reply

Leave a Reply

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

No spam? * Time limit is exhausted. Please reload CAPTCHA.