Translations of this page: en bg cs de fi fr hu it ja pl ru tr zh

This is an old revision of the document!


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.

Performing a query

  • 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.

Data Manipulation with query builder

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

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

Personal Tools