Database table

by Elxis Team

Working with table rows and the elxisDbTable class

elxisDbTable is an abstact class that allows you to easily handle database table rows as objects. elxisDbTable extensions for the built-in extensions are stored inside the includes/libraries/elxis/database/tables/ folder. For the third party extensions they can be placed anywhere in Elxis filesystem. Moreover Elxis provides cross-database table definition system.

elxisDbTable

The most important methods of the elxisDbTable class

load

Load a table row as object by providing the value of the primary key column ($id)

public function load($id)

// Example: Load the category with catid = 4
$row = new categoriesDbTable();
$row->load(4);

bind

Bind an array (usually $_POST) to elxisDbTable object

public function bind($array)

// Example:
$row = new categoriesDbTable();
$row->bind($_POST);

getTable

Get the database table name

public function getTable()

// Example:
$row = new categoriesDbTable();
$row->getTable();
//will return #__categories

getColumns

Get the columms of the current table name as array

public function getColumns()

getPrimaryKey

Get the name of the table's primary key column

public function getPrimaryKey()

// Example:
$row = new categoriesDbTable();
$primary_key = $row->getPrimaryKey();
//will return catid

forceNew

Force new row. Be careful for dublicated entries if $reset_primary is false

public function forceNew($reset_primary=false)

store

Insert or update table row. For new rows an insert is executed, for existing ones an update.

public function store()

insert

Insert a new row into the database.

public function insert()

// Example: Insert a new row in table #__acl
$row = new aclDbTable();
$row->category = 'module';
$row->element = 'mod_sample';
$row->identity = 4;
$row->action = 'view';
$row->gid = 0;
$row->uid = 0;
$row->aclvalue = 1;
$row->insert();

update

Update a row.

public function update()

// Example: Load the category with catid = 4, change its title and save it into the database
$row = new categoriesDbTable();
$row->load(4);
$row->title = 'New title';
$row->update();

copy

Copy current row and save it into the database with a new primary key.

public function copy()

delete

Delete current row or any row by providing the primary key value.

public function delete($id='')

move

Move (re-order) a row one position UP or DOWN. Returns true on success, false on error.

public function move($inc, $wheres=array())

$inc should be -1 to move row up and 1 to move it down.
$wheres is an array of conditional statements to be passed in the SQL query.
Each element of this array is an array with 3 arguments:
- Column name
- Operator (available options: =, >, >=, <, <= and LIKE)
- Value
The AND operator will be used to join the $wheres elements.

elxisLoader::loadFile('includes/libraries/elxis/database/tables/menu.db.php');
$row = new menuDbTable();
$row->load(9);
$inc = -1;
$wheres = array(
array('collection', '=', $row->collection),
array('parent_id', '=', $row->parent_id),
array('section', '=', $row->section)
);
$row->move($inc, $wheres);

reorder

Reorder rows in a table.

public function reorder($wheres=array(), $fixall=false)

check

Checks the validity of the row column values before an insert or update and returns true or false.

protected function check()

getErrorMsg

Returns table's or database's last error message.

public function getErrorMsg()

Extending elxisDbTable

You can create extensions of the elxisDbTable like this:

class customDbTable extends elxisDbTable {
public function __construct() {}
public function check() {}
}

Note the DbTable suffix in the class name. The constructor of the customDbTable class should set the table name and table primary key in the parent class as well as define the table columns. The class can also have a check method that will be called automatically by the parent class during insert/update/store to validate the data before trying to save them.


Defining table columns

In the constructor of the extension class you can define the table columns by providing an array to the columns property. This array must have as keys the column name and as values an array with the column type and initial value. The supported column types are the following:

  • integer For table columns of type integer (any size) (PDO::PARAM_INT)
  • bit For columns of type integer having as value 0 or 1 (PDO::PARAM_INT)
  • numeric For numeric columns, such as floats. (PDO::PARAM_STR)
  • string For table columns containing strings as values such as VARCHAR. (PDO::PARAM_STR)
  • text For table columns containing text as values such as TEXT/BLOB. (PDO::PARAM_LOB)
  • binary For table columns containing binary data (PDO::PARAM_LOB)

Example

public function __construct() {
parent::__construct('#__custom', 'id');
$this->columns = array(
'id' => array('type' => 'integer', 'value' => null),
'title' => array('type' => 'string', 'value' => null),
'message' => array('type' => 'text', 'value' => null),
'published' => array('type' => 'bit', 'value' => 0)
);
}

Generic table

A generic table handler.

class genericDbTable extends elxisDbTable

It has been read 5848 times
Prepared statements
Next article
Prepared statements

You are free to copy, distribute and transmit the articles in this site for non-commercial purposes.
Creative Commons 3.0