Function Reference

These functions are used by the phpBB Database Abstraction Layer to interface with the PHP functions and the database itself.

A summary of these functions and their main uses follows. All data in this section is based on phpBB 2.0.6 DBAL files (but is consistent across most phpBB 2.0 versions). Unlike DBAL variables, each function is supported for each type of database. The behavior of the functions may vary, but one can be certain that they are there. Each function is described in the following manner.

Usage (How to call the function.)

Also, please see these special notes about some functions.

Functions Index

1. sql_db - Open a database connection
2. sql_close - Close a database connection
3. sql_query - Execute a query
4. sql_numrows - Get the number of rows in a result
5. sql_affectedrows - Get the number of rows changed
6. sql_numfields - Get the number of fields
7. sql_fieldname - Get the name of a table column
8. sql_fieldtype - Get the type of a table column
9. sql_fetchrow - Get a result row
10. sql_fetchrowset - Get all result rows
11. sql_fetchfield - Get a result field
12. sql_rowseek - Find a result row
13. sql_nextid - Get the last ID number generated
14. sql_freeresult - Free result memory
15. sql_error - Get the last SQL error message

Special Parameter Note

Most of the DBAL functions have an optional parameter called query_id. This parameter, when used in calling a function, should be a resource link identifier associated with the result of an SQL query. If the parameter is not used, then usually the resource link identifier of the last executed query will be used by default. When no queries have been executed, these functions will return false.

In some cases, the functions for some database types may use this parameter while functions for other database types may use a connection resource or link identifier instead. The latter will also not check to see if any queries have been executed as noted above. They will instead return false if there is no open database connection. Use of the query_id parameter is not recommended when dealing with the affected functions, and this will be noted in the individual function documentation when it applies.

Resource link identifiers can be returned by the sql_query function. Connection resources or link identifiers can be returned by the sql_db function.

About Oracle

Please note that references to the DBAL for Oracle databases are included, but official support for Oracle was removed in phpBB 2.0.14.

$db = new sql_db('localhost', 'username', 'password', 'database');

Used to establish a database connection. In MySQL, MS SQL, and PostgreSQL, it will also attempt to select the current working database (as stored in $dbname via config.php). If this attempt fails in MySQL or MS SQL, the database connection will be closed.

If successful, sql_db will return a connection resource or link identifier (as stored in $db->db_connect_id). If a connection could not be established (or was established and closed), sql_db returns false.

sqlserver is the address of the database server. sqluser is the username on that server and sqlpassword is the password for that account. database is the database name. All of those details are stored in config.php after installing phpBB. persistency specifies if the script should open a persistent connection to the database server (this has no effect if the database software does not support such connections).

If the result of sql_db is stored in a variable other than $db, that variable should be used when using other functions or variables.

$connection = new sql_db('localhost', 'username', 'password', 'database');
if(!$connection->db_connect_id)
{
   die("Could not connect to the database");
}

sql_db is called near the bottom of db.php using the database details defined in config.php.

See Also: odbc_connect, odbc_pconnect, mssql_connect, mssql_pconnect, mysql_connect, mysql_pconnect, OCINLogon, OCIPLogon, pg_connect, pg_pconnect, mssql_select_db, mysql_select_db, mssql_close, mysql_close

$db->sql_close();

Used to close a database connection. In MySQL 4, Oracle, PostgreSQL, MS SQL, MS SQL (ODBC), and MS Access any uncompleted transactions will be finished before the connection is closed. For MySQL 3, Oracle, PostgreSQL, MS SQL (ODBC), MS Access, and ODBC, all memory associated with the most recent query result will be freed before the connection is closed, also.

If there is no open connection to close or the connection fails to close, the function will return false. Otherwise the function can be expected to return true.

Although not necessary in most cases, sql_close() should be called before the end of any script. The function is called in page_tail.php to close the normal phpBB database connection.

See Also: odbc_close, odbc_free_result, odbc_commit, mssql_query, mssql_close, mysql_free_result, mysql_close, mysql_query, OCICommit, OCIFreeStatement, OCILogoff, pg_exec, pg_freeresult, pg_close

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

