$smcFunc: Difference between revisions From Online Manual

Jump to: navigation, search
(Started this page. Got pretty much all the formatting kinks out.)
 
(code instead of pre)
 
(40 intermediate revisions by 10 users not shown)
Line 1: Line 1:
{{TOCright}}
{{needs_work|Proofread}}
These functions are used throught SMF code in order to deal with different versions of php, different character sets, and different databases.
==General Utility Functions==
These functions are defined in function reloadSettings, in Load.php. Some of these functions have the same name as standard PHP functions, but were designed to deal uniformly with UTF-8 character sets, HTML entities, and differences in behavior in different versions of PHP. It is recommended that they be used instead of the functions they replace.
*'[[entity_fix]]'
*'[[htmlspecialchars]]'
*'[[htmltrim]]'
*'[[strlen]]'
*'[[strpos]]'
*'[[substr]]'
*'[[strtolower]]'
*'[[strtoupper]]'
*'[[ucfirst]]'
*'[[ucwords]]'
==Database Functions==
{{Version specific|version=2.0}}
{{Version specific|version=2.0}}
{{TOCright}}
In SMF 2.0, multiple database support was introduced. This was implemented by developers as a new layer of database functions along with a new security model, which provides a fast and secure method to work across database systems.
In SMF 2.0, multiple database support was introduced. This was implemented by developers as a new layer of database functions along with a new security model, which provides a fast and secure method to work across database systems.
Below is a list of the database functions that currently exist in 2.0. Each of these links will direct you towards a section about that function that will help you understand what each one does, how its input is expected and if possible, the exact duplicate function for mysql. An example is provided as well for most of these, these examples come straight from the SMF Source code.
Below is a list of the database functions that currently exist in 2.0. Each of these links will direct you towards a section about that function that will help you understand what each one does, how its input is expected and if possible, the exact duplicate function for mysql. An example is provided as well for most of these, these examples come straight from the SMF Source code.


Please note our [[Function Database]] now has the latest SMF 2.0 functions for your information and may help in explaining the functions. They do not use the $smcFunc variables that this guide does. For most of your functions you will see "smf_db_xxx" where xxx is the function name such as "smf_db_insert" that is used by $smcFunc['db_insert'].
Please note our [[Function database|Function Database]] now has the latest SMF 2.0 functions for your information and may help in explaining the functions. They do not use the $smcFunc variables that this guide does. For most of your functions you will see "smf_db_xxx" where xxx is the function name such as "smf_db_insert" that is used by $smcFunc['db_insert'].


==Database Functions==
===db_query===
===db_query===
'''Usage''' $smcFunc['db_query'] (identifier, query, values, connection)===
'''Usage''' $smcFunc['db_query'] (identifier, query, values, connection)
* Works Similar to how db_query worked in 1.x versions.
* Works Similar to how db_query worked in 1.x versions.
* Identifier is used for identifying specific queries that will be handled specially.
* '''identifier''' is used to identify specific query types in which it is necessary to replace portions of the query for compatability across all Database Management Systems supported by SMF. The query must be written for MYSQL. The db_query function will alter the query.
** 'substring' --- for the SQLite database, '''SUBSTRING''' will be replaced by '''SUBSTR'''
** See the function smf_db_query in Subs-DB-{dbms}.php for the full list of replacements.
* Values is an array of values you are intending to use in the query.
* Values is an array of values you are intending to use in the query.
'''Example'''
'''Example'''
<pre>            $result = $smcFunc['db_query'](''', '
{{code|1=<nowiki>            $result = $smcFunc['db_query']('', '
                 SELECT poster_time
                 SELECT poster_time
                 FROM {db_prefix}messages
                 FROM {db_prefix}messages
Line 22: Line 38:
                     'id_msg' =&gt; $user_settings['id_msg_last_visit'],
                     'id_msg' =&gt; $user_settings['id_msg_last_visit'],
                 )
                 )
             );}</pre>
             );</nowiki>}}
 
'''Example with identifier 'substring'''
{{code|1=<nowiki>$result = $smcFunc['db_query']('substring', '
SELECT m.subject,
'. ($mylimit > 0 ? SUBSTRING(m.body, 1, '.$mylimit.') as body  : 'm.body'). ',
IFNULL(mem.real_name, m.poster_name) AS realName, m.poster_time as date, mem.avatar, mem.posts, mem.date_registered as dateRegistered,mem.last_login as lastLogin,
IFNULL(a.id_attach, 0) AS ID_ATTACH, a.filename, a.attachment_type as attachmentType, t.id_board as category, b.name as category_name,
array(
'id_msg' => $user_settings['id_msg_last_visit'],
)
);</nowiki>}}
 
===db_quote===
===db_quote===
'''Usage''' $smcFunc['db_quote'] (query, values, connection)===
'''Usage''' $smcFunc['db_quote'] (query, values, connection)
* Works Similar to how db_query works with the exception of no identifier.
* 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.
* Values is an array of values you are intending to use in the query.
* Does <strong>not</strong> execute the query, Formats as if it where going to be and returns the string.
* Does '''not''' execute the query, Formats as if it where going to be and returns the string.
'''Example'''
'''Example'''
<pre>           $realNameMatches[] = $smcFunc['db_quote'](
{{code|1=<nowiki>             $realNameMatches[] = $smcFunc['db_quote'](
                 '{string:possible_user}',  
                 '{string:possible_user}',  
                 array(
                 array(
                     'possible_user' =&gt; $possible_user
                     'possible_user' =&gt; $possible_user
                 )
                 )
             );}</pre>
             );</nowiki>}}
===db_fetch_assoc===
===db_fetch_assoc===
'''Usage''' $smcFunc['db_fetch_assoc'] ($result)===
'''Usage''' $smcFunc['db_fetch_assoc'] ($result)
* Will return exact same results as <a href="http://php.net/mysql_fetch_assoc" class="bbc_link" target="_blank">mysql_fetch_assoc</a>
* Will return exact same results as [http://php.net/mysql_fetch_assoc mysql_fetch_assoc]
'''Example'''
'''Example'''
<pre>       while ($row = $smcFunc['db_fetch_assoc']($request))
{{code|1=<nowiki>         while ($row = $smcFunc['db_fetch_assoc']($request))
             $search_params['brd'][] = $row['id_board'];}</pre>
             $search_params['brd'][] = $row['id_board'];</nowiki>}}
===db_fetch_row===
===db_fetch_row===
'''Usage''' $smcFunc['db_fetch_row'] ($result)===
'''Usage''' $smcFunc['db_fetch_row'] ($result)
* Will return exact same results as <a href="http://php.net/mysql_fetch_row" class="bbc_link" target="_blank">mysql_fetch_row</a>.
* Will return exact same results as [http://php.net/mysql_fetch_row mysql_fetch_row].
* Emulated while using SQlite with smf_sqlite_fetch_row.
* Emulated while using SQlite with smf_sqlite_fetch_row.
'''Example'''
'''Example'''
<pre>        while ($row = $smcFunc['db_fetch_row']($request))
{{code|1=<nowiki>        while ($row = $smcFunc['db_fetch_row']($request))
             $toDelete[] = $row[0];}</pre>
             $toDelete[] = $row[0];</nowiki>}}
===db_free_result===
===db_free_result===
'''Usage''' $smcFunc['db_free_result'] ($result)===
'''Usage''' $smcFunc['db_free_result'] ($result)
* Will return exact same results as <a href="http://php.net/mysql_free_result" class="bbc_link" target="_blank">mysql_free_result</a>.
* Will return exact same results as [http://php.net/mysql_free_result mysql_free_result].
* Emulated while using SQlite with smf_sqlite_free_result.
* Emulated while using SQlite with smf_sqlite_free_result.
'''Example'''
'''Example'''
<pre>       $smcFunc['db_free_result']($request);}</pre>
{{code|1=<nowiki>         $smcFunc['db_free_result']($request);</nowiki>}}
===db_insert===
===db_insert===
'''Usage''' $smcFunc['db_insert'] (method, table, columns, data, keys, disable_trans, connection)===
'''Usage''' $smcFunc['db_insert'] (method, table, columns, data, keys, disable_trans, connection)
* Emulated with smf_db_insert.
* Emulated with smf_db_insert.
* Method tells how to change the data. Accepts "replace" "ignore" or "insert".
* Method tells how to change the data. Accepts "replace" "ignore" or "insert".
Line 63: Line 91:
* Keys is supposed to hold the tables key information, only appears to affect sqlite and postrgresql (when using "replace") versions.
* Keys is supposed to hold the tables key information, only appears to affect sqlite and postrgresql (when using "replace") versions.
'''Example'''
'''Example'''
<pre>       $smcFunc['db_insert']('replace',
{{code|1=<nowiki>         $smcFunc['db_insert']('replace',
             '{db_prefix}log_topics',
             '{db_prefix}log_topics',
             array(
             array(
Line 72: Line 100:
             ),
             ),
             array('id_member', 'id_topic')
             array('id_member', 'id_topic')
         );}</pre>
         );</nowiki>}}
===db_insert_id===
===db_insert_id===
'''Usage''' $smcFunc['db_insert_id'] (table, field, connect)===
'''Usage''' $smcFunc['db_insert_id'] (table, field, connect)
* Will return exact same results as <a href="http://php.net/mysql_insert_id" class="bbc_link" target="_blank">mysql_insert_id</a>.
* Will return exact same results as [http://php.net/mysql_insert_id mysql_insert_id].
* Emulated while using PostgreSQL with smf_db_insert_id.
* Emulated while using PostgreSQL with smf_db_insert_id.
* Table holds the table name that was affected.
* Table holds the table name that was affected.
* Field holds the name of the field that was affected.
* Field holds the name of the field that was affected.
'''Example'''
'''Example'''
<pre>       $bcinfo['id_poll'] = $smcFunc['db_insert_id']('{db_prefixpolls', 'id_poll');}</pre>
{{code|1=<nowiki>         $bcinfo['id_poll'] = $smcFunc['db_insert_id']('{db_prefix}polls', 'id_poll');</nowiki>}}
 
===db_num_rows===
===db_num_rows===
'''Usage''' $smcFunc['db_num_rows'] ($result)===
'''Usage''' $smcFunc['db_num_rows'] ($result)
* Will return exact same results as <a href="http://php.net/mysql_num_rows" class="bbc_link" target="_blank">mysql_num_rows</a>.
* Will return exact same results as [http://php.net/mysql_num_rows mysql_num_rows].
'''Example'''
'''Example'''
<pre>    if ($smcFunc['db_num_rows']($request) == 0)
{{code|1=<nowiki>    if ($smcFunc['db_num_rows']($request) == 0)
         fatal_lang_error('admin_file_not_found', true, array($_REQUEST['filename']));}</pre>
         fatal_lang_error('admin_file_not_found', true, array($_REQUEST['filename']));</nowiki>}}
