|
|
| Author |
Message |
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
 Posted: September 24th 2003, 9:52 pm Post subject: |
 |
|
About the DBAL functions
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. 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.
| Quote:
|
Function Name (whatever_it_is_called)
Parameters (The information that can be passed to the function, listed as "type|name." Optional parameters are in [ brackets. ] )
| Code:
|
|
Usage (How to call the function.)
|
Description (General uses of the function.)
See Also (PHP's internal database functions that are similar. Coders may be familiar with these.)
|
Last edited by Thoul on September 24th 2003, 9:58 pm; edited 1 time in total |
|
| Back to top |
|
 |
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 24th 2003, 9:55 pm Post subject: |
|
|
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. |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:18 am Post subject: |
|
|
sql_db
sql_db( string|sqlserver , string|sqluser , string|sqlpassword , string|database [ , boolean|persistency ] )
| Code:
|
|
$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). 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.
| Code:
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:20 am Post subject: |
|
|
sql_close
sql_close() No parameters.
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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:24 am Post subject: |
|
|
sql_query
sql_query( [ string|query [ , integer|transaction ] ] )
| Code:
|
$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.
| Code:
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:30 am Post subject: |
|
|
sql_numrows
sql_numrows( [ resource|query_id ] )
| Code:
|
$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
sql_affectedrows
sql_affectedrows( [ resource|query_id ])
| Code:
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:34 am Post subject: |
|
|
sql_numfields
sql_numfields( [ resource|query_id ] )
| Code:
|
$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
sql_fieldname
sql_fieldname( int|offset [ , resource|query_id ] )
| Code:
|
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:35 am Post subject: |
|
|
sql_fieldtype
sql_fieldtype( int|offset [ , resource|query_id ] )
| Code:
|
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:36 am Post subject: |
|
|
sql_fetchrow
sql_fetchrow( [ resource|query_id [, boolean|debug ] ] )
| Code:
|
|
$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 |
|
| Back to top |
|
|
Thoul
VIP
Joined: 30 Jul 2002
Posts: 17676
Location: USA
|
| Posted: September 26th 2003, 3:37 am Post subject: |
|
|
sql_fetchrowset
sql_fetchrowset( [ resource|query_id ] )
| Code:
|
|
$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.
| Code:
|
$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 |
|
| Back to top |
|
|
|
Forums
Home
Register
Profile
Log in to check your private messages
Log in
FAQ
Search
Contact
Us 
