Database Abstraction Layer

phpBB Olympus uses a database abstraction layer to access the database instead of directly calling e.g. php:mysql_query functions. You usually access the DBAL using the global variable $db. This variable is defined in common.php: require($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx); ... $db			= new $sql_db;

Connecting and disconnecting
Use these methods only if you cannot include common.php (to connect) or run garbage_collection (to disconnect; you may also use other functions that run this function, for example page_footer).

sql_connect
Connects to the database. Defined in dbal_* class.

Example: include($phpbb_root_path . 'includes/db/mysql.' . $phpEx);

$db = new dbal_mysql; // 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);

Example using config.php: 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); 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.


 * Parameters

sql_close
Disconnects from the DB. Defined in dbal class (_sql_close is defined in dbal_* class).

Example: $db->sql_close;

sql_build_array
Builds SQL statement from array. Possible types of queries: INSERT, INSERT_SELECT, UPDATE, SELECT. Defined in dbal class.

Example: //Array with the data to insert $data = array(	'username' 	=> 'Bertie',	'email' 	=> 'bertie@example.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). ' WHERE user_id = '. (int) $user_id; $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);


 * Parameters

Note: As of phpBB 3.0.3, MULTI_INSERT is no longer allowed as a query type for sql_build_array. Coders should use sql_multi_insert instead.

sql_build_query
Builds full SQL statement from array. Possible types of queries: SELECT, SELECT_DISTINCT Defined in dbal class.


 * See dbal::sql_build_query manual page.

sql_in_set
Builds IN, NOT IN, = and <> sql comparison string. Defined in dbal class.

Example: $sql_in = array(2, 58, 62);

$sql = 'SELECT * FROM '. USERS_TABLE. '	WHERE '. $db->sql_in_set('user_id', $sql_in);


 * Parameters

sql_escape
Escapes a string in a SQL query. sql_escape is different for every DBAL driver and written specially for that driver, to be sure all characters that need escaping are escaped. Defined in dbal_* class.

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


 * Parameters

sql_query
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. Defined in dbal_* class.

Example: $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);


 * Parameters

sql_query_limit
Gets/changes/deletes only selected number of rows. Defined in dbal class (_sql_query_limit is defined in dbal_* class).

Example: $start = 25; $sql = 'SELECT * FROM '. POSTS_TABLE. ' 	WHERE topic_id = 1045'; $result = $db->sql_query_limit($sql, $config['topics_per_page'], $start);


 * Parameters

sql_multi_insert
Builds and runs more than one insert statement. Defined in dbal class.

Example: // Users which will be added to group $users = array(11, 57, 87, 98, 154, 211); $sql_ary = array;

foreach ($users as $user_id) {	$sql_ary[] = array(		'user_id'		=> (int) $user_id,		'group_id'		=> 154,		'group_leader'	=> 0,		'user_pending'	=> 0,	); }

$db->sql_multi_insert(USER_GROUP_TABLE, $sql_ary);


 * Parameters

Methods useful after running INSERT and UPDATE queries
All methods in this part of article are defined in dbal_* class.

sql_affectedrows
Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query.

Example: $sql = 'DELETE FROM '. TOPICS_TRACK_TABLE. "	WHERE user_id = {$user->data['user_id']}"; $db->sql_query($sql);

$affected_rows = $db->sql_affectedrows;

sql_nextid
Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

Example: $sql = 'INSERT INTO '. USERS_TABLE. ' ' . $db->sql_build_array('INSERT', $user_ary); $db->sql_query($sql);

$user_id = $db->sql_nextid;

sql_fetchfield
Fetches field. Defined in dbal class.

Example: $sql = 'SELECT COUNT(post_id) AS num_posts FROM '. POSTS_TABLE. "	WHERE topic_id = $topic_id		AND post_time >= $min_post_time	". (($auth->acl_get('m_approve', $forum_id)) ? '' : 'AND post_approved = 1'); $result = $db->sql_query($sql);

$total_posts = (int) $db->sql_fetchfield('num_posts');


 * Parameters

sql_fetchrowset
Returns an array with the result of using $db->sql_fetchrow on every row. Defined in dbal class.


 * Parameters

sql_fetchrow
Fetches current row. Defined in dbal_* class.

Example: $sql = 'SELECT * FROM '. TOPICS_TABLE. '   WHERE topic_id = 1045'; $result = $db->sql_query($sql);

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

Example with a while-loop: $sql = 'SELECT config_name, config_value FROM '. CONFIG_TABLE; $result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result)) {	$config[$row['config_name']] = $row['config_value']; }


 * Parameters

sql_rowseek
Seeks to given row number. The row number is zero-based. Defined in dbal_* class.


 * Parameters

sql_freeresult
Clears result of SELECT query. Defined in dbal_* class.

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

// Fetch the data $post_data = $db->sql_fetchrow($result);

// We don't need to do anything with our query anymore, so lets set it free $db->sql_freeresult($result);


 * Parameters