Simple Machines

Welcome to Simple Machines Community Forum.
Log in
Sign up

Main Menu

Advertisement:

Navigation

Standard Database Functions:

Database Package Functions only:
Database Package functions were also introduced in SMF 2.0. These special functions allow customization creators to easily modify a database that will support multiple database types. The below functions only exist when using db_extend('packages');. By Default this is automatically called in the Package Manager.

$smcFunc['db_query'] (identifier, query, values, connection)

Works Similar to how db_query worked in 1.x versions.
Identifier is used for identifying specific queries that will be handled specially.
Values is an array of values you are intending to use in the query.

Example:

Code Select

	$request = $smcFunc['db_query']('', '
		SELECT data, filetype
		FROM {db_prefix}admin_info_files
		WHERE filename = {string:current_filename}
		LIMIT 1',
		array(
			'current_filename' => $_REQUEST['filename'],
		)
	);

$smcFunc['db_quote'] (query, values, connection)

Example:

Code Select

	$request = $smcFunc['db_quote']('
		SELECT data, filetype
		FROM {db_prefix}admin_info_files
		WHERE filename = {string:current_filename}
		LIMIT 1',
		array(
			'current_filename' => $_REQUEST['filename'],
		)
	);

Works Similar to how db_query works with the exception of no identifier.
Values is an array of values you are intending to use in the query.
Does not execute the query, Formats as if it where going to be and returns the string.

$smcFunc['db_fetch_assoc'] ($result)

Will return exact same results as mysql_fetch_assoc

Example:

Code Select

		while ($row = $smcFunc['db_fetch_assoc']($request))
		{
			censorText($row['label']);
			$pollOptions[$row['id_choice']] = $row;
			$realtotal += $row['votes'];
			$pollinfo['has_voted'] |= $row['voted_this'] != -1;
		}

$smcFunc['db_fetch_row'] ($result)

Will return exact same results as mysql_fetch_row.
Emulated while using SQlite with smf_sqlite_fetch_row.

Example:

Code Select

	list ($file_data, $filetype) = $smcFunc['db_fetch_row']($request);

$smcFunc['db_free_result'] ($result)

Will return exact same results as mysql_free_result.
Emulated while using SQlite with smf_sqlite_free_result.

Example:

Code Select

	$smcFunc['db_free_result']($request);

$smcFunc['db_insert'] (method, table, columns, data, keys, disable_trans, connection)

Emulated with smf_db_insert.
Method tells how to change the data. Accepts "replace" "ignore" or "insert".
Table: the data will be changed on.
Columns: An array ( column_name => input_type) set that holds all column names that will be changed and their expected input type.
Data holds an array that must be as long as the column array with all the data that will be used.
Keys is supposed to hold the tables key information, only appears to affect sqlite and postrgresql (when using "replace") versions.

Example:

Code Select

			$smcFunc['db_insert']('replace',
				'{db_prefix}themes',
				array('id_member' => 'int', 'id_theme' => 'int', 'variable' => 'string', 'value' => 'string'),
				array($user_info['id'], 1, 'agreement_accepted', time()),
				array('id_member', 'id_theme', 'variable')
			);

$smcFunc['db_insert_id'] (table, field, connect)

Will return exact same results as mysql_insert_id.
Emulated while using PostgreSQL with smf_db_insert_id.
Table holds the table name that was affected.
Field holds the name of the field that was affected.

Example:

Code Select

			$old_id = $smcFunc['db_insert_id']($table);

$smcFunc['db_num_rows'] ($result)

Will return exact same results as mysql_num_rows.

[/li]
[/list]
Example:

Code Select

	if ($smcFunc['db_num_rows']($request) == 0)
		fatal_lang_error('admin_file_not_found', true, array($_REQUEST['filename']), 404);

$smcFunc['db_data_seek'] ($result, row_number)

Will return exact same results as mysql_data_seek.
Emulated while using PostgreSQL with db_data_seek.
Row_number is the row number you wish the pointer to be at.

Example:

Code Select

			for ($i = 0, $n = $smcFunc['db_num_rows']($request); $i < $n; $i += $cache_step_size)
			{
				$smcFunc['db_data_seek']($request, $i);
				list($memberlist_cache['index'][$i]) = $smcFunc['db_fetch_row']($request);
			}

