PunBB 1.3 database helpers

PunBB has an implementation of the database layer class. It takes into account the forum configuration and works with different databases. On include/common.php inclusion an instance of this database layer named $forum_db is being created in global scope to provide database helpers.

Please remember the following when use PunBB database layer:

Performing a query

 $result = $forum_db->query('SELECT * FROM '.$forum_db->prefix.'topics WHERE id = 10');

Be sure, that your SQL code is cross-compatible with all database engines supported by PunBB.

$query = array(
   'SELECT'  => '*',
   'FROM'    => 'topics',
   'WHERE'   => 'id = 10'
);
$result = $forum_db->query_build($query);

Let's discuss how to use the query builder.

Query Builder: Data Manipulation

SELECT

The following entries can be used in SELECT queries: FROM, JOINS, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

// Taken from include/search_idx.php
$query = array(
   'SELECT'   => 'word_id',            // field names
   'FROM'     => 'search_matches',     // table name
   'WHERE'    => 'word_id IN('.implode(',', $word_ids).')',
   'GROUP BY' => 'word_id, subject_match',
   'HAVING'   => 'COUNT(word_id)=1'
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);
// Taken from admin/reindex.php
$query = array(
   'SELECT'   => 'p.id, p.message, t.id, t.subject, t.first_post_id',
   'FROM'     => 'posts AS p',
   'JOINS'    => array(
      array(
         'INNER JOIN' => 'topics AS t',
         'ON'         => 't.id=p.topic_id'
      )
   ),
   'WHERE'    => 'p.id>='.$start_at,
   'ORDER BY' => 'p.id',
   'LIMIT'    => $per_page
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

How to work with query results

For example, we have this query:

$query = array(
   'SELECT'  => 't.id, t.poster, t.subject, t.posted',
   'FROM'    => 'topics AS t',
   'WHERE'   => 't.forum_id = 1'
);
$result = $forum_db->query_build($query) or error(__FILE__, __LINE__);

To know how many rows this query returns, use this:

  $forum_db->num_rows($result);

To fetch the current row into an associative array:

  $data = $forum_db->fetch_assoc($result);
  //An example of getting topic_id
  $topic_id = $data['id'];

To fetch the current row into a numeric array:

  $data = $forum_db->fetch_row($result);
  //An example of getting topic_id
  $topic_id = $data[0];

To fetch only some values from the current row:

  //This code will fetch only the topic id and the topic subject
  list($id,, $subject,) = $forum_db->fetch_row($result);

To process all rows in a set you can use this code:

  while ($cur_row = $forum_db->fetch_assoc($result))
  {
    //Actions with $cur_row
  }

INSERT

The following entries allowed in INSERT queries: INTO, VALUES.

// Taken from admin/categories.php
$query = array(
   'INSERT'   => 'cat_name, disp_position',  // field names
   'INTO'     => 'categories',               // table name
   'VALUES'   => '\''.$forum_db->escape($new_cat_name).'\', '.$new_cat_pos  // inserting values
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

UPDATE

The following entries allowed in UPDATE queries: SET, WHERE.

// Taken from admin/censoring.php
$query = array(
   'UPDATE'   => 'censoring',  // table name
   'SET'      => 'search_for=\''.$forum_db->escape($search_for).'\', replace_with=\''.$forum_db->escape($replace_with).'\'',
   'WHERE'    => 'id='.$id
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

DELETE

Only WHERE entry is allowed for DELETE queries.

// Taken from admin/bans.php
$query = array(
   'DELETE'   => 'bans',  // table name
   'WHERE'    => 'id='.$ban_id
);
$forum_db->query_build($query) or error(__FILE__, __LINE__);

Data Definition

Now let's consider the DDL (Data Definition Language) part of the PunBB database layer class.

Tables

function table_exists($table_name, $no_prefix = false)

This function returns true if the table with $table_name name exists and false otherwise.

function create_table($table_name, $schema, $no_prefix = false)

Takes either two or three parameters and creates a database table. The first parameter is the name of the table to be added. The second is an array of schema for the table itself (see the explanation and example below). The third is the optional parameter $no_prefix.

function drop_table($table_name, $no_prefix = false)

Deletes the table that has the $table_name name.

Creating a table

Table structure

The table structure is described by an array which accepts following parameters:

Acceptable field types

When adding a column or creating a new table, you must specify the data type for each field. The following data-types are acceptable to use in the helper functions (create_table() and add_field()). These data-types are guaranteed to be translated properly when used.

Note: Don't use $query in extension code that has global scope as this variable is used in the core and might interfere with it. Rather use a specific variable for your script to avoid errors.

//Taken from install.php
$schema = array(
   'FIELDS'      => array(
       'id'         => array(
            'datatype'     => 'SERIAL',
            'allow_null'   => false
         ),
         'cat_name'        => array(
            'datatype'     => 'VARCHAR(80)',
            'allow_null'   => false,
            'default'      => '\'New Category\''
         ),
         'disp_position'   => array(
            'datatype'     => 'INT(10)',
            'allow_null'   => false,
            'default'      => '0'
         )
      ),
      'PRIMARY KEY'   => array('id')
   );

After creating the schema, a method of the database class is used:

$forum_db->create_table('categories', $schema);

Fields

function field_exists($table_name, $field_name, $no_prefix = false)

Checks if the field $field_name is present in the table $table_name.

function add_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)

Adds a field to the $table_name table. One can specify the name of an existent field, $after_field, to insert after it the field to be added. The meaning of the other parameters is clear from its names.

function alter_field($table_name, $field_name, $field_type, $allow_null, $default_value = null, $after_field = null, $no_prefix = false)

Modifies the $field_name field. The meaning of parameters is the same as above.

function drop_field($table_name, $field_name, $no_prefix = false)

Removes the $field_name field from the $table_name table.

Here are examples of the fields manipulation.

// Taken from admin/db_update.php
$forum_db->alter_field('posts', 'poster_ip', 'VARCHAR(39)', true);
$forum_db->alter_field('users', 'registration_ip', 'VARCHAR(39)', false, '0.0.0.0');
// Taken from the pun_pm extension
// Install section
if (!$forum_db->field_exists('users', 'pun_pm_new_messages'))
   $forum_db->add_field('users', 'pun_pm_new_messages', 'INT(10)', true);
 
// Uninstall section
$forum_db->drop_field('users', 'pun_pm_new_messages');

Indexes

function index_exists($table_name, $index_name, $no_prefix = false)

Checks if the index $index_name is present in the table $table_name.

function add_index($table_name, $index_name, $index_fields, $unique = false, $no_prefix = false)

Adds an index to the $table_name table. $index_fields is the array of fields to be added to the index.

function drop_index($table_name, $index_name, $no_prefix = false)

Removes the $index_name index from the $table_name table.

Here are examples of the indexes manipulation.

// Taken from admin/db_update.php
if (!$forum_db->index_exists('online', 'user_id_ident_idx'))
{
   ...
   $forum_db->add_index('online', 'user_id_ident_idx', array('user_id', 'ident'), true);
   ...
}
 
$forum_db->drop_index('online', 'user_id_idx');