===db_data_seek===
===db_data_seek===
'''Usage''' $smcFunc['db_data_seek'] ($result, row_number)===
'''Usage''' $smcFunc['db_data_seek'] ($result, row_number)
* Will return exact same results as <a href="http://php.net/mysql_data_seek" class="bbc_link" target="_blank">mysql_data_seek</a>.
* Will return exact same results as [http://php.net/mysql_data_seek mysql_data_seek].
* Emulated while using PostgreSQL with db_data_seek.
* Emulated while using PostgreSQL with db_data_seek.
* Row_number is the row number you wish the pointer to be at.
* Row_number is the row number you wish the pointer to be at.
'''Example'''
'''Example'''
<pre>   // Start from the beginning...
{{code|1=<nowiki>     // Start from the beginning...
     if ($reset)
     if ($reset)
         return @$smcFunc['db_data_seek']($messages_request, 0);}</pre>
         return @$smcFunc['db_data_seek']($messages_request, 0);</nowiki>}}
===db_num_fields===
===db_num_fields===
'''Usage''' $smcFunc['db_num_fields'] ($result)===
'''Usage''' $smcFunc['db_num_fields'] ($result)
* Will return exact same results as <a href="http://php.net/mysql_num_fields" class="bbc_link" target="_blank">mysql_num_fields</a>.
* Will return exact same results as [http://php.net/mysql_num_fields mysql_num_fields].
'''Example'''
'''Example'''
<pre>       // Get the fields in this row...
{{code|1=<nowiki>         // Get the fields in this row...
         $field_list = array();
         $field_list = array();
         for ($j = 0; $j &lt; $smcFunc['db_num_fields']($result); $j++)
         for ($j = 0; $j &lt; $smcFunc['db_num_fields']($result); $j++)
Line 111: Line 140:
             else
             else
                 $field_list[] = ''' . $smcFunc['db_escape_string']($row[$j]) . ''';
                 $field_list[] = ''' . $smcFunc['db_escape_string']($row[$j]) . ''';
         }}</pre>
         }</nowiki>}}
===db_escape_string===
===db_escape_string===
'''Usage''' $smcFunc['db_escape_string'] (uncleaned_string)===
'''Usage''' $smcFunc['db_escape_string'] (uncleaned_string)
* MySQL databases use addslashes function instead of <a href="http://php.net/mysql_escape_string" class="bbc_link" target="_blank">mysql_escape_string</a>.
* MySQL databases use addslashes function instead of [http://php.net/mysql_escape_string mysql_escape_string].
* Does not require a database connection to use this.
* Does not require a database connection to use this.
'''Example'''
'''Example'''
<pre>   // Add slashes to every element, even the indexes!
{{code|1=<nowiki>     // Add slashes to every element, even the indexes!
     foreach ($var as $k =&gt; $v)
     foreach ($var as $k =&gt; $v)
         $new_var[$smcFunc['db_escape_string']($k)] = escapestring__recursive($v);}</pre>
         $new_var[$smcFunc['db_escape_string']($k)] = escapestring__recursive($v);</nowiki>}}
===db_unescape_string===
===db_unescape_string===
'''Usage''' $smcFunc['db_unescape_string'] (cleaned_string)===
'''Usage''' $smcFunc['db_unescape_string'] (cleaned_string)
* MySQL databases use stripslashes function.
* MySQL databases use stripslashes function.
* PostgreSQL databases will emulate this with smf_postg_unescape_string.
* PostgreSQL databases will emulate this with smf_postg_unescape_string.
Line 127: Line 156:
* Does not require a database connection to use this.
* Does not require a database connection to use this.
'''Example'''
'''Example'''
<pre>   // Strip the slashes from every element.
{{code|1=<nowiki>     // Strip the slashes from every element.
     foreach ($var as $k =&gt; $v)
     foreach ($var as $k =&gt; $v)
         $new_var[$smcFunc['db_unescape_string']($k)] = unescapestring__recursive($v);}</pre>
         $new_var[$smcFunc['db_unescape_string']($k)] = unescapestring__recursive($v);</nowiki>}}
===db_server_info===
===db_server_info===
'''Usage''' $smcFunc['db_server_info'] (connection)===
'''Usage''' $smcFunc['db_server_info'] (connection)
* Attempts to get database server information.
* Attempts to get database server information.
'''Example'''
'''Example'''
<pre>       // Some MySQL versions are superior to others.
{{code|1=<nowiki>         // Some MySQL versions are superior to others.
         $this-&gt;canDoBooleanSearch = version_compare($smcFunc['db_server_info']($db_connection), '4.0.1', '&gt;=') == 1;}</pre>
         $this-&gt;canDoBooleanSearch = version_compare($smcFunc['db_server_info']($db_connection), '4.0.1', '&gt;=') == 1;</nowiki>}}
===db_tablename===
'''Usage''' $smcFunc['db_tablename'] ($result, table_number)===
* Will return exact same results as <a href="http://php.net/mysql_tablename" class="bbc_link" target="_blank">mysql_tablename</a>.
* Finds a table by a number.
* Does not appear to be used in SMF coding yet.
No example provided
 
 
 
