How To List Database Records in Joomla Backend

The most typical type of screen seen in the backend of Joomla is one that lists all the records in a table making them available for editing. Let's start off the backend of the Noida City component, with a list of all the places in the database.

Backend of joomla component noida city

First, let's login to the backend of Joomla Go to Noida City Component and choose the Noida City Places item. So, right now the places view item is just showing the default text that we have for the backend of the component that we still haven't built out yet.

Backend view for noida places

Let's add some code that will load a view rather than just showing this backend placeholder text.

Editing backend noida city file

Next, go to administrator>components>com_noidacity and then go to noidacity.php and open it and then replace that file with following code.

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport('joomla.application.component.controller');
  5.  
  6. $controller = JController::getInstance('NoidaCity');
  7. $controller->execute(JRequest::getCmd('task'));
  8. $controller->redirect();

So you will notice, this is the exact same code that we have for the front end. It's pulling in a controller and it's getting an instance of it, and then it is executing the task from the request against that controller and that is redirecting the browser if necessary.

So next we need to add the controller, that we are going to use for our back end.

Creating backend controller file

Add the following code of lines to our controller.php file

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport('joomla.application.component.controller');
  5.  
  6. class NoidaCityController extends JController
  7. {
  8. }

You will notice it's a very basic controller. There are no tasks on it, we are just relying on the default display task, that is included in JController.

500 error in joomla backend of noida city component

Right now, if we go and refresh the backend you will notice that we will get a 500 error and that's because while we're specifying the view places here up at the top in url, we still don't have the places view in place.

So let's add a model and a view so that Joomla has something to display when we request the places view.

Creating model and view in the backend of joomla

Go back to com_noidacity component in the backend and create the models and the views folders.

Let's take a look at the model. So create a file called places.php inside your models folder and add the following lines of code.

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport('joomla.application.component.modellist');
  5.  
  6. class NoidaCityModelPlaces extends JModelList
  7. {
  8. public function getItems()
  9. {
  10. $items = parent::getItems();
  11.  
  12. foreach($items as &$item)
  13. {
  14. $item->url = 'index.php?option=com_noidacity&amp;task=place.edit&amp;place_id'.$item->place_id;
  15. }
  16. return $items;
  17. }
  18.  
  19. public function getListQuery()
  20. {
  21. $query = parent::getListQuery();
  22.  
  23. $query->select('*');
  24. $query->from('#__noidacity_places');
  25.  
  26. return $query;
  27. }
  28. }
  1. public function getListQuery()
  2. {
  3. $query = parent::getListQuery();
  4.  
  5. $query->select('*');
  6. $query->from('#__noidacity_places');
  7.  
  8. return $query;
  9. }

You will notice here we have a getListQuery just like we have for the other model lists and we are just getting a fresh query and selecting all the records from places and all the columns in that table as well, and then we are returning the query object.

  1. public function getItems()
  2. {
  3. $items = parent::getItems();
  4.  
  5. foreach($items as &$item)
  6. {
  7. $item->url = 'index.php?option=com_noidacity&amp;task=place.edit&amp;place_id='.$item->place_id;
  8. }
  9. return $items;
  10. }

Then when Joomla goes to execute the getItems function, we are first calling the parent::getItems function, which will run this query and return all of the results from the database.After that what we're doing is we're doing a little bit of prep work for the view. We are cycling over the items in a for each loop and we're getting each item by reference so that we can edit them in place, and then we are adding this URL property to each item and we are building the edit url for every item that's in that result set. We are pointing to be noidacity component and calling up the place controller with the edit task, and then we are assembling the place_id onto the end of that. So that way when we assemble the URL here, we don't have to do it in the view and it keeps our view nice and tidy.

So let's take a look at the view now.Go to the places view here in the backend and create view.html.php file and add the following lines of code

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport('joomla.application.component.view');
  5.  
  6. class NoidaCityViewPlaces extends JView
  7. {
  8. protected $items;
  9.  
  10. public function display($tpl = null)
  11. {
  12. $this->items = $this->get('Items');
  13.  
  14. parent::display($tpl);
  15. }
  16. }

You will notice it's a very straightforward view. We are just pulling in the items from the model and assigning them to the view, and then we will be displaying in layout.

