Table Basic API Guide

From Joomla! Documentation

Introduction

This is one of a series of API guides, which aim to help you understand how to use the Joomla APIs through providing detailed explanations and sample code which you can easily install and run.

The Joomla Table class provides a framework which enables you to do CRUD operations (and more) on database tables. It is an implementation of the Active Record design pattern. It's mainly used for the case where you're developing a Joomla component with its associated database table, and you're providing admin functionality for managing the data in the database table or displaying site pages associated with individual database records. Many of the steps in the Joomla MVC Component Development tutorial involve using its functionality.

The Table functionality can be used for batch operations if the batch operation is split into individual record updates. However it doesn't support SQL operations on multiple records (eg selecting several records from a database table); for this use the Joomla Database class (see API guides Selecting data using JDatabase and Inserting, Updating and Removing data using JDatabase).

This API guide covers just the basic functionality of the Joomla Table class; more advanced functionality is covered in Table Advanced API Guide.

Initialisation

You need to do some initialisation tasks to be able to use the Joomla Table functionality. Table is an abstract class with methods which support interaction with a general database table. To use it you need to define your own class which inherits from Table, and you need to specify the name of the database table you want to access.

The diagram below shows how the initialisation process works, with the different sections of source code indicated by the coloured backgrounds:

  • yellow – the core Joomla Table code
  • green – your code for your class (called TableExample below) which inherits from Joomla Table
  • white – your code in your extension which interacts with your TableExample class.
Joomla Table - Initialisation

The steps involved in initialisation are as follows:

Step 1 – from your extension code you call the static function Table::getInstance() passing 3 parameters:

  • type – usually this is the name of the entity you're going to be accessing in your table, eg "Contact", "User". It doesn't really matter what you call it, but Joomla will look for a php file with this name when it's looking for your class which extends Table (eg if the type is "Contact" it will expect to find the class in contact.php).
  • prefix – a string with which the "type" is prefixed to create the name of your class. Examples of the prefix from Joomla core code are "ContactsTable", "MenusTable". The key thing is that the class name has to be unique within the namespace. This parameter is optional, the default is "JTable".
  • an optional array of options – currently the only option which you can pass is an existing Database Object with key "dbo".

Step 2 – the Table::getInstance() code will attempt to load your table class by looking for it in a php file which in this case must be called example.php. It will look for it in certain specific directories. For example, if your extension is a component then it will look in the /tables folder in the administrator area of your code.

If you have located the file somewhere else you can call the static function Table::addIncludePath() to tell Joomla where to find it. In the sample module code at the end of this guide a call to addIncludePath() is used to tell Joomla that the class file is in the same directory as the module code itself.

Once the getInstance() code has found the file it will run it, which will result in PHP being made aware of your table classname (TableExample in this case).

Step 3 – The Table::getInstance() code will use new to create an instance of your table class (eg new "TableExample"). PHP now knows about this class and will create an instance and call your constructor.