Used to execute an SQL query on the database. query must be a valid SQL query for your database type. On database layers other than MySQL, portions of queries may be translated into the form recognized by that database. SQL queries containing the LIMIT keyword are particularly subject to this.

transaction has no use in the ODBC layer. In other database layers, it is used to indicate the beginning or ending of an SQL transaction as supported by that database software. Values that transaction may take are false (the default) and the phpBB constants BEGIN_TRANSACTION and END_TRANSACTION.

$result = $db->sql_query($sql, BEGIN_TRANSACTION);
$result = $db->sql_query($sql, END_TRANSACTION);

Information stored in the class for any previous query will be removed each time sql_query is called, so it is important to store any relevant result data in your own variables before calling sql_query again. For some database layers, the result data will be stored in variables of the class when sql_query is used. The result data should not be accessed through these variables as this does not happen on MySQL and some other layers.

The value of num_queries will be increased by one each time sql_query is called. The function may return true, false, or a resource link identifier associated with the result of an SQL query.

See Also: odbc_exec, odbc_num_fields, odbc_field_name, odbc_field_type, odbc_fetch_row, odbc_result, odbc_num_rows, odbc_commit, odbc_autocommit, odbc_free_result, odbc_rollback, mssql_query, mssql_data_seek, mssql_fetch_array, mysql_query, OCIParse, OCIExecute, OCICommit, OCIRollback, pg_exec

$num_rows = $db->sql_numrows($result);
$num_rows = $db->sql_numrows();

Used to get the number of rows in the result set of an executed SQL query. The function should only be used for this when dealing with a SELECT query statement. For INSERT, UPDATE and DELETE queries, use sql_affectedrows instead.

See the special note for information on the query_id parameter.

See Also: mssql_num_rows, mysql_num_rows, OCIFetchStatement, OCIExecute, pg_numrows

$affected_rows = $db->sql_affectedrows($result);
$affected_rows = $db->sql_affectedrows();

Used to get the number of rows affected by an executed SQL query. This function should only be used to get the number of rows changed by INSERT, UPDATE and DELETE queries, as it only returns the number of modified rows. When dealing with DELETE queries that remove all records from a table, the function may return zero.

For the number of rows returned by SELECT queries, use sql_numrows. On the ODBC database layer, this function is identical to sql_numrows.

See the special note for information on the query_id parameter. Using this parameter is not recommended for this function. It will not be used by the MS Access, MS SQL, MS SQL (ODBC), or MySQL database layers.

See Also: mysql_affected_rows, OCIRowCount, pg_cmdtuples

$numfields = $db->sql_numfields($result);
$numfields = $db->sql_numfields();

Used to get the number of fields (or table columns) in the result set of an executed SQL query. See the special note for information on the query_id parameter.

See Also: mssql_num_fields, mysql_num_fields, OCINumCols, pg_numfields

$field_name = $db->sql_fieldname($offset, $result);

Used to get the name of a specified field (or table column) in a query result. offset is the numerical offset of the field in an array based on the number zero. The offset of the first field is 0, the offset of the second field is 1, and so on. On Oracle, offset will be increased by one for compatibility with the PHP function OCIColumnName.

Field names returned may be case-sensitive, especially on MySQL.

See the special note for information on the query_id parameter.

See Also: mssql_field_name, mysql_field_name, OCIColumnName, pg_fieldname

$field_type = $db->sql_fieldtype($offset, $result);

Used to get the SQL type of a specified field (or table column) in a query result. SQL types may vary depending on database software, but possible types may include "int," "real," "string," "blob," "char," "varchar," "time," "year," "datetime," "date," "timestamp," "set," "enum," and "text."

offset is the numerical offset of the field in an array based on the number zero. The offset of the first field is 0, the offset of the second field is 1, and so on. On Oracle, offset will be increased by one for compatibility with the PHP function OCIColumnName. The ODBC database layer should follow a similar behavior, but does not due to a phpBB bug.

See the special note for information on the query_id parameter.

See Also: odbc_field_type, mssql_field_type, mysql_field_type, OCIColumnType, pg_fieldtype

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

