phpBB 3.0 has a so-called DBAL. DBAL stands for database abstraction layer. It basicly means that all code is database-independent and you can use it (phpbb) with many differen't databases. This code is located in includes/db/. There there also is a file called db_tools.php, which includes some tools for modifying tables. Modders should always use the dbal.

How to use the DBAL

When coding in PHP, if you want to do a mysql query, you usually use the mysql_query() function. In phpBB 3.0 you use the $db->sql_query() method. The DBAL works like this: includes/db/dbal.php contains the base functions, includes/db/{dbms}.php extends the dbal class and contains the dbms-specific stuff. Like the sql_query method.

Here's a list of methods you can use:

• sql_affectedrows - should hardly be used
• sql_connect - already used in common.php
• sql_escape - used to escape characters
• sql_fetchrow
• sql_freeresult
• sql_nextid - get primary key value from INSERT statement
• sql_query
• sql_rowseek
• sql_server_info

And here are those from the base class:

• sql_build_array - build insert or update statements
• sql_build_query - build a query
• sql_close - close the connection
• sql_fetchfield - fetch a single field from a select
• sql_fetchrowset - fetch a whole fieldset
• sql_in_set - helper for IN expressions
• sql_like_expression - helper for LIKE expressions
• sql_multi_insert - insert into multiple tables
• sql_num_queries - number of queries so far
• sql_query_limit - LIMIT query
• sql_report

sql_query

sql_query is used to query an sql database. If you've worked with mysql before, you probably know the function mysql_query(). Now, in phpBB 3.0, instead of the following:

Code: Select all

$sql = 'SELECT *
   FROM ' . USERS_TABLE;
$result = mysql_query($sql);

You do:

Code: Select all

$sql = 'SELECT *
   FROM ' . USERS_TABLE;
$result = $db->sql_query($sql);

sql_escape

sql_escape is used to secure data for an SQL query. For mysql, the equivalent would be mysql_real_escape_string(). For numbers, you cast the variable to an integer ($var = (int) $var). For strings, we use sql_escape. Here's an example:

Code: Select all

$username = 'eviL<3';
$sql = 'SELECT *
   FROM ' . USERS_TABLE . '
   WHERE username = \'' . $db->sql_escape($username) . '\'';
$result = $db->sql_query($sql);

sql_fetchrow

To sql_fetchrow you pass a result, and it will get a row. The row is an associative array of all selected columns. Or if there were no columns, it returns false. If we'd want to the row from the previous query we'd do:

Code: Select all

$row = $db->sql_fetchrow($result);

sql_freeresult

When we do an sql SELECT query, PHP internally stores the result somewhere. For memory reasons you should always free results after fetching the needed data. Here's how you'd do that:

Code: Select all

$db->sql_freeresult($result);

sql_nextid

This is a special method. When you do an INSERT action, you don't know the primary key value of the inserted row. This function returns it.

sql_build_array

This is a handy one, very useful for building INSERT/UPDATE queries. Here's an example:

Code: Select all

$sql_ary = array_merge($sql_ary, array(
   'data_time'      => time(),
   'data_poster'   => (int) $user->data['user_id'],
));

$sql = 'INSERT INTO ' . BLOG_DATA_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);
$db->sql_query($sql);

Or for an UPDATE query:

Code: Select all

$sql = 'UPDATE ' . BLOG_DATA_TABLE . ' SET ' . $db->sql_build_array('UPDATE', $sql_ary) . ' WHERE data_id = ' . $data_id;
$db->sql_query($sql);

It is also possible to use that method for other types of queries. The following are the possible values for the first parameter: INSERT, INSERT_SELECT, MULTI_INSERT, UPDATE, SELECT.

sql_fetchfield

This is used to get a single field from a query. This is useful if you only selected one field anyway. Only argument passed is the field name. Here's an example:

Code: Select all

$sql = "SELECT $db_field as post_exists
   FROM $db_table
      WHERE $db_field = $del_id";
$result = $db->sql_query($sql, 1);
$post_exists = (bool) $db->sql_fetchfield('post_exists');
$db->sql_freeresult($result);

sql_fetchrowset

Works like sql_fetchrow, but gets the whole rowset.

sql_in_set

This is used for creating sql IN() statements. The first argument is the field name, the second is an array of items. The third optional argument, which defaults to false will create a "NOT IN()" statement, if set to true. Here is an example query, that selects all topics from a list of forums:

Code: Select all

$forum_list = array(1, 2, 3);
$sql = 'SELECT topic_id, forum_id, topic_title, topic_poster, topic_time, topic_first_poster_name, topic_first_poster_colour
   FROM ' . TOPICS_TABLE . '
   WHERE ' . $db->sql_in_set('forum_id', $forum_list) . '
      AND topic_approved = 1
         AND topic_status = ' . POST_NORMAL . '
   ORDER BY topic_time DESC';
$result = $db->sql_query_limit($sql, $limit);
$rowset = $db->sql_fetchrowset($result);
$db->sql_freeresult($result);

sql_like_expression

This is hardly ever used. It's needed to build a LIKE query. Here's a part of a query, taken from memberlist.php:

Code: Select all

$sql_where .= ($username) ? ' AND u.username_clean ' . $db->sql_like_expression(str_replace('*', $db->any_char, utf8_clean_string($username))) : '';

sql_query_limit

This works just like sql_query. The first argument is the SQL, the second is the limit, the third is the offset. Note that in mysql the query would be "LIMIT [{offset},] {limit}". The [] mean that it's optional, so is the argument for the dbal method.

How to use db_tools

Before we can use the phpbb_db_tools class, we need to include the file. Like this:

Code: Select all

include($phpbb_root_path . 'includes/db/db_tools.' . $phpEx);

Now we can create a new phpbb_db_tools object. Our $db object is passed by reference in the constructor.

Code: Select all

$db_tools = new phpbb_db_tools($db);

Now, what can we actually do with it? Here's a list of methods:

• sql_column_add
• sql_column_change
• sql_column_exists
• sql_column_remove
• sql_create_index
• sql_create_primary_key
• sql_create_unique_index
• sql_index_drop
• sql_list_index

You will probably only even need the sql_column_add method. If you're a MOD author, and you want to add an automatic installer, you could do something like this:

Code: Select all

$db_tools->sql_column_add(USERS_TABLE, 'user_gender', array('INT:2', 0));

More details on how the third argument is structured can be found in this article.

Table constants

phpBB 3.0 uses so-called "table constants". In php you can define constants. phpBB defines most of them in includes/constants.php. So if you take a look at that file, you'll find the following:

Code: Select all

// Table names
define('ACL_GROUPS_TABLE',         $table_prefix . 'acl_groups');
define('ACL_OPTIONS_TABLE',         $table_prefix . 'acl_options');
define('ACL_ROLES_DATA_TABLE',      $table_prefix . 'acl_roles_data');

And so on .

What does this do? It basicly gives every table a nice name, so we can access that from everywhere. So instead of writing:

Code: Select all

'SELECT * FROM ' . $table_prefix . 'users';

We write:

Code: Select all

'SELECT * FROM ' . USERS_TABLE;

What does this mean for a MOD author? It means scroll down . And at the very end of the constants.php file, you'll find:

Code: Select all

// Additional tables

After this we can add our modifications custom tables. You should always use table constants. It will also save you from having to global $table_prefix all the time.