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.

• Direct query (not recommended). You can simply write an SQL-statement and execute it.

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

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

• Using query builder (recommended). You may transparently build database queries. All the specific of database engines and database structure will automatically be taken in account. Example of usage:

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

See query builder page for details.

First consider the DML (Data Manipulation Language) part of the PunBB database layer class.

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__);

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
  }

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__);

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__);

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__);

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

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

• FIELDS: Fields is an array, with the keys being field names and the values being an array of data about the field. The field data contains a key datatype that should be mapped to one of the acceptable field types, a key allow_null which should be set to either true or false, and an optional key default which specifies a default value for the field.
• PRIMARY KEY: An array whose elements are field names from FIELDS.
• UNIQUE KEYS: An array with keys that act as the name of unique indexes and values that are arrays of field names (again, the field names should match field names in FIELDS).
• INDEXES: An array with keys that act as the name of indexes and values that are arrays of field names (again, the field names should match field names in FIELDS).
• ENGINE: This element is optional and is only used by MySQL. It specifies the database engine that MySQL should use in creating this table.

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.

• SERIAL (translates to INT(10) UNSIGNED AUTO_INCREMENT in MySQL)
• TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT (##) UNSIGNED (UNSIGNED and (##) are optional)
• VARCHAR/CHAR
• TINYTEXT/MEDIUMTEXT/TEXT/LONGTEXT
• FLOAT
• DOUBLE

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);

The create_table() function takes either two or three parameters. The first parameter is the name of the table to be added. The second is an array of schema for the table itself. The third parameter, which is optional, specifies whether or not to add the database prefix to the table name. If the third parameter is not specified or is set to false, the prefix will be prepended to the table name. If it is specified as true, the prefix will not be prepended.