Accessing Joomla Database With JTable

The JTable class is used in Joomla for creating, reading, updating, and deleting (CRUD) tasks for records in the database table. JTable provides many methods to make common manipulations to the table much simpler.

For example, one of the most common operations you will need to perform is to read a table row into memory given a value for the primary key. This can be done easily using the load method. The table row can then be just as easily updated using the save method, which also performs any predefined sanity checks on the table fields.

Each physical database table created should have a corresponding class derived from JTable to represent it. For example, for a database table "#__helloworld" with an auto increment primary key "id", the only thing you need to start using JTable is to create a constructor in extended JTable class.

JTable Class Extension

You have to add the following code in the file: admin/com_helloworld/tables/helloworld.php

class HelloWorldTableHelloWorld extends JTable
{
function __construct(&$db)
{
parent::__construct('#__helloworld', 'id', $db);
}
}

You can also use this functionality in plugins just as in components. As a general rule you should avoid writing to the database from a module, and use plugins or components for this.

Using JTable Class Extension

After you have added the table class, you can use it in model getTable() function.

public function getTable($type = 'HelloWorld', $prefix = 'HelloWorldTable', $config = array())
{
return JTable::getInstance($type, $prefix, $config);
}

You can also use JTable classes in any other Joomla extension by using JTable::addIncludePath() function.

JTable::addIncludePath(JPATH_ADMINISTRATOR . '/components/com_helloworld/tables');
$row = JTable::getInstance('helloworld', 'HelloWorldTable', array());

The lowercase version of the suffix of your class name is used as the first parameter. The prefix 'HelloWorldTable' is used as the second parameter.

The getInstance() member function of JTable returns a JTableObject by reference instead of a value.

Reserved Database Field Names

Some features of JTable require the existence of specially-named fields in the database table. If you require this additional functionality, ensure that these named fields are present in the table. These field names should be considered reserved as any attempts to use them for purposes other than those supported by JTable may result in conflict.

  • checked_out
  • checked_out_time
  • hits
  • ordering
  • published
<field name="state"
type="list"
label="JSTATUS"
description="JFIELD_PUBLISHED_DESC"
size="1"
default="1">
<option value="1">JPUBLISHED</option>
<option value="0">JUNPUBLISHED</option>
<option value="2">JARCHIVED</option>
<option value="-2">JTRASHED</option>
</field>
<field name="checked_out"
type="hidden"
filter="unset"
/>
<field name="checked_out_time"
type="hidden"
filter="unset"
/>
<field name="created_user_id"
label="JGLOBAL_FIELD_CREATED_BY_LABEL"
type="hidden"
filter="unset"
/>
<field name="created_time"
label="JGLOBAL_FIELD_CREATED_LABEL"
type="hidden"
filter="unset"
/>
<field name="modified_user_id"
label="JGLOBAL_FIELD_MODIFIED_BY_LABEL"
type="hidden"
filter="unset"
/>
<field name="modified_time"
label="JGLOBAL_FIELD_MODIFIED_LABEL"
type="hidden"
filter="unset"
/>
<field name="hits"
type="text"
id="hits"
class="readonly"
label="JGLOBAL_HITS"
size="20"
readonly="true"
filter="unset"
/>

Methods in JTable

__construct(string $table, mixed $key, \JDatabaseDriver $db)

addIncludePath(array|string $path = null) : array

appendPrimaryKeys(\JDatabaseQuery $query, mixed $pk = null) : void

attachObserver(\JObserverInterface|\JTableObserver $observer) : void

bind(array|object $src, array|string $ignore = array()) : boolean

check() : boolean

checkIn(mixed $pk = null) : boolean

checkOut(integer $userId, mixed $pk = null) : boolean

delete(mixed $pk = null) : boolean

getColumnAlias(string $column) : string

getDbo() : \JDatabaseDriver

getFields(boolean $reload = false) : mixed

getInstance(string $type, string $prefix = 'JTable', array $config = array()) : \Joomla\CMS\Table\Table|boolean

getKeyName(boolean $multiple = false) : mixed

getNextOrder(string $where = '') : integer

getObserverOfClass(string $observerClass) : \JTableObserver|null

getPrimaryKey(array $keys = array()) : array

getRules() : \JAccessRules

getTableName() : string

hasPrimaryKey() : boolean

hit(mixed $pk = null) : boolean

isCheckedOut(integer $with, integer $against = null) : boolean

load(mixed $keys = null, boolean $reset = true) : boolean

move(integer $delta, string $where = '') : boolean

publish(mixed $pks = null, integer $state = 1, integer $userId) : boolean

reorder(string $where = '') : mixed

reset() : void

save(array|object $src, string $orderingFilter = '', array|string $ignore = '') : boolean

setColumnAlias(string $column, string $columnAlias) : void

setDbo(\JDatabaseDriver $db) : boolean

setRules(mixed $input) : void

store(boolean $updateNulls = false) : boolean