Documentation

1.3. Using the phpBB3.0 DBAL

Paul999

smithy_dll

$Id: v3_dbal.xml 44 2007-07-25 11:06:55Z smithy_dll $

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 
*
*/

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

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 
*
*/

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

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.

Example 1.3. An example for selecting data from the database.

				
<?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 
*
*/

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

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 
*
*/

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

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 
*
*/

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

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.

1.3.4. Inserting Data

1.3.5. Updating Data

1.3.6. Removing Data

1.3.7. Managing the Cache