So let's take a look at the layout in default.php. Create a folder inside places views call it tmpl and inside the tmpl folder create a file called default.php and add the following lines of code.

  1. <?php defined( '_JEXEC' ) or die; ?>
  2. <form action="index.php?option=com_noidacity&amp;view=places" method="post" name="adminForm" id="adminForm">
  3.  
  4. <table class="adminList">
  5. <thead>
  6. <tr>
  7. <th width="1%">
  8. <input type="checkbox" name="checkall-toggle" value=""
  9. onclick="checkAll(this)" />
  10. </th>
  11. <th><?php echo JText::_('COM_NOIDACITY_FIELD_PLACE_NAME_LABEL'); ?></th>
  12. <th><?php echo JText::_('COM_NOIDACITY_FIELD_PLACE_DESCRIPTION_LABEL'); ?></th>
  13. <th><?php echo JText::_('JSTATUS'); ?></th>
  14. </tr>
  15. </thead>
  16. <tbody>
  17. <?php foreach($this->items as $i => $item): ?>
  18. <tr class="row<?php echo $i % 2 ?>">
  19. <td class="center">
  20. <?php echo JHTML::_('grid.id', $i, $item->place_id); ?>
  21. </td>
  22. <td>
  23. <a href="<?php echo $item->url; ?>"><?php echo $this->escape($item->place_name);?></a>
  24. </td>
  25. <td><?php echo $this->escape($item->place_description); ?></td>
  26. <td class="center">
  27. <?php echo JHTML::_('jgrid.published', $item->published, $i, 'places', 'cb');?>
  28. </td>
  29. </tr>
  30. <?php endforeach; ?>
  31. </tbody>
  32. </table>
  33. </form>

Now if we go to our component noida city and go to noida city places and hit refresh instead of showing 500 error it will show the listing of all records in our database

Pulling database records in joomla backend

  1. <form action="index.php?option=com_noidacity&amp;view=places" method="post" name="adminForm" id="adminForm">

So here we have a form for the backend. You will notice that the name of this form is adminForm and that's very crucial to know because all of the JavaScript that's in the backend of Joomla is designed to work with a form named adminForm. So just be sure to add that to your form whatever you're doing the list view.

  1. <form action="index.php?option=com_noidacity&amp;view=places" method="post" name="adminForm" id="adminForm">
  2.  
  3. <table class="adminList">

You will also notice that the form is posting back to the noida city component, and then we begin to get into the table. We have a table here with a class of adminList.

Admin class in joomla backend

If we go and refresh this view now, you'll notice that we have a specific format for the table with the alternating backgrounds. This is controlled through the adminList class for the table. Every row that we go through, we are going to take a look at which row number it is and decide whether it's an odd or an even row and then output the class name based on that.

  1. <thead>
  2. <tr>
  3. <th width="1%">
  4. <input type="checkbox" name="checkall-toggle" value=""
  5. onclick="checkAll(this)" />
  6. </th>
  7. <th><?php echo JText::_('COM_NOIDACITY_FIELD_PLACE_NAME_LABEL'); ?></th>
  8. <th><?php echo JText::_('COM_NOIDACITY_FIELD_PLACE_DESCRIPTION_LABEL'); ?></th>
  9. <th><?php echo JText::_('JSTATUS'); ?></th>
  10. </tr>
  11. </thead>

Additionally, up here at the top, we have headers for every column, we have a checkall check box in the first column, and then we have standard table headers and all the other columns.

  1. <?php foreach($this->items as $i => $item): ?>
  2. <tr class="row<?php echo $i % 2 ?>">
  3. <td class="center">
  4. <?php echo JHTML::_('grid.id', $i, $item->place_id); ?>
  5. </td>

And then finally down here in each row, we first echo out a check box and we are using the JHtml class to generate a check box that's specific for these records. It finds the record ID to every check box and in that way when Joomla is using the toolbar buttons to publish or unpublish or delete or deal with any number of records as a group, it will have the correct IDs behind these check boxes.

  1. <td>
  2. <a href="<?php echo $item->url; ?>"><?php echo $this->escape($item->place_name);?></a>
  3. </td>

Next, we have the URL, the ones that we built back in the model and we are simply echoing them out and we are not building them here, we are just echoing what we built in the model, and then we're escaping the place name and echoing it as the link title.

  1. <td><?php echo $this->escape($item->place_description); ?></td>
  2. <td class="center">
  3. <?php echo JHTML::_('jgrid.published', $item->published, $i, 'places', 'cb');?>
  4. </td>

Then we have the place description and then again we are calling the JHtml class to generate publishing and publishing buttons.

If we take a look back here at the view, you will notice these check boxes and these are images, when you click on them, they will unpublish and publish records depending on what the state they are in. So that JHtml::_('jgrid.published') function is going to determine whether or not the item is published based on the item data, and then it's going to generate the appropriate check box or unpublish button depending on whether or not that record is published.

For the most part, a list view in the backend starts the same way that a list view in the front end does. Much of the same MVC functionality is present and it's written in a very similar way. However, the JHtml helper functions and specific HTML markup create the familiar Joomla backend user-interface.