Step 4 – in your constructor you must call the parent constructor passing

  • the name of the table you want to access, (the #__ prefix on the table name gets replaced by the random Joomla database table prefix),
  • the key field or fields in your table,
  • the database object passed into your constructor.

Step 5 – the Joomla Table constructor is called, and is passed the name of your table and key field(s) as parameters. It then finds from the database the column names of your table and stores these together with the key fields as class instance properties for later use.

Step 6 – The Table::getInstance() code returns to your code the newly instantiated object for your table class.

Basic Table Operations

Once the above initialisation is completed, you will have a reference ($t in this case) to your table class instance which will enable you to perform database CRUD operations on your database table, as illustrated in the diagram below.

Joomla Table - Basic Operations

In each case you will invoke the method within your own class, but generally you end up calling the equivalent Joomla Table method, either because you don't specify the method yourself, or you call the parent method from your method code.

load($keys, $reset) – you call this function passing in the value(s) of the primary key(s) for the record you wish to access. Table::load() reads this record from the database and sets up class properties (shown as red bars on the diagram) whose name is the same as the database field name, giving them the value of that field from the database record. These properties have public scope, so you can access them for reading or writing from your code, eg if there's a "title" column in your table:

$mytitle = $t->title;
// or
$t->title = "new title";

If you're doing multiple load() calls within your code then it's best to set $reset=true to reset the values of these properties before a new record is read from the database.

bind($src, $ignore)Table::bind() sets up those class properties (red bars in the diagram) from the input $src parameter. In general $src is an associative array, and the key/value pairs then get mapped to property/value pairs.

In a web context, data is usually updated through a user submitting an HTML form. If you're designing your data management this way, then it's best to name the input elements of your form so that the HTTP POST basically contains this $src array, with keys matching the names of fields in your table. In this way you can call bind() and get your data all ready for performing an SQL UPDATE or INSERT to the database.

$src can also be an object, in which case the objects properties/values are mapped to the Joomla Table properties.

If you don't want all of the elements of your associative array $src (or properties if $src is an object) mapped to the Table properties, then specify the ones you want ignored in the $ignore parameter.

If in your $src array you have elements (eg params) where the value is an associative array which should be json encoded before writing to the database, then declare within your TableExample class code eg:

protected $_jsonEncode = array('params');

Then bind() will json_encode your array before writing it to the Table property.

Note that the converse doesn't happen – when receiving a field in $src which is a json-encoded string, bind() doesn't convert the string to an associative array, for example.

check() – this is really for you to include any validation you want to perform before the data is written to the database. The Table check() method is blank.

store() – The Table store() code will take the Table properties, match them to the column names of your table and write them to the database using a SQL INSERT or SQL UPDATE statement depending upon whether the primary key already exists or not.

Two other methods fall into the basic operations category:

save() – is roughly a concatenation of the bind(), check() and store() methods.

delete($key) – deletes the database record whose primary key is given by $key. Or if no parameter $key is passed then it will use the primary key already set in the Table properties (eg from a previous load() call). You can actually delete multiple records using this API call by passing a $key value which matches multiple records. However it's more usual to perform a load() and delete() for each individually, as this allows you to check that the user has permission to delete each record, and enables the Table class code to delete any associated asset record as well (see Table Advanced API Guide).

In summary, these methods can be used insert, update or delete database records:

  1. UPDATE
    • load() to load the existing record from the database, passing the primary key of the record
    • bind() to set the new values for the fields
    • check() to perform any validation
    • store() to save the new values to the database
  2. INSERT
    • bind() to set the field values (the previous load() isn't appropriate here)
    • check() to perform any validation
    • store() to save the new record to the database
  3. DELETE
    • load() to load the record
    • delete() to delete it

Sample Module Code

Below is the code for a simple Joomla module which you can install and run to demonstrate use of the basic Table functionality. If you are unsure about development and installing a Joomla module then following the tutorial at Creating a simple module will help.

In a folder mod_basic_table create the following 3 files:

mod_basic_table.xml

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.1" client="site" method="upgrade">
    <name>Basic Table demo</name>
    <version>1.0.1</version>
    <description>Code demonstrating basic use of Joomla Table class</description>
    <files>
        <filename module="mod_basic_table">mod_basic_table.php</filename>
        <filename>demomodules.php</filename>
    </files>
</extension>

mod_basic_table.php

<?php
defined('_JEXEC') or die('Restricted Access');

use Joomla\CMS\Factory;
use Joomla\CMS\Table\Table;

$app = Factory::getApplication();
$input = $app->input;

echo "Module id is {$module->id}<br>";

// Tell Joomla to look in the current (module) directory for our table class file
Table::addIncludePath(__DIR__);
$moduleTable = Table::getInstance('DemoModules', 'Table', array());

if ($moduleTable->load($module->id))
{
	echo "Module title is {$moduleTable->title}<br>";
	
	$moduleParams = json_decode($moduleTable->params, true);
	$moduleParams['header_tag'] = 'h2';

	$note = $input->get('demonote', '', "STRING");
	$data = array("note" => $note, "params" => $moduleParams); 

	$moduleTable->bind($data);

	$moduleTable->check();

	$moduleTable->store();
}

demomodules.php (our table class)

<?php
defined('_JEXEC') or die('Restricted access');
use Joomla\CMS\Table\Table;
class TableDemoModules extends Table

{
	
protected $_jsonEncode = array('params');

	function __construct(&$db)
	{
		parent::__construct('#__modules', 'id', $db);
	}
	
	function check()
	{
		$this->note .= " added via module";
	}
}

Zip up the mod_basic_table directory to create mod_basic_table.zip.

Within your Joomla administrator go to Install Extensions and via the Upload Package File tab select this zip file to install this sample mod_basic_table module.

Make this module visible by editing it (click on it within the Modules page) then:

  1. making its status Published
  2. selecting a position on the page for it to be shown
  3. on the menu assignment tab specify the pages it should appear on

When you visit a site web page then you should see the module in your selected position.

This modules demonstrates reading a record from the modules table then writing an update back to that record in the database.

All the modules are stored in the Joomla modules table, so we find the record which is associated with this basic table module. We do this by accessing the $module variable, which has $module->id property set to the id of the database record. This $module variable is available within the module itself (as is $params, which is why $moduleParams is used instead of $params as the variable set later in the code).

Some notes on the functionality of this module:

  • The module record is loaded from the database record and the title of the module output
  • The module params is json decoded into an associative array and the header tag is then set to 'h2' (which controls how the header title is displayed in the module on the web page)
  • The new note to be written to the module record is read from the demonote parameter in the URL, so you can set this yourself in the URL
  • The new note and params are written back to the database record using bind() and store() commands.
  • The params will be converted back to a json string because of the line protected $_jsonEncode = array('params'); in our table class
  • The check() function in our table class appends a string " added via module" to the note.

You can verify the changes to the database record by viewing the module record within the admin back-end.