Returns an array containing a single row from the result set of an SQL query. See the special note for information on the query_id parameter.

When using MS SQL, MySQL 3, or PostgreSQL, the array will contain the result data in both numeric indices (e.g., $row[0]) and associative indices (e.g., $row['user_id']), using the field names as keys. On MySQL 4 and Oracle, the array will use only associative indices.

On MS SQL, stripslashes() will be run on each value in the array.

On Oracle, the keys of the array will always be lowercase. The optional debug parameter can be used to print all the content of the result row. This parameter has no effect on other database layers.

See Also: mssql_fetch_array, mysql_fetch_array, OCIFetchInto, pg_fetch_array

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

This function is essentially the same as sql_fetchrow, except it returns all the rows from the result set of the SQL query. The rows are returned as arrays, which are stored within an array. This effect can also be accomplished with sql_fetchrow using the code below.

$result_array = array();
while ( $row = $db->sql_fetchrow($result) )
{
	$result_array[] = $row;
}

The only other notable difference between this function and sql_fetchrow is that on PostgreSQL, the result arrays use only associative indices.

See Also: mssql_fetch_array, mysql_fetch_array, OCIFetchInto, OCIExecute, OCIFetchStatement, pg_fetch_array

// Get username from the 21st row
$field = $db->sql_fetchfield('username', 21, $result);
// Get username in the current row
$field = $db->sql_fetchfield('username');

Returns the content of one field (or table column) from one row in a SQL result set. The parameter field should be the name of the field to be returned. number should be the number of the row from which to return the field. If number is -1 (the default value), the current row will be used. See the special note for information on the query_id parameter.

Using sql_fetchrow to retrieve an entire row instead of a single field is recommended. sql_fetchrow will often be faster, and in some cases sql_fetchfield actually calls sql_fetchrow or does the same work anyway. Also, on Oracle databases, the field will be returned entirely in uppercase.

See Also: mssql_result, mssql_fetch_array, mysql_result, OCIExecute, OCIFetch, OCIResult, pg_fetch_array

$db->sql_rowseek($offset, $result);

Used to move the internal row pointer of the result associated with query_id to the indicated row number. The next call to sql_fetchrow would return the row to which the internal pointer is moved.

The function will return true unless the row pointer can not be moved to the indicated row, in which case false is returned. See the special note for information on the query_id parameter for another case in which false could be returned.

See Also: mssql_data_seek, mysql_data_seek, OCIExecute, OCIFetch

$id_number = $db->sql_nextid();

Returns the id number automatically generated when the previous INSERT query was executed. In MySQL, this is the value of an AUTO_INCREMENT column. sql_nextid should be used after the INSERT query and before any other queries are executed.

See the special note for information on the query_id parameter. Using this parameter is not recommended for this function. It will not be used by the MS Access, MS SQL, MS SQL (ODBC), or MySQL database layers.

Oracle users may encounter errors due to this function being defined twice. If this occurs, the errors may be solved by deleting one copy of the function.

See Also: mysql_insert_id, OCIParse, OCIExecute, OCIFetchInto, pg_exec, pg_fetch_array

$db->sql_freeresult();
$db->sql_freeresult($result);

This function is used to free all memory associated with an SQL query. See the special note for information on the query_id parameter.

sql_freeresult should be used if you are worried about the memory being used for a query. All memory is freed when a script finishes executing, but sql_freeresult is helpful when dealing with queries that return large result sets.

See Also: odbc_free_result, mssql_free_result, mysql_free_result, OCIFreeStatement, pg_freeresult

$error = $db->sql_error();
$error = $db->sql_error($result);

Returns an associative array containing information about the last SQL error encountered. This array typically has the following elements:

• code - An error code generated by the database software. Disabled on MS Access and MS SQL. Always -1 on PostgreSQL.
• message - The error message returned by the database software. Always "Error" on MS Access.

The function always returns an empty string on the ODBC database layer. See the special note for information on the query_id parameter.

See Also: odbc_error, odbc_errormsg, mssql_get_last_message, mysql_error, mysql_errno, OCIError, pg_errormessage