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

Getting Multiple Database Table Rows - CodeIgniter


We already inserted many values into the database and we learnt the retrieval of data into our project. But in our data selection tutorial, we retrieved data one by one. Here let us learn how to get all or multiple data from our database table. This is the fetching of database rows.

First of all, let us create a controller to retrieve all the data.

Controller (getalldata):

public function getalldata()
  {
    $this->load->Model('ProjectModel');
    $data['query'] =  $this->ProjectModel->getalldata();
    $this->load->view('user/showdata',$data);
  }

Here,

$this->load->Model('ProjectModel');
Controller calls a model named 'ProjectModel' is loaded.

$data['query'] =  $this->ProjectModel->getalldata();
$this->ProjectModel->getalldata(); calls a model function named 'getalldata'.

Model (getalldata):

public function getalldata()
{
  $this->load->database();
  $query = $this->db->query("Select * from table_user");
  return $query->result();
}

Here the select query selects all the data from our database table. We can add conditions using 'where' clause.

Example:
$this->db->query("Select * from table_user WHERE user_age > 20");

return $query->result();
Here the query returns the given database retrieved result into the controller class. Note that, here retrieved data contains multiple values.

In Controller:

$data['query'] =  $this->ProjectModel->getalldata();
Due to the multiple retrieved data, the result should be stored on an array variable.  Here $data['query'] is an array variable, where $data is the variable name.

$this->load->view('user/showdata', $data);
Here the variable $data is send into the view page named 'showdata'.

View (showdata.php):

<table>
<tr>
  <th>User name</th>
  <th>Age</th>
  <th>Email</th>
</tr>
<tbody>
<?php foreach($query as $row): ?>
 <tr>
  <td><?php echo $row->user_name; ?></td>
  <td><?php echo $row->user_age; ?></td>
  <td><?php echo $row->user_email; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

In view page, we should use a loop to get individual data.  Here foreach loop is used.

<?php foreach($query as $row): ?>
In foreach loop, array variable $query is converted into individual result using a variable $row.
Now variable $row contains a row of data(tuple). So each row contains value of different columns in the database table such as user_name, user_age, etc.

echo $row->user_name; can print a row data with a column name of user_name.

Here we use <table> to align the data. So table tags such as <tbody>,<th>,<tr> and <td> are used.

<?php endforeach; ?>
This is the end of the foreach loop.

Sample Output:


Retrieve all database table rows data in CodeIgniter

You can customize the data by specifying different columns name on the view section, also you can modify the style of the document and data arrangement using HTML and CSS codes.