$smcFunc['db_num_fields'] ($result)

Will return exact same results as mysql_num_fields.

Example:

Code Select

		// Get the fields in this row...
		$field_list = array();
		for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
		{
			// Try to figure out the type of each field. (NULL, number, or 'string'.)
			if (!isset($row[$j]))
				$field_list[] = 'NULL';
			elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
				$field_list[] = $row[$j];
			else
				$field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
		}

$smcFunc['db_escape_string'] (uncleaned_string)

MySQL databases use addslashes function instead of mysql_escape_string.
Does not require a database connection to use this.

Example:

Code Select

	// Add slashes to every element, even the indexes!
	foreach ($var as $k => $v)
		$new_var[$smcFunc['db_escape_string']($k)] = escapestring__recursive($v);

$smcFunc[' '] (cleaned_string)

MySQL databases use stripslashes function.
PostgreSQL databases will emulate this with smf_postg_unescape_string.
SQlite databases will emulate this with smf_sqlite_unescape_string.
Does not require a database connection to use this.

Example:

Code Select

	// Strip the slashes from every element.
	foreach ($var as $k => $v)
		$new_var[$smcFunc['db_unescape_string']($k)] = unescapestring__recursive($v);

$smcFunc['db_server_info'] (connection)

Attempts to get database server information.

Example:

Code Select

if ($smcFunc['db_server_info'] < 8.0)

$smcFunc['db_affected_rows'] (connection)

Will return exact same results as mysql_affected_rows.

Example:

Code Select

			if ($smcFunc['db_affected_rows']() > 0)
			{
				require_once($sourcedir . '/Profile-Modify.php');
				$user_info['alerts'] = alert_count($user_info['id'], true);
				updateMemberData($user_info['id'], array('alerts' => $user_info['alerts']));
			}

$smcFunc['db_transaction'] (type, connection)

Same as calling mysql queries for "BEGIN", "ROLLBACK", and "COMMIT".
Accepts "begin", "rollback", and "commit".

Code Select

		$smcFunc['db_transaction']('begin');
		$smcFunc['db_query']('', '
			ALTER TABLE ' . $short_table_name . '
			ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type,
			array(
				'security_override' => true,
			)
		);
...
		$smcFunc['db_transaction']('commit');

$smcFunc['db_error'] (connection)

Will return the exact same results as mysql_error.
SQlite databases will emulate this with smf_sqlite_last_error, and the connection is ignored.

Example:

Code Select

		// Language files aren't loaded yet :(.
		$db_error = @$smcFunc['db_error']($db_connection);
		@mail($webmaster_email, $mbname . ': SMF Database Error!', 'There has been a problem with the database!' . ($db_error == '' ? '' : "\n" . $smcFunc['db_title'] . ' reported:' . "\n" . $db_error) . "\n\n" . 'This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.');

$smcFunc['db_select_db'] (database_name, connection)

Will return exact same results as mysql_select_db.
PostgreSQL functions will have this return true always in postg_select_db. PostgreSQL has database selected upon creating the connection.
SQlite will do nothing as there is only one database per file.

Example:

Code Select

			// Okay, now let's try to connect...
			if (!$smcFunc['db_select_db']($db_name, $db_connection))
			{
				$incontext['error'] = sprintf($txt['error_db_database'], $db_name);
				return false;
			}

$smcFunc['db_title'] ()

Name of the database being used. Such as MySQL, PostgreSQL and SQlite.
Should not be called as a function, but used a string.

Example:

Code Select

		// Check whether the new code has duplicates. It should be unique.
		$request = $smcFunc['db_query']('', '
			SELECT id_smiley
			FROM {db_prefix}smileys
			WHERE code = {raw:mysql_binary_statement} {string:smiley_code}',
			array(
				'mysql_binary_statement' => $smcFunc['db_title'] == MYSQL_TITLE ? 'BINARY' : '',
				'smiley_code' => $_POST['smiley_code'],
			)
		);

$smcFunc['db_sybase'] ()

Tells SMF whether the Database uses sybase or not.
PostgreSQL and SQlite use sybase, MySQL does not.

No Examples

$smcFunc['db_case_sensitive'] ()

Tells SMF whether the Database is case sensitive or not.
PostgreSQL is case sensitive, MySQL and SQlite are not.

Example:

Code Select

	// Does name case insensitive match a member group name?
	$request = $smcFunc['db_query']('', '
		SELECT id_group
		FROM {db_prefix}membergroups
		WHERE {raw:group_name} LIKE {string:check_name}
		LIMIT 1',
		array(
			'group_name' => $smcFunc['db_case_sensitive'] ? 'LOWER(group_name)' : 'group_name',
			'check_name' => $checkName,
		)
	);

Database Package Functions only.

The below functions only exist when using db_extend('packages');

$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error)

- Use with db_extend('packages');

This function allows for adding a column to a table.
Table name should be an already existing table. If you specific a database prefix, add 'no_prefix' to the paramaters.
Column Info should be an array of data containing with keys
- 'name' of the column
- 'type' of the column
- 'size' of the column if required by type.
- 'null', whether to use "null" or "not null"
- 'default' should contain the default value for the column
- 'auto' tells wether the column uses auto_increment or not.
- As of 2.0 RC2, 'unsigned' (true/false) specifies whether the column is unsigned or not.
Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
if_exists controls what to do if the column exists, by default it updates the column

$smcFunc['db_add_index'] (table_name, index_into, parameters, if_exists, error)

- Use with db_extend('packages');

This function allows for adding an index to a table.
Table name should be an already existing table. If you specific a database prefix, add 'no_prefix' to the paramaters.
index Info should be an array of data containing with keys
- 'name' of the index
- 'type' of the column ('primary', 'unique')
- 'is_primary' allows the column to be primary or not.
Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
if_exists controls what to do if the column exists, by default it updates the column

$smcFunc['db_calculate_type'] (type_name, type_size, reverse)

- Use with db_extend('packages');

This function will calculate the type and size for a column.
type_name should be the type of the column.
type_size contains the size of the column (can be empty

$smcFunc['db_change_column'] (table_name, old_column, column_info, parameters, error)

- Use with db_extend('packages');

This function allows for changing an existing column structure.
'table name' should be an already existing table. If you specific a database prefix, add 'no_prefix' to the paramaters.
'old_column' should be an already existing column name.
'column_info' should be an array of data containing with keys
- 'name' of the column
- 'type' of the column
- 'size' of the column if required by type.
- 'null', whether to use "null" or "not null"
- 'default' should contain the default value for the column
- 'auto' tells whether the column uses auto_increment or not.
- As of 2.0 RC2, 'unsigned' (true/false) specifies whether the column is unsigned or not.
Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.

$smcFunc['db_create_table'] (table_name, columns, indexes, parameters, if_exists, error)

- Use with db_extend('packages');

This function allows for creating a table. You can not create a SMF default table.
Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.
columns is a multi-dimensional array containing the columns to be in the table. This is passed to $smcFunc['db_add_column'] function for handling.
if_exists is by default using "update". Other options are 'overwrite', 'ignore', 'error', 'update_remove',
Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
if_exists controls what to do if the column exists, by default it updates the column

$smcFunc['db_drop_table'] (table_name, parameters, error)

- Use with db_extend('packages');

This function allows for removal a table. You can not delete a SMF default table.
Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the parameters.
Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.

$smcFunc['db_table_structure'] (table_name)

- Use with db_extend('packages');

This function returns the structure of a table.
If you need to specific the database prefix use {db_prefix}.
Returns with an array with table name, columns, and indexes.

$smcFunc['db_list_columns'] (table_name, detail)

- Use with db_extend('packages');

This function returns the current columns in a table in a multi-dimensional array
If you need to specific the database prefix use {db_prefix}.
If 'detail' is specified a formated array will be returned of the column info, otherwise the plain straight column info is returned. The detailed array is 'name', 'null', 'default', 'type', 'size', 'auto'.

$smcFunc['db_list_indexes'] (table_name, detail)

- Use with db_extend('packages');

This function returns the current indexes in a table in a multi-dimensional array
If you need to specific the database prefix use {db_prefix}.
If 'detail' is specified a formated array will be returned of the index info, otherwise the plain straight index info is returned. The detailed array is 'name', 'type', 'columns'

$smcFunc['db_remove_column'] (table_name, column_name, parameters, error)

- Use with db_extend('packages');

This function removes a column
Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.

$smcFunc['db_remove_index'] (table_name, index_name, parameters, error)

- Use with db_extend('packages');

This function removes a index
Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.

Advertisement: