Using the phpBB3.0 DBAL

Abstract This section describes the phpBB3 Database Abstraction Layer system.

phpBB uses the DBAL to execute all database Queries.

1.3.1. Initialising a Connection
To use the functions of the DBAL class, you need to instantiate the DBAL class and create a connection. Before you can do so, you need to include the correct DBAL driver from /includes/db/.


 * Important
 * The variable $dbms must be set to the name of the used driver. This variable is needed in /includes/db/dbal.php to set $sql_db.


 * Tip
 * If you use the variable $dbms from config.php, you can use $sql_db to instantiate the class. See Example 1.2, “An example to initialise the :connection with config.php” for an example on how to do that.


 * Tip
 * If you include common.php the DBAL is automatically instantiated and initialised, so you don't need to do that. The object is called $db.

After you have included all needed files, you can create the new connection. First you need to instantiate the class by creating a new db object ($db = new $sql_db;. Then you can initialise it with $sql_db, or with dbal_DRIVER_NAME. After that you can call sql_connect to create the connection.

Example 1.1. A sample to initialise the connection without using config.php (don't do that at home)
<?php /**
 * @package phpBB3
 * @version $Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

/** define('IN_PHPBB', true); $phpbb_root_path = './'; $phpEx = substr(strrchr(__FILE__, '.'), 1); $dbms = 'mysql';
 * @ignore

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

$db = new $sql_db; // we're using bertie and bertiezilla as our example user credentials. You need to fill in your own ;D $db->sql_connect('localhost', 'bertie', 'bertiezilla', 'phpbb', '', false, false);

?>

In this example the config.php file isn't used. All data is directly passed to the sql_connect function to illustrate the system.

Parameters for sql_connect

FIRST PARAMETER, HOST The host of the database. When using config.php you should use $dbhost instead.

SECOND PARAMETER, DATABASE USER The database user to connect to the database. When using config.php you should use $dbuser instead.

THIRD PARAMETER, DATABASE PASSWORD The password for the user to connect to the database. When using config.php you should use $dbpasswd instead.

FOURTH PARAMETER, DATBABASE NAME The database where the phpBB tables are located. When using config.php you should use $dbname instead.

FIFTH PARAMETER, DATABASE PORT The port to the database server. Leave empty/false to use the default port. When using config.php you should use $dbport instead.

SIXTH PARAMETER, PERSISTENCE Database connection persistence, defaults to false.

SEVENTH PARAMETER, NEW LINK Use a new connection to the database for this instance of the DBAL. Defaults to false.

Example 1.2. An example to initialise the connection with config.php
<?php /**
 * @package phpBB3
 * @version $Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

/** define('IN_PHPBB', true); $phpbb_root_path = './'; $phpEx = substr(strrchr(__FILE__, '.'), 1);
 * @ignore

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

$db = new $sql_db;

$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false);

// We do not need this any longer, unset for safety purposes unset($dbpasswd);

?>

This second example is using the config.php file for the correct parameters to connect to the database. The parameters are the same as noted above. The unset is used to be sure that the $dbpasswd can not be stolen later in the script.

1.3.2. Selecting Data
For selecting basic data from the database, the function sql_query is enough. If you want to use any variable in your query, you should use (If it isn't a integer) $db->sql_escape to be sure the data is safe.

<?php /**
 * @package phpBB3
 * @version $Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

/** define('IN_PHPBB', true); $phpbb_root_path = './'; $phpEx = substr(strrchr(__FILE__, '.'), 1);
 * @ignore

include($phpbb_root_path . 'common.' . $phpEx);

$integer = 0; $data = "This is ' some data";

$sql = 'SELECT * FROM '. POSTS_TABLE. ' 	WHERE post_id = '. (int) $integer. " 		AND post_text = '". $db->sql_escape($data). "'"; $result = $db->sql_query($sql); ?>

In this example there are 2 variables, one integer and one string with a apostrophe in the string. The variable $integer is cast to int, to be sure it really is an integer. The string is passed to $db->sql_escape to ensure that the string is correctly escaped. $db->escape is different for every DBAL driver and written specially for that driver, to be sure all characters that need escaping are escaped. $db->sql_query returns a query result.


 * Tip
 * If you want to use the build in cache function, you should set the second parameter for $db->sql_query. This should be the time (In seconds) to cache the data returning from that query. You could only use this cache on SELECT queries.


 * Tip
 * When you want to use LIMIT in a query, you should use $db->sql_query_limit instead of $db->sql_query. See Example 1.4, “An example for using sql_query_limit” for information on how to use it.

Example 1.4. An example for using sql_query_limit
<?php /**
 * @package phpBB3
 * @version $Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

/** define('IN_PHPBB', true); $phpbb_root_path = './'; $phpEx = substr(strrchr(__FILE__, '.'), 1);
 * @ignore

include($phpbb_root_path . 'common.' . $phpEx);

$integer = 0; $data = "This is ' some data";

// Variable for query_limit // Start with item 10 $start = 10; // Select 5 rows $number = 5

$sql = 'SELECT * FROM '. POSTS_TABLE. ' 	WHERE post_id = '. (int) $integer. " 		AND post_text = '". $db->sql_escape($data). "'"; $result = $db->sql_query_limit($sql, $number, $start); ?>

This example use $db->sql_query_limit to select the data from the database with a limit clause. In this example, there are 5 items selected, started from item 10. The second parameter is required, the third parameter is optional. $db->sql_query_limit returns a query result, just like $db->sql_query does.


 * Tip
 * If you want to use the cache with $db->sql_query_limit, you need to specify the time to cache as fourth parameter.

1.3.3. Building Queries
phpBB 3 offers powerful functions to assemble queries in a secure, re-usable and cross-database compatible way. This section will introduce you to the most important ones.

Example 1.5. Using sql_build_array
<?php /**
 * @package phpBB3
 * @version $Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License

/** define('IN_PHPBB', true); $phpbb_root_path = './'; $phpEx = substr(strrchr(__FILE__, '.'), 1);
 * @ignore

include($phpbb_root_path . 'common.' . $phpEx);

//Array with the data to insert $data = array(	'username' 	=> 'Bertie',	'email' 	=> 'bertie@bertie.com', );

// First doing a select with this data. // Note: By using the SELECT type, it uses always AND in the query. $sql = 'SELECT user_password FROM '. USERS_TABLE. '	WHERE '. $db->sql_build_array('SELECT', $data); $result = $db->sql_query($sql);

// And doing an update query: (Using the same data as for SELECT) $sql = 'UPDATE '. USERS_TABLE. ' SET '. $db->sql_build_array('UPDATE', $data); $db->sql_query($sql);

// And as last, a insert query $sql = 'INSERT INTO '. USERS_TABLE. ' ' . $db->sql_build_array('INSERT', $data); $db->sql_query($sql); ?>

The $db->sql_build_array function can create easy UPDATE, INSERT, MULTI_INSERT, INSERT_SELECT and SELECT queries from a array.


 * Important
 * Use MULTI_INSERT only in combination with $db->sql_multi_insert. For more information, see the part about sql_multi_insert later.

The first parameter for sql_build_array is the query type (UPDATE, INSERT, MULTI_INSERT, INSERT_SELECT or SELECT). The second parameter is a associative array with the items to add to the query. The key of the array is the field name, the value of the array is the value for that field.


 * Important
 * If you want to use a integer as value, you need to cast the value explicitly to an integer.


 * Important
 * When you use $db->sql_build_array for SELECT queries, note that this will be using only AND in the query's WHERE clause, so the query as used in the example above will be like this:SELECT user_password FROM phpbb_users WHERE username = 'Bertie' AND email = 'bertie@bertie.com'


 * Tip
 * If you want to build SELECT queries with JOINS or SELECT DISTINCT, you should use $db->sql_build_query instead.