===db_affected_rows===
===db_affected_rows===
'''Usage''' $smcFunc['db_affected_rows'] (connection)===
'''Usage''' $smcFunc['db_affected_rows'] (connection)
* Will return exact same results as <a href="http://php.net/mysql_affected_rows" class="bbc_link" target="_blank">mysql_affected_rows</a>.
* Will return exact same results as [http://php.net/mysql_affected_rows mysql_affected_rows].
'''Example'''
'''Example'''
<pre>        if ($smcFunc['db_affected_rows']() &lt;= 0)
{{code|1=<nowiki>        if ($smcFunc['db_affected_rows']() &lt;= 0)
         {
         {
             loadLanguage('Admin');
             loadLanguage('Admin');
             fatal_lang_error('salvaged_category_error', false);
             fatal_lang_error('salvaged_category_error', false);
         }}</pre>
         }</nowiki>}}
===db_transaction===
===db_transaction===
'''Usage''' $smcFunc['db_transaction'] (type, connection)===
'''Usage''' $smcFunc['db_transaction'] (type, connection)
* Same as calling mysql queries for "BEGIN", "ROLLBACK", and "COMMIT".
* Same as calling mysql queries for "BEGIN", "ROLLBACK", and "COMMIT".
* Accepts "begin", "rollback", and "commit".
* Accepts "begin", "rollback", and "commit".
'''Example'''
'''Example'''
<pre>   $smcFunc['db_transaction']('begin');
{{code|1=<nowiki>     $smcFunc['db_transaction']('begin');
     // Do the table and indexes...
     // Do the table and indexes...
     $smcFunc['db_query'](''', $table_query,
     $smcFunc['db_query'](''', $table_query,
Line 169: Line 189:
     );
     );


     $smcFunc['db_transaction']('commit');}</pre>
     $smcFunc['db_transaction']('commit');</nowiki>}}
