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.

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