While developing extensions, you need to query database and display list of items. Joomla provides an easy way to achieve this.

Step 1

In the view.html.php file, get the data for the model in the function display().

// Get data from the model
$this->items = $this->get('Items');

Above code will look for the function getItems() in the model file.

Step 2

In Joomla, there is a class that is able to manage a list of data: JModelList. This class has already defined functions getItems() and getPagination(), so you are not required to define it again. The function getItems() calls another protected function getListQuery() for the database query. 

Also, two states are defined: list.start for determining the list offset and list.limit for determining the list length.

Step 3

Class JModelList and inherited classes need only one method: getListQuery which constructs an SQL query. Write the protected function getListQuery() and build the query here. This function will return the query.

protected function getListQuery()
 // Initialize variables
 $db = JFactory::getDbo();
 $query = $db->getQuery(true);

 // Create the base select statement
 $query->select('id, name, email, published')
  ->where('published = 1');

  $query->order('id DESC');

return $query;

Step 4

You can access and display data in layout file, default.php using foreach loop.

<table class="table table-striped table-hover">
 <?php if (!empty($this->items)) : ?>
  <?php foreach ($this->items as $i => $row) : ?>
    <?php echo $i; ?>
   <?php echo $row->name; ?>
 <?php endforeach; ?>
 <?php endif; ?>