===db_error===
===db_error===
'''Usage''' $smcFunc['db_error'] (connection)===
'''Usage''' $smcFunc['db_error'] (connection)
* Will return the exact same results as <a href="http://php.net/mysql_error" class="bbc_link" target="_blank">mysql_error</a>.
* Will return the exact same results as [http://php.net/mysql_error mysql_error].
* SQlite databases will emulate this with smf_sqlite_last_error, and the connection is ignored.
* SQlite databases will emulate this with smf_sqlite_last_error, and the connection is ignored.
'''Example'''
'''Example'''
<div class="codeheader">Code: <a href="javascript:void(0);" onclick="return smfSelectText(this);" class="codeoperation">[Select]</a></div><pre style="margin: 0pt; padding: 0pt;"><code style="overflow: scroll;" class="bbc_code">           // Language files aren't loaded yet.
{{code|1=<nowiki>             // Language files aren't loaded yet.
             $db_error = @$smcFunc['db_error']($db_connection);
             $db_error = @$smcFunc['db_error']($db_connection);
             @mail($webmaster_email, $mbname . ': SMF Database Error!', 'There has been a problem with the database!' . ($db_error == ''' ? ''' : "" . $smcFunc['db_title'] . ' reported:' . "" . $db_error) . "" . 'This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.');
             @mail($webmaster_email, $mbname . ': SMF Database Error!', 'There has been a problem with the database!' . ($db_error == ''' ? ''' : "" . $smcFunc['db_title'] . ' reported:' . "" . $db_error) . "" . 'This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.');
</code></pre>
</nowiki>}}
 
===db_select_db===
===db_select_db===
'''Usage''' $smcFunc['db_select_db'] (database_name, connection)===
'''Usage''' $smcFunc['db_select_db'] (database_name, connection)
* Will return exact same results as <a href="http://php.net/mysql_select_db" class="bbc_link" target="_blank">mysql_select_db</a>.
* Will return exact same results as [http://php.net/mysql_select_db mysql_select_db].
* PostgreSQL functions will have this return true always in postg_select_db. PostgreSQL has database selected upon creating the connection.
* 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.
* SQlite will do nothing as there is only one database per file.
No Examples
No Examples


===db_title===
===db_title===
'''Usage''' $smcFunc['db_title'] ()===
'''Usage''' $smcFunc['db_title'] ()
* Name of the database being used. Such as MySQL, PostgreSQL and SQlite.
* Name of the database being used. Such as MySQL, PostgreSQL and SQlite.
* Should not be called as a function, but used a string.
* Should not be called as a function, but used a string.
Line 195: Line 215:


===db_sybase===
===db_sybase===
'''Usage''' $smcFunc['db_sybase'] ()===
'''Usage''' $smcFunc['db_sybase'] ()
* Tells SMF whether the Database uses sybase or not.
* Tells SMF whether the Database uses sybase or not.
* PostgreSQL and SQlite use sybase, MySQL does not.
* PostgreSQL and SQlite use sybase, MySQL does not.
Line 202: Line 222:


===db_case_sensitive===
===db_case_sensitive===
'''Usage''' $smcFunc['db_case_sensitive'] ()===
'''Usage''' $smcFunc['db_case_sensitive'] ()
* Tells SMF whether the Database is case sensitive or not.
* Tells SMF whether the Database is case sensitive or not.
* PostgreSQL is case sensitive, MySQL and SQlite are not.
* PostgreSQL is case sensitive, MySQL and SQlite are not.
No Examples
No Examples


<hr><strong>==Database Package Functions only.==
==Database Package Functions only.==
</strong>
The below functions only exist when using db_extend('packages');
The below functions only exist when using db_extend('packages');


===db_add_column===
'''Usage''' $smcFunc['db_add_column'] (string ''$table_name'', array ''$column_info'', array ''$parameters'', string ''$if_exists'', string ''$error'')
This function allows for adding a column to a table.


===db_add_column===
'''Parameters'''
'''Usage''' $smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error)===
* table_name
* with db_extend('packages');
** an already existing table.
* This function allows for adding a column to a table.
*column_info
* Table name should be an already existing table. If you specific a database prefix, add 'no_prefix' to the paramaters.
**an array of data containing with keys
Column Info should be an array of data containing with keys* 'name' of the column
*** ''string'' 'name' of the column
* 'type' of the column
*** ''string'' 'type' of the column
* 'size' of the column if required by type.
*** ''int'' 'size' of the column if required by type.
* 'null', whether to use "null" or "not null"
*** ''bool'' 'null', whether to use "null" or "not null"
* 'default' should contain the default value for the column
*** ''string/int'' 'default' should contain the default value for the column
* 'auto' tells wether the column uses auto_increment or not.
*** ''bool''' 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.
*** ''bool'' '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.
* parameters
* if_exists controls what to do if the column exists, by default it updates the column
** deprecated as of 2.0 RC3, leave empty
* if_exists
** controls what to do if the column exists
*** 'update' - updates the column
* error
** what to do if an error was encountered
*** fatal - Default
'''Returns''' ''bool'' true if column added (changed); false otherwise


===db_add_index===
===db_add_index===
'''Usage''' $smcFunc['db_add_index'] (table_name, index_into, parameters, if_exists, error)===
'''Usage''' $smcFunc['db_add_index'] (table_name, index_info, parameters, if_exists, error)
* with db_extend('packages');
 
* This function allows for adding an index to a table.
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
'''Parameters'''
* 'type' of the column ('primary', 'unique')
* table_name - an already existing table
* 'is_primary' allows the column to be primary or not.
* index_info - an array of data as follows:
* Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
** ''string'' 'type' - index type. Can be one of 'primary', 'unique', or 'index'. Leave this entry out to keep a regular index.
* if_exists controls what to do if the column exists, by default it updates the column
** ''string'' 'name' - name of index, can be left blank to use first column name.
** 'array'' 'columns' - column names
* parameters
** none, leave empty
* if_exists
** controls what to do if the index exists
*** 'update' - updates the index but overwrites if primary
* error
** what to do if an error was encountered
*** fatal - Default
'''Returns''' ''bool'' false if no columns specified


===db_calculate_type===
===db_calculate_type===
'''Usage''' $smcFunc['db_calculate_type'] (type_name, type_size, reverse)===
'''Usage''' $smcFunc['db_calculate_type'] (type_name, type_size, reverse)
* with db_extend('packages');
 
* This function will calculate the type and size for a column.
This function will calculate the type and size for a column.
 
'''Parameters'''
* type_name should be the type of the column.
* type_name should be the type of the column.
* type_size contains the size of the column (can be empty
* type_size contains the size of the column (can be empty
* reverse whether or not to reverse things
'''Returns''' ''array'' the first value is the type, and the second value is the size


===db_change_column===
===db_change_column===
'''Usage''' $smcFunc['db_change_column'] (table_name, old_column, column_info, parameters, error)===
'''Usage''' $smcFunc['db_change_column'] (table_name, old_column, column_info, parameters, error)
* with db_extend('packages');
 
* This function allows for changing an existing column structure.
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.
'''Parameters'''
'column_info' should be an array of data containing with keys* 'name' of the column
* table_name -  an already existing table
* 'type' of the column
* old_column - the name of the column to change
* 'size' of the column if required by type.
*column_info - an array of data containing with keys
* 'null', whether to use "null" or "not null"
** ''string'' 'name' of the column
* 'default' should contain the default value for the column
** ''string'' 'type' of the column
* 'auto' tells whether the column uses auto_increment or not.
** ''int'' 'size' of the column if required by type.
* As of 2.0 RC2, 'unsigned' (true/false) specifies whether the column is unsigned or not.
** ''bool'' 'null', whether to use "null" or "not null"
* Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
** ''string/int'' 'default' should contain the default value for the column
** ''bool''' auto' tells whether the column uses auto_increment or not.
** ''bool'' 'unsigned' (true/false) specifies whether the column is unsigned or not.
* parameters
** deprecated as of 2.0 RC3, leave empty
* error
** what to do if an error was encountered
*** fatal - Default
'''Returns''' ''bool'' true if column added (changed); false otherwise


===db_create_table===
===db_create_table===
'''Usage''' $smcFunc['db_create_table'] (table_name, columns, indexes, parameters, if_exists, error)===
'''Usage''' $smcFunc['db_create_table'] (table_name, columns, indexes, parameters, if_exists, error)
* with db_extend('packages');
 
* This function allows for creating a table. You can not create a SMF default table.
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.
'''Parameters'''
* if_exists is by default using "update". Other options are 'overwrite', 'ignore', 'error', 'update_remove',  
* table_name - name of the table
* Parameters contains special items such in an array such as 'no_prefix' to not auto add a the database prefix.
* columns - a multi-dimensional array containing the columns to be in the table. The first-level elements are used as individual columns, depicted by the example. The second-level items are the column details as seen below.
* if_exists controls what to do if the column exists, by default it updates the column
** ''string'' 'name' of the column
** ''string'' 'type' of the column
** ''int'' 'size' of the column if required by type.
** ''bool'' 'null', whether to use "null" or "not null"
** ''string/int'' 'default' should contain the default value for the column
** ''bool''' auto' tells whether the column uses auto_increment or not.
** ''bool'' 'unsigned' (true/false) specifies whether the column is unsigned or not.
* indexes - a multi-dimensional array containing the indexes to be in the table. The first-level elements are used as individual indexes, depicted by the example. The second-level items are the index details as seen below.
** ''string'' 'type' - index type. Can be one of 'primary', 'unique', or 'index'. Leave this entry out to keep a regular index.
** ''string'' 'name' - name of index, can be left blank to use first column name.
** 'array'' 'columns' - column names
* parameters
** deprecated as of 2.0 RC3, leave empty
* if_exists - controls what to do if the table exists.
** 'ignore' will do nothing if the table exists. (And will return true)
** 'overwrite' will drop any existing table of the same name.
** 'error' will return false if the table already exists.
* error  - what to do if an error was encountered
** fatal - Default
'''Returns''' ''bool'' false if the table name was a SMF table.
 
'''Example'''
{{code|1=<nowiki>  $columns = array(
array(
'name' => 'id_article',
'type' => 'int',
'size' => 10,
'unsigned' => true,
'auto' => true,
),
array(
'name' => 'id_category',
'type' => 'int',
'size' => 10,
'unsigned' => true,
),
array(
'name' => 'poster_time',
'type' => 'int',
'size' => 10,
'unsigned' => true,
),
array(
'name' => 'content',
'type' => 'text',
),
);
 
$indexes = array(
array(
'type' => 'primary',
'columns' => array('id_article')
),
array(
'columns' => array('id_category')
),
);
 
$smcFunc['db_create_table']('{db_prefix}articles', $columns, $indexes, array(), 'update_remove');
 
$columns = array(
array(
'name' => 'id_category',
'type' => 'int',
'size' => 10,
'unsigned' => true,
'auto' => true,
),
array(
'name' => 'name',
'type' => 'tinytext',
),
);
 
$indexes = array(
array(
'type' => 'primary',
'columns' => array('id_category')
),
);
 
$smcFunc['db_create_table']('{db_prefix}article_categories', $columns, $indexes, array(), 'update_remove');</nowiki>}}


===db_drop_table===
===db_drop_table===
'''Usage''' $smcFunc['db_drop_table'] (table_name, parameters, error)===
'''Usage''' $smcFunc['db_drop_table'] (table_name, parameters, error)
* with db_extend('packages');
* with db_extend('packages');
* This function allows for removal a table. You can not delete a SMF default table.
* This function allows for removal a table. You can not delete a SMF default table.
Line 278: Line 411:


===db_table_structure===
===db_table_structure===
'''Usage''' $smcFunc['db_table_structure'] (table_name)===
'''Usage''' $smcFunc['db_table_structure'] (table_name)
* with db_extend('packages');
* with db_extend('packages');
* This function returns the structure of a table.
* This function returns the structure of a table.
Line 285: Line 418:


===db_list_columns===
===db_list_columns===
'''Usage''' $smcFunc['db_list_columns'] (table_name, detail)===
'''Usage''' $smcFunc['db_list_columns'] (table_name, detail)
* with db_extend('packages');
* with db_extend('packages');
* This function returns the current columns in a table in a multi-dimensional array
* This function returns the current columns in a table in a multi-dimensional array
Line 292: Line 425:


===db_list_indexes===
===db_list_indexes===
'''Usage''' $smcFunc['db_list_indexes'] (table_name, detail)===
'''Usage''' $smcFunc['db_list_indexes'] (table_name, detail)
* with db_extend('packages');
* with db_extend('packages');
* This function returns the current indexes in a table in a multi-dimensional array
* This function returns the current indexes in a table in a multi-dimensional array
Line 299: Line 432:


===db_remove_column===
===db_remove_column===
'''Usage''' $smcFunc['db_remove_column'] (table_name, column_name, parameters, error)===
'''Usage''' $smcFunc['db_remove_column'] (table_name, column_name, parameters, error)
* with db_extend('packages');
* with db_extend('packages');
* This function removes a column
* This function removes a column
* Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.
* Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the parameters.


===db_remove_index===
===db_remove_index===
'''Usage''' $smcFunc['db_remove_index'] (table_name, index_name, parameters, error)===
'''Usage''' $smcFunc['db_remove_index'] (table_name, index_name, parameters, error)
* with db_extend('packages');
* with db_extend('packages');
* This function removes a index
* This function removes a index
* Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the paramaters.
* Table_name can have a database prefix. If you specific a database prefix, add 'no_prefix' to the parameters.


{{Developing SMF}}
{{Developing SMF}}
[[Category:2.0]]
[[Category:Package SDK]]

Latest revision as of 14:48, 15 August 2015

These functions are used throught SMF code in order to deal with different versions of php, different character sets, and different databases.

General Utility Functions

These functions are defined in function reloadSettings, in Load.php. Some of these functions have the same name as standard PHP functions, but were designed to deal uniformly with UTF-8 character sets, HTML entities, and differences in behavior in different versions of PHP. It is recommended that they be used instead of the functions they replace.

Database Functions

In SMF 2.0, multiple database support was introduced. This was implemented by developers as a new layer of database functions along with a new security model, which provides a fast and secure method to work across database systems. Below is a list of the database functions that currently exist in 2.0. Each of these links will direct you towards a section about that function that will help you understand what each one does, how its input is expected and if possible, the exact duplicate function for mysql. An example is provided as well for most of these, these examples come straight from the SMF Source code.

Please note our Function Database now has the latest SMF 2.0 functions for your information and may help in explaining the functions. They do not use the $smcFunc variables that this guide does. For most of your functions you will see "smf_db_xxx" where xxx is the function name such as "smf_db_insert" that is used by $smcFunc['db_insert'].

db_query

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

  • Works Similar to how db_query worked in 1.x versions.
  • identifier is used to identify specific query types in which it is necessary to replace portions of the query for compatability across all Database Management Systems supported by SMF. The query must be written for MYSQL. The db_query function will alter the query.
    • 'substring' --- for the SQLite database, SUBSTRING will be replaced by SUBSTR
    • See the function smf_db_query in Subs-DB-{dbms}.php for the full list of replacements.
  • Values is an array of values you are intending to use in the query.

Example

            $result = $smcFunc['db_query']('', '
                SELECT poster_time
                FROM {db_prefix}messages
                WHERE id_msg = {int:id_msg}
                LIMIT 1',
                array(
                    'id_msg' => $user_settings['id_msg_last_visit'],
                )
            );

Example with identifier 'substring

$result = $smcFunc['db_query']('substring', '
	SELECT m.subject, 
		'. ($mylimit > 0 ? SUBSTRING(m.body, 1, '.$mylimit.') as body  : 'm.body'). ', 
		IFNULL(mem.real_name, m.poster_name) AS realName, m.poster_time as date, mem.avatar, mem.posts, mem.date_registered as dateRegistered,mem.last_login as lastLogin,
		IFNULL(a.id_attach, 0) AS ID_ATTACH, a.filename, a.attachment_type as attachmentType, t.id_board as category, b.name as category_name,
		array(
			'id_msg' => $user_settings['id_msg_last_visit'],
		)
	);

db_quote

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

  • 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.

Example

             $realNameMatches[] = $smcFunc['db_quote'](
                '{string:possible_user}', 
                array(
                    'possible_user' => $possible_user
                )
            );

db_fetch_assoc

Usage $smcFunc['db_fetch_assoc'] ($result)

Example

          while ($row = $smcFunc['db_fetch_assoc']($request))
            $search_params['brd'][] = $row['id_board'];

db_fetch_row

Usage $smcFunc['db_fetch_row'] ($result)

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

Example

        while ($row = $smcFunc['db_fetch_row']($request))
            $toDelete[] = $row[0];

db_free_result

Usage $smcFunc['db_free_result'] ($result)

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

Example

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

db_insert

Usage $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

          $smcFunc['db_insert']('replace',
            '{db_prefix}log_topics',
            array(
                'id_member' => 'int', 'id_topic' => 'int', 'id_msg' => 'int',
            ),
            array(
                $user_info['id'], $topic, $modSettings['maxMsgID'],
            ),
            array('id_member', 'id_topic')
        );

db_insert_id

Usage $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

          $bcinfo['id_poll'] = $smcFunc['db_insert_id']('{db_prefix}polls', 'id_poll');

db_num_rows

Usage $smcFunc['db_num_rows'] ($result)

Example

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

db_data_seek

Usage $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

      // Start from the beginning...
    if ($reset)
        return @$smcFunc['db_data_seek']($messages_request, 0);

db_num_fields

Usage $smcFunc['db_num_fields'] ($result)

Example

         // 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]))
                $field_list[] = $row[$j];
            else
                $field_list[] = ''' . $smcFunc['db_escape_string']($row[$j]) . ''';
        }

db_escape_string

Usage $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

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

db_unescape_string

Usage $smcFunc['db_unescape_string'] (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

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

db_server_info

Usage $smcFunc['db_server_info'] (connection)

  • Attempts to get database server information.

Example

          // Some MySQL versions are superior to others.
        $this->canDoBooleanSearch = version_compare($smcFunc['db_server_info']($db_connection), '4.0.1', '>=') == 1;

db_affected_rows

Usage $smcFunc['db_affected_rows'] (connection)

Example

        if ($smcFunc['db_affected_rows']() <= 0)
        {
            loadLanguage('Admin');
            fatal_lang_error('salvaged_category_error', false);
        }

db_transaction

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

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

Example

      $smcFunc['db_transaction']('begin');
    // Do the table and indexes...
    $smcFunc['db_query'](''', $table_query,
        'security_override'
    );
    foreach ($index_queries as $query)
        $smcFunc['db_query'](''', $query,
        'security_override'
    );

    $smcFunc['db_transaction']('commit');

db_error

Usage $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

              // 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 == ''' ? ''' : "" . $smcFunc['db_title'] . ' reported:' . "" . $db_error) . "" . 'This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.');

db_select_db

Usage $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.

No Examples

db_title

Usage $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.

No Examples


db_sybase

Usage $smcFunc['db_sybase'] ()

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

No Examples


db_case_sensitive

Usage $smcFunc['db_case_sensitive'] ()

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

No Examples

Database Package Functions only.

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

db_add_column

Usage $smcFunc['db_add_column'] (string $table_name, array $column_info, array $parameters, string $if_exists, string $error)

This function allows for adding a column to a table.

Parameters

  • table_name
    • an already existing table.
  • column_info
    • an array of data containing with keys
      • string 'name' of the column
      • string 'type' of the column
      • int 'size' of the column if required by type.
      • bool 'null', whether to use "null" or "not null"
      • string/int 'default' should contain the default value for the column
      • bool' auto' tells whether the column uses auto_increment or not.
      • bool 'unsigned' (true/false) specifies whether the column is unsigned or not.
  • parameters
    • deprecated as of 2.0 RC3, leave empty
  • if_exists
    • controls what to do if the column exists
      • 'update' - updates the column
  • error
    • what to do if an error was encountered
      • fatal - Default

Returns bool true if column added (changed); false otherwise

db_add_index

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

This function allows for adding an index to a table.

Parameters

  • table_name - an already existing table
  • index_info - an array of data as follows:
    • string 'type' - index type. Can be one of 'primary', 'unique', or 'index'. Leave this entry out to keep a regular index.
    • string 'name' - name of index, can be left blank to use first column name.
    • 'array 'columns' - column names
  • parameters
    • none, leave empty
  • if_exists
    • controls what to do if the index exists
      • 'update' - updates the index but overwrites if primary
  • error
    • what to do if an error was encountered
      • fatal - Default

Returns bool false if no columns specified

db_calculate_type

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

This function will calculate the type and size for a column.

Parameters

  • type_name should be the type of the column.
  • type_size contains the size of the column (can be empty
  • reverse whether or not to reverse things

Returns array the first value is the type, and the second value is the size

db_change_column

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

This function allows for changing an existing column structure.

Parameters

  • table_name - an already existing table
  • old_column - the name of the column to change
  • column_info - an array of data containing with keys
    • string 'name' of the column
    • string 'type' of the column
    • int 'size' of the column if required by type.
    • bool 'null', whether to use "null" or "not null"
    • string/int 'default' should contain the default value for the column
    • bool' auto' tells whether the column uses auto_increment or not.
    • bool 'unsigned' (true/false) specifies whether the column is unsigned or not.
  • parameters
    • deprecated as of 2.0 RC3, leave empty
  • error
    • what to do if an error was encountered
      • fatal - Default

Returns bool true if column added (changed); false otherwise

db_create_table

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

This function allows for creating a table. You can not create a SMF default table.

Parameters

  • table_name - name of the table
  • columns - a multi-dimensional array containing the columns to be in the table. The first-level elements are used as individual columns, depicted by the example. The second-level items are the column details as seen below.
    • string 'name' of the column
    • string 'type' of the column
    • int 'size' of the column if required by type.
    • bool 'null', whether to use "null" or "not null"
    • string/int 'default' should contain the default value for the column
    • bool' auto' tells whether the column uses auto_increment or not.
    • bool 'unsigned' (true/false) specifies whether the column is unsigned or not.
  • indexes - a multi-dimensional array containing the indexes to be in the table. The first-level elements are used as individual indexes, depicted by the example. The second-level items are the index details as seen below.
    • string 'type' - index type. Can be one of 'primary', 'unique', or 'index'. Leave this entry out to keep a regular index.
    • string 'name' - name of index, can be left blank to use first column name.
    • 'array 'columns' - column names
  • parameters
    • deprecated as of 2.0 RC3, leave empty
  • if_exists - controls what to do if the table exists.
    • 'ignore' will do nothing if the table exists. (And will return true)
    • 'overwrite' will drop any existing table of the same name.
    • 'error' will return false if the table already exists.
  • error - what to do if an error was encountered
    • fatal - Default

Returns bool false if the table name was a SMF table.

Example

  $columns = array(
	array(
		'name' => 'id_article',
		'type' => 'int',
		'size' => 10,
		'unsigned' => true,
		'auto' => true,
	),
	array(
		'name' => 'id_category',
		'type' => 'int',
		'size' => 10,
		'unsigned' => true,
	),
	array(
		'name' => 'poster_time',
		'type' => 'int',
		'size' => 10,
		'unsigned' => true,
	),
	array(
		'name' => 'content',
		'type' => 'text',
	),
);

$indexes = array(
	array(
		'type' => 'primary',
		'columns' => array('id_article')
	),
	array(
		'columns' => array('id_category')
	),
);

$smcFunc['db_create_table']('{db_prefix}articles', $columns, $indexes, array(), 'update_remove');

$columns = array(
	array(
		'name' => 'id_category',
		'type' => 'int',
		'size' => 10,
		'unsigned' => true,
		'auto' => true,
	),
	array(
		'name' => 'name',
		'type' => 'tinytext',
	),
);

$indexes = array(
	array(
		'type' => 'primary',
		'columns' => array('id_category')
	),
);

$smcFunc['db_create_table']('{db_prefix}article_categories', $columns, $indexes, array(), 'update_remove');

db_drop_table

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

  • 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.

db_table_structure

Usage $smcFunc['db_table_structure'] (table_name)

  • 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.

db_list_columns

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

  • 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'.

db_list_indexes

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

  • 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'

db_remove_column

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

  • 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 parameters.

db_remove_index

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

  • 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 parameters.


Advertisement: