C

Simplified tutorials to learn the popular basic language C

VB.Net

Learn the basic desktop development application language

CodeIgniter

One of the popular PHP three tier (MVC) framework

Select Statement in CodeIgniter


Now we successfully inserted some data into our database. Now we are going to retrieve some of them from our database using select query.



There are many ways to select data from database like getting a single value, multiple values, etc.

Here let us retrieve single value and we will learn other ways in coming lessons.

Selecting data from MySQL database codeIgniter

       We are already familiar with database operation to insert data. Like inserting data, we should need a function to retrieve our data in to our project.

So let us create a function named getage(). Here we are going to retrieve age of a user when we give their name. The function getage is independent, so we have to call this function from our controller.

public function getage()
{

}

Like insertion operation, there  are mainly two ways for selection of data, such as CodeIgniter specialized and traditional method.

I think tradition way is simple and easier.

Tradition way of selection:

We are going to select the name of the user, his age is 25.

Now let us write codes to retrieve name of the user inside the 'getage' function.

public function getage()
{
  $this->load->database();
  $query = $this->db->query("Select user_name from table_user where user_age= 25");
  $row = $query->row();
  echo $row->user_name;
}

Explanation:
  • $this->load->database();
This code loads the database.
  • $query = $this->db->query("SELECT user_name FROM table_user WHERE user_age= 25");
In this line of code, "SELECT user_name FROM table_user WHERE user_age= 25" is the select query, which select user_name(database column name) from the table_user(database table name)  where user_age (database column name) where ‘=25’ is the condition.

This statement retrieves the user name whose age is 25, and the retrieved data is stored on a variable ‘query’. You can use any variable name instead of  'query'.
  • $row = $query->row();
This line generates the query result. Now the variable $row gets a row value of the query result.
  •   echo $row->user_name;
Now variable $row contains the query retrieved data, that is 'user_name'. So the code $row->user_name; generates value of user_name, that is 'Aslam Kakkove'.
    We commonly do no print (show) anything on a model class, we have to pass this value into the View section of our CodeIgniter project. As a testing, we just echo its value to check the database retrieved data.

Running the Model:

To run a Model class, we should call it from a Controller function.
Like we created a controller function 'userresiter' for inserting data, we have to create a new function to retrieve the data. So let us create an another function to call the Model class function 'getage'.

Here I created a controller function named ‘getdata()’. Now we can invoke Model class function 'getage' on it.

  public function getdata()
  {
   $this->load->Model('ProjectModel');
   $this->ProjectModel->getage();
  }

How to run this code of data selection:

Just run the controller function ‘getdata’ that we just created.

http://localhost/myproject/index.php/user/getdata

Sample output:

Selecting data from MySQL database codeIgniter Output

Total Controller Class with Insertion:


<?php
class User extends CI_Controller
 {
  public function register()
  {
    $this->load->view("user/registration");
  }
  public function userregister()
  {
    $name = $this->input->post("username");
    $place = $this->input->post("userplace");
    $age = $this->input->post("userage");

     $this->load->Model('ProjectModel');
     $this->ProjectModel->USERNAME = $name;
     $this->ProjectModel->USERAGE = $age;

     $this->ProjectModel->addvalue();
  }

  public function getdata()
  {
   $this->load->Model('ProjectModel');
   $this->ProjectModel->getage();
  }

Total Model Class:


<?php
class ProjectModel extends CI_Model
{
  var $USERNAME = '';
  var $USERAGE = 0;

public function addvalue()
{
  $this->load->database();
     $data = array(
            'user_name' => $this->USERNAME,
         'user_age' => $this->USERAGE
                 );
  $this->db->insert('table_user', $data);
}

public function getage()
{
  $this->load->database();
  $query = $this->db->query("Select user_name from table_user where user_age= 25");
  $row = $query->row();
  echo $row->user_name;
}
}

CodeIgniter Specialized selection statement:

 public function getage()
 {
  $this->load->database();
  $this->db->select('user_name');
  $this->db->from('table_user');
  $this->db->where('user_age', 25);
  $query = $this->db->get();
  $row = $query->row();
  echo $row->user_name;
 }

 Explanation: 

 $this->db->select('user_name');
 $this->db->from('table_user');
 $this->db->where('user_age', 25);

 These three lines of codes select 'user_name' from our table 'table_user' with the condition of 'user_age' as '25'. Remaining codes are same as our first method


No comments:

Post a Comment