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']
Standard Database Functions:- $smcFunc['db_query'] (https://dev.simplemachines.org/smcfunctions.php#db_query)
- $smcFunc['db_quote'] (https://dev.simplemachines.org/smcfunctions.php#db_quote)
- $smcFunc['db_fetch_assoc'] (https://dev.simplemachines.org/smcfunctions.php#db_fetch_assoc)
- $smcFunc['db_fetch_row'] (https://dev.simplemachines.org/smcfunctions.php#db_fetch_row)
- $smcFunc['db_free_result'] (https://dev.simplemachines.org/smcfunctions.php#db_free_result)
- $smcFunc['db_insert'] (https://dev.simplemachines.org/smcfunctions.php#db_insert)
- $smcFunc['db_insert_id'] (https://dev.simplemachines.org/smcfunctions.php#db_insert_id)
- $smcFunc['db_num_rows'] (https://dev.simplemachines.org/smcfunctions.php#db_num_rows)
- $smcFunc['db_data_seek'] (https://dev.simplemachines.org/smcfunctions.php#db_data_seek)
- $smcFunc['db_num_fields'] (https://dev.simplemachines.org/smcfunctions.php#db_num_fields)
- $smcFunc['db_escape_string'] (https://dev.simplemachines.org/smcfunctions.php#db_escape_string)
- $smcFunc['db_unescape_string'] (https://dev.simplemachines.org/smcfunctions.php#db_unescape_string)
- $smcFunc['db_server_info'] (https://dev.simplemachines.org/smcfunctions.php#db_server_info)
- $smcFunc['db_tablename'] (https://dev.simplemachines.org/smcfunctions.php#db_tablename)
- $smcFunc['db_affected_rows'] (https://dev.simplemachines.org/smcfunctions.php#db_affected_rows)
- $smcFunc['db_transaction'] (https://dev.simplemachines.org/smcfunctions.php#db_transaction)
- $smcFunc['db_error'] (https://dev.simplemachines.org/smcfunctions.php#db_error)
- $smcFunc['db_select_db'] (https://dev.simplemachines.org/smcfunctions.php#db_select_db)
- $smcFunc['db_title'] (https://dev.simplemachines.org/smcfunctions.php#db_title)
- $smcFunc['db_sybase'] (https://dev.simplemachines.org/smcfunctions.php#db_sybase)
- $smcFunc['db_case_sensitive'] (https://dev.simplemachines.org/smcfunctions.php#db_case_sensitive)
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_add_column'] (https://dev.simplemachines.org/smcfunctions.php#db_add_column)
- $smcFunc['db_add_index'] (https://dev.simplemachines.org/smcfunctions.php#db_add_index)
- $smcFunc['db_calculate_type'] (https://dev.simplemachines.org/smcfunctions.php#db_calculate_type)
- $smcFunc['db_change_column'] (https://dev.simplemachines.org/smcfunctions.php#db_change_column)
- $smcFunc['db_create_table'] (https://dev.simplemachines.org/smcfunctions.php#db_create_table)
- $smcFunc['db_drop_table'] (https://dev.simplemachines.org/smcfunctions.php#db_drop_table)
- $smcFunc['db_table_structure'] (https://dev.simplemachines.org/smcfunctions.php#db_table_structure)
- $smcFunc['db_list_columns'] (https://dev.simplemachines.org/smcfunctions.php#db_list_columns)
- $smcFunc['db_list_indexes'] (https://dev.simplemachines.org/smcfunctions.php#db_list_indexes)
- $smcFunc['db_remove_column'] (https://dev.simplemachines.org/smcfunctions.php#db_remove_column)
- $smcFunc['db_remove_index'] (https://dev.simplemachines.org/smcfunctions.php#db_remove_index)
$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:
$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:
$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 (https://php.net/mysql_fetch_assoc)
Example:
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 (https://php.net/mysql_fetch_row).
- Emulated while using SQlite with smf_sqlite_fetch_row.
Example:
list ($file_data, $filetype) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result'] ($result)- Will return exact same results as mysql_free_result (https://php.net/mysql_free_result).
- Emulated while using SQlite with smf_sqlite_free_result.
Example:
$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:
$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 (https://php.net/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:
$old_id = $smcFunc['db_insert_id']($table);
$smcFunc['db_num_rows'] ($result)- Will return exact same results as mysql_num_rows (https://php.net/mysql_num_rows).
[/li]
[/list]
Example:
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 (https://php.net/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:
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 (https://php.net/mysql_num_fields).
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]) && (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 (https://php.net/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);
$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:
// 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:
if ($smcFunc['db_server_info'] < 8.0)
$smcFunc['db_affected_rows'] (connection)- Will return exact same results as mysql_affected_rows (https://php.net/mysql_affected_rows).
Example:
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".
$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 (https://php.net/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 == '' ? '' : "\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 (https://php.net/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:
// 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:
// 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:
// 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.
Fixed a few things. Split out team replies, and moving to Charter boards.
Charter Customizers (cool name eh) and Charter Members in general, any feedback on this documentation would be great. I plan on moving this to the Customize Support boards after the first public beta of 2.0
Looks good, but the second section (db_extend('packages') area) has some broken BBCode.
And do you really need to reinforce that the stuff that is listed under the "only when you use db_extend('packages')" header is only available then in every single item?
I fixed the bbc issue. Thats what I get for being lazy and using the editor :P
I wanted to emphasize this in some way, So people don't forget this. Do you think there might be a better way I could emphasize the fact that the functions wont be there unless that function is called?
Nice guide, hope you would've posted this earlier
BTW, I think it is a good idea on giving available parameters on things like if_exists parameter in $smcFunc['db_create_table'].
:( No examples for package functions?
Well, instead of
$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error)
- Only exists if you are using db_extend('packages');
How about
$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error) - with db_extend('packages');
Slightly less annoying, or at least I think so.
(list bullets, they don't appear to work in preview without the WYSIWTF editor. Dunno about with it. No, I don't mean WYSIWYG. ;) )
Quote from: Dragooon on March 16, 2008, 11:26:21 PM
Nice guide, hope you would've posted this earlier
BTW, I think it is a good idea on giving available parameters on things like if_exists parameter in $smcFunc['db_create_table'].
If you look at the date of the first topic you will notice I posted it February 21st. So this topic is not to old. I needed team feedback and other things.
Edit, I missed your second question Dragooon.
Most of them only have one parameter with 'update'. If I remember right only one function uses a second one.
They are their mainly fore future expansion. Just like the priority value was in the sendmail function for 1.1.
Quote from: YodaOfDarkness on March 16, 2008, 11:31:08 PM
:( No examples for package functions?
Well, I thought about it. Creating them would mean doing my own coding, I wanted to try to stay with SMF generic coding for the best examples. If you want some examples, I can sure write a few up.
Quote from: CmptrWz on March 17, 2008, 12:10:51 AM
Well, instead of
$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error)
- Only exists if you are using db_extend('packages');
How about
$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error) - with db_extend('packages');
Slightly less annoying, or at least I think so.
(list bullets, they don't appear to work in preview without the WYSIWTF editor. Dunno about with it. No, I don't mean WYSIWYG. ;) )
Thanks. That is better. I will tweak it.
The first one was kind off sarcasm :P
About the second one, I mean like if_exists got 4 values
'ignore','update','drop','overwrite' If I recall it correctly.
Although you do have a valid point.
But this is a Nice guide :)
I will check at home tonight, but I don't think any of the functions besides one has a second option for if_exists at the moment. The one that does I will update the note on it.
Thanks for posting.
This will come in useful ;)
indeed. Thanks :)
I think we should put this in the public coding boards too :)
I will move it soon. I need to check the if_exists thing first.
Edit, create table is the only one that had more than one option for if_exists.
Updating and moving.
Yes Good Work
Have you opened up the function DB to everyone too?
the 2.0 function DB isn't ready yet
Can someone please clarify, wether if it is $smcFunc or $smfFunc cause both functions are somewhat present in the source files...
I'm kinda confused, now. :\
Nevermind, I found it myself, it's $smcFunc...
Its $smcFunc
$smfFunc was used in an earlier charter member (beta 1). If you have any instances of that then either your forum files aren't the latest, or something went wrong ;)
One thing I don't like is how some functions, like 'db_list_columns' require that you must explicitly put {db_prefix} in the table name while you must not do so in the others (unless you specify 'no_prefix'. This is inconsistent (and has just bitten me).
In the documentation for this, you say
QuoteIf you need to specific the database prefix use {db_prefix}
which is also confusing. It should read that you
must use {db_prefix}.
Whats the real difference?
Must just says it must be there.
You could also force the index yourself before hand it you wanted with
$smcFunc['list_columns']($db_preifx . 'mytable');
Most of the time things like list_columns is having things such as editing a table call it, so it already has the prefix and ignores the use of calling it again.
Inconsistency is always bad. You have a large number of functions that behave one way, and a few related ones (admittedly rarely used) that behave completely differently for no apparent reason.
I've had to use it in a mod install/upgrade script that renames database column names from uppercase to lowercase.
It doesn't say 'must' anywhere in the documentation, it says 'If you need to specify', it doesn't say that you actually need to specify it.
Well you don't need to specific the prefix.
You can use it without the database prefix as well. Thats what it is intended for.
If you want to question the consistency of the functions, you should do this in the feedback board where the developers are more likely to see it.
Quote from: SleePy on April 28, 2008, 11:46:15 AM
Well you don't need to specific the prefix.
You can use it without the database prefix as well. Thats what it is intended for.
You can with the others as well, but in a different manner.
QuoteIf you want to question the consistency of the functions, you should do this in the feedback board where the developers are more likely to see it.
Aye, you're right there. Actually, I'll be better off raising a bug ticket (hell, if typos in variable names are bugs, this is).
hi
i want help when i was going in my profile or any other members's profile then profile shows this error
Unknown column 'com.time' in 'field list'
File: /home/vol1/byethost10.com/b10_2027440/merapind.isgreat.org/htdocs/Sources/Profile.php
Line: 1344
what should it means and what shoul i do? plsss hel me
please start a topic in the support boards.
$smcFunc['db_create_table'] needs some explanation on the syntax for indexes.
[edited]
Here's what I figured out, the basic syntax for the index array is:
// because indexes is an array
array(
// a new array for each index
array(
// makes this a primary key (with no name, obviously)
'type' => 'primary',
// all the columns that will be primary keys
'columns' => array (
'mycol1',
'mycol2'
)
)
// a normal index
array(
//name of index
'name' => 'my_unique_index',
// type can be "unique" or "key" (defaults to "key")
'type' => 'unique',
// all the columns that will be unique keys
'columns' => array (
'mycol1',
'mycol2'
)
),
// a bare minimum index
array(
'name' => 'my_index',
'columns' => array (
'mycol1',
'mycol2'
)
)
)
Quote from: SleePy on February 21, 2008, 05:57:21 PM
The below functions only exist when using db_extend('packages');. By Default this is automatically called in the Package Manager.
Also note that this is not entirely true. I had to specify db_extend('packages') in my install.php file when I called it using the "<code>" tag in package-info.xml.
However, when I changed the <code> tag to <database>, it automatically included db_extend('packages')
I apologize for the multi-post, however these are very separate points that need to be addressed.
It should also be noted that db_drop_table is NOT NECESSARY in the uninstall script of your mod, and should, in fact, be frowned upon. By using drop_table, you are basically forcing the admin to lose data. What is not explained very well is the fact that when create_table is used in the install script, it will add the new table to a database log, and then offer the option to remove the table AUTOMATICALLY when the package is uninstalled.
So don't use drop_table in a package unless you really are sure that you want to do it. Just let the admin check the little box when they go to remove their mod.
How does this look for the index info:
Quote$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.
- 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.
- The indexes is a multi-dimensional array containing the indexes for the table. The basic syntax is: array('name' => 'index_name', 'type' = > 'primary', 'columns => array('column_name', 'another_column'))
- 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
Yes you only need to specify the db_extend('packages') if you are using it outside of the Packages <database> tag. It should still be in your install file though for manual installations.
I believe its in the coding guidelines to not drop tables and such.. I have to many windows open to try and pull it up, but its in the sticky above/below this one :P
Quote$smcFunc['db_add_column'] (table_name, column_into, parameters, if_exists, error) - 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', wether 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.
- 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
[/list]
I am not quite sure I understand how to use this funcion...
This is how I currently add a new column to the database:
global $smcFunc, $db_prefix;
//check if columns exist
$request = $smcFunc['db_query']('', "
SHOW COLUMNS FROM {$db_prefix}messages
",
array()
);
$found = 0; //initialize search
while($c = mysql_fetch_assoc($request)){
if($c['Field'] == 'ua_os'){
$found = 1; //we have a winner
break;
}
}
if ($found == 0) { //insert column
$smcFunc['db_query']('', "
ALTER TABLE {$db_prefix}messages
ADD COLUMN ua_os TINYTEXT NOT NULL
",
array()
);
}
Now, I'm not quite sure how to convert this code to use the db_add_column function... This is what I think it should look like:
Quote
global $smcFunc;
$smcFunc['db_add_column']("messages",
array(
'name' => 'ua_os', 'type' => 'tinytext', 'size' => '', 'null' => 'not null', 'default' => '', 'auto' => '',
),
array(),
if_exists???,
''
);
I have no idea what to put in the if_exists parameter, I want to just skip the whole thing if the column is already there...
Oh, and what exactly does "with db_extend('packages')" mean?
if_exists defaults to 'update'. It'll just use db_change_column for you, and overwrite the old column. Use any other value, and if it already exists, it will return false.
db_extend() adds extra $smcFunc['db_...'] functions. Default parameter is 'extra'.
db_extend('packages');
'db_add_column'
'db_add_index'
'db_calculate_type'
'db_change_column'
'db_create_table'
'db_drop_table'
'db_table_structure'
'db_list_columns'
'db_list_indexes'
'db_remove_column'
'db_remove_index'
db_extend('search');
'db_search_query'
'db_search_support'
'db_create_word_search'
'db_support_ignore'
db_extend();
'db_backup_table'
'db_optimize_table'
'db_insert_sql'
'db_table_sql'
'db_list_tables'
'db_get_version'
Thank you for your reply, I will try that.
Quote from: YodaOfDarkness on November 20, 2008, 10:38:30 PM
db_extend() adds extra $smcFunc['db_...'] functions. Default parameter is 'extra'.
So basically I should call db_extend('packages'); before $smcFunc['db_add_column'] in my code?
Yep :)
Thanks again :)
Okay, I've tried the code mentioned above, and it doesn't work. Here it is again:
Quoteif (!defined('SMF'))
die('Hacking attempt...');
global $smcFunc;
db_extend('packages');
$smcFunc['db_add_column']("messages",
array(
'name' => 'column_name', 'type' => 'tinytext', 'size' => '', 'null' => 'not null', 'default' => '', 'auto' => '',
),
array(),
'do_nothing',
''
);
It doesn't report any errors, but also adds nothing to the table, so I guess some of these parameters are wrong. Any ideas?
Can you try it with removing 'size' and 'default' indexes?
I've removed size, auto and default (which were all blank), and now it works. Thank you ;)
Hi.
db_query("INSERT INTO
{$db_prefix}table
(col1, col2)
VALUES (data1,data2)
, __FILE__, __LINE__
);
Now is equivalent to:
$smcFunc['db_insert']('insert',
'{db_prefix}table',
array( 'col1' => 'type1', 'col2' => 'type2' ),
array( data1, data2 )
);
Ok, But?
db_query("INSERT INTO
{$db_prefix}table
(col1, col2)
VALUES (data1a,data2a), (data1b,data2b), (data1c,data2c), (data1d,data2d)
, __FILE__, __LINE__
);
Which should do? Several queries? ($smcFunc['db_insert']...) :-\
Thanks.
Quote from: M-DVD on November 26, 2008, 10:49:28 PM
Hi.
db_query("INSERT INTO
{$db_prefix}table
(col1, col2)
VALUES (data1,data2)
, __FILE__, __LINE__
);
Now is equivalent to:
$smcFunc['db_insert']('insert',
'{db_prefix}table',
array( 'col1' => 'type1', 'col2' => 'type2' ),
array( data1, data2 )
);
Ok, But?
db_query("INSERT INTO
{$db_prefix}table
(col1, col2)
VALUES (data1a,data2a), (data1b,data2b), (data1c,data2c), (data1d,data2d)
, __FILE__, __LINE__
);
Which should do? Several queries? ($smcFunc['db_insert']...) :-\
Thanks.
$smcFunc['db_insert']('insert',
'{db_prefix}table',
array( 'col1' => 'type1', 'col2' => 'type2' ),
array(
array(data1, data2),
array(data3, data4),
)
);
Quote from: Niko on November 27, 2008, 05:06:00 AM
$smcFunc['db_insert']('insert',
'{db_prefix}table',
array( 'col1' => 'type1', 'col2' => 'type2' ),
array(
array(data1, data2),
array(data3, data4),
)
);
:o Thanks, it works
Quote$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:
QuoteKode: [Vælg]
$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')
);
"Keys is supposed to hold the tables key information, only appears to affect sqlite and postrgresql (when using "replace") versions."
So what does that mean??
IIRC, you can put an array of the table's keys there (i.e. array('id_member', 'id_board'))
I don't remember ever putting anything there, though... it's been a while since I've done any 2.0 coding.
If you don't put anything, you get an error in your log.
Then you need to find the keys (index/primary) for the table you're modifying, and put them in an array in the "keys" parameter.
Yeah I did that. I just don't know why I have to do it.
apparently sqlite and postgresql need it... and 2.0 is supposed to be compatible with those two also.
Yeah I got that too, but if it is essential for a mod to work in SMF2 with other databases, I need to know what it is for.
As I said, sqlite and postgresql seem to need it in their queries when doing an insert.
From Subs-dq-sqlite:
// If it's a key we don't actally update it.
if (in_array($columnName, $keys))
$where .= (empty($where) ? '' : ' AND ') . substr($actualType,0, -2);
else
$updateData .= $actualType;
How can I set the character set to utf8 and the collation to utf8_general_ci with db_create_table?
Is there any solution?
I'm currently doing several db_queries to change collations of the table and all columns after I create the table, like this:
$smcFunc['db_query']('',
'ALTER TABLE {db_prefix}table COLLATE utf8_general_ci');
$smcFunc['db_query']('',
'ALTER TABLE {db_prefix}table CHANGE colname colname type CHARACTER SET utf8 COLLATE utf8_unicode_ci');
And it works, but couldn't that be set in db_create_table somehow?
Quote from: SleePy on February 21, 2008, 05:57:21 PM
$smcFunc['db_query'] (identifier, query, values, connection)
- ...
- Identifier is used for identifying specific queries that will be handled specially.
What is the identifier supposed to do/aim to? I want to know if i'm missing its functionality (which i could then take advantage of) simply because i'm ignoring what it does.
Do you have an example of the identifier in use?[/list]
You pretty much always leave the identifier blank. The only one I've ever used is truncate_table, which I think is needed to let it do text replacements with the different database systems.
Allright, thanks :)
hi, can anyone help me to transform this:
// Delete it?
if (in_array($i, $delete_tags))
{
db_query("
DELETE FROM {$db_prefix}settings
WHERE variable LIKE 'custom123_%_$i'
", __FILE__, __LINE__);
$_POST['custom123_count']--;
}
else
{
$save_vars[] = array('check', 'custom123_enable_' . $i);
$save_vars[] = array('check', 'custom123_button_' . $i);
}
}
saveDBSettings($save_vars);
redirectexit('action=admin;area=featuresettings;sa=123');
}
is it equivalent to?
thanks :)
I'm not sure but try this:
// Delete it?
if (in_array($i, $delete_tags))
{
$smcFunc['db_query']('', '
DELETE FROM {db_prefix}settings
WHERE variable LIKE {text:123}',
array(
'123' => 'custom123_%_$i',
)
);
$_POST['custom123_count']--;
}
else
{
$save_vars[] = array('check', 'custom123_enable_' . $i);
$save_vars[] = array('check', 'custom123_button_' . $i);
}
}
saveDBSettings($save_vars);
redirectexit('action=admin;area=featuresettings;sa=123');
}
:(
it saying me
Fatal error: Function name must be a string in...
at line 60:
$smcFunc['db_query']('', '
You should add $smcFunc to the global line.
:D great!!
perfectly working :)
thank you very much :D
:( not working
he deleted me all the lines in the table
i think because the line
", __FILE__, __LINE__);
is missing in the translation
__FILE__,__LINE__ is not required anymore.
What do you mean? That code deleted all entries in the table? (The error I noticed is using {text:123} instead of {string:123} but I believe it should throw a fatal error on that).
yes he deleted all entries in the table
i tried {string:123}
no fatal error
but
at first save nothing happens, and at second save all the entries are deleted again
any idea? :)
'123' => 'custom123_%_$i',
should be
'123' => 'custom123_%_' . $i,
great!!!! :D thank you very much :)
i'm not really a fluent with sql script, i want to know what function should is use to update the specific row?
You should $smcFunc['db_query'] for UPDATE. Example.
$smcFunc['db_query']('', '
UPDATE {db_prefix}some_table
SET some_field = {int:some_parameter_1}
WHERE some_field = {int:some_parameter_2}',
array(
'some_parameter_1' => 1,
'some_paremeter_2' => 2,
)
);
Hi Tenma,
Thanks for the help. but i hope you can help me more on my modification. Here is my problem. I have this code added on my profile.template.php, I put there a textarea where user can edit their own css text to customize there own theme and this is already working and displaying the css text on my textarea, my problem now is how can is use the function that you pointed to update the css field on my members table with the value from textarea?
<textarea name="style" rows="30" cols="60">', $user_info['css'] ,'</textarea><input type="submit" action="SaveStyle" style="margin: 0 1ex 1ex 1ex;" value="Save Style" />
please help.
In which profile area do you have that field added?
In the Looks and Layout.
Try this.
Sources/Profile-Modify.php
// Free memory!
if (isset($_POST['style']))
updateMemberData($context['id_member'], array('css' => $smcFunc['htmlspecialchars']($_POST['style'], ENT_QUOTES)));
// Free memory!
'css' should be name of the field in the database.
Thanks! you really helped me alot!
You're welcome. Glad that I could be of help.
Yes! and i will show you the result once I'm done.
Thanks for the useful db codes sleepy.
Now i have started learning to creating my own mod for SMF.
I know RC1-1 is still not the final release, and I know this is something that must have been considered already, given the quality that the SMF devs always look for this piece of software, so please take this question as a humble reminder :)
Will it be allowed for column creation with the UNSIGNED attribute? Currently RC1-1 can't handle it and it's not possible for a mod to be designed with this in mind. Also, if a table has to be created having columns that replicate columns from default SMF tables (i.e. id_member), the created columns won't be true replicas from the originals.
Quote from: OutofOrder on June 12, 2009, 01:44:20 AM
I know RC1-1 is still not the final release, and I know this is something that must have been considered already, given the quality that the SMF devs always look for this piece of software, so please take this question as a humble reminder :)
Will it be allowed for column creation with the UNSIGNED attribute? Currently RC1-1 can't handle it and it's not possible for a mod to be designed with this in mind. Also, if a table has to be created having columns that replicate columns from default SMF tables (i.e. id_member), the created columns won't be true replicas from the originals.
RC2 will include support for unsigned columns.
@Arantor: as of RC1-1, $smcFunc['db_add_column'] nor $smcFunc['db_create_table'] have a working parameter to create new unsigned columns.
@Niko: that's great to know, thank you. :)
Quote from: Arantor on June 26, 2009, 07:39:36 PM
Oh, just noticed...
db_change_column isn't documented as far as I can see - there's a link but the link doesn't go anywhere.
Updated. Thanks!
No examples for package functions?
You can find many examples if you check a few mods in the Mod Site.
thanks ;D
I've noticed that you can't reference one same value twice (or more) inside a query.
As of SMF 2 RC1-2, this is not possible:
<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist})
OR column_b IN ({array_int:numlist})',
array( 'numlist' => $myArray ));
?>
for such code will throw an error "The database value you're trying to insert does not exist: numlist".
I think this is because of the way preg_replace_callback works: in smf_db_quote(), the call to smf_db_replacement__calback() function won't return a match for the second time {array_int:numlist} is parsed.
If I wanted to get that query working, the fix would have to be this one:
<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist1})
AND column_b IN ({array_int:numlist2})',
array( 'numlist1' => $myArray, 'numlist2' => $myArray ));
?>
And I'd be passing the same array twice. If you worry about efficiency, this could get you worried a tiny bit.
I would consider that a bug
Would anybody mind if I backed up a little bit and asked for some explanations of the explanation? For example:
Quote
$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.
$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'],
)
);
We're told that there is an identifier which is "used for identifying specific queries that will be handled specially." How and when would such an identifier be used? What specific queries would use one? How would they be handled?
Then there's
{int:id_msg}
which seems to relate to
'id_msg' => $user_settings['id_msg_last_visit'],
except that the
int part has not been defined or explained. What does it mean? Is it always there? Are there other things that might be used instead of
int?
What happens when there are multiple criteria in the "WHERE" clause? Where does an "AND" or "OR" go? Can I get a simple example?
Or....
Is there some place I can go to get this information? If I need help with php, I usually go to php.net. Is there something similar to learn whatever language this is written in?
Quote from: OutofOrder on August 21, 2009, 02:25:12 PM
I've noticed that you can't reference one same value twice (or more) inside a query.
As of SMF 2 RC1-2, this is not possible:
<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist})
OR column_b IN ({array_int:numlist})',
array( 'numlist' => $myArray ));
?>
for such code will throw an error "The database value you're trying to insert does not exist: numlist".
I think this is because of the way preg_replace_callback works: in smf_db_quote(), the call to smf_db_replacement__calback() function won't return a match for the second time {array_int:numlist} is parsed.
If I wanted to get that query working, the fix would have to be this one:
<?php
$smcFunc['db_query']('',
'SELECT * FROM mytable
WHERE column_a IN ({array_int:numlist1})
AND column_b IN ({array_int:numlist2})',
array( 'numlist1' => $myArray, 'numlist2' => $myArray ));
?>
And I'd be passing the same array twice. If you worry about efficiency, this could get you worried a tiny bit.
First off, your query above implies 2 different methods. Are you trying to get the row when both columns equal the integer found in the array (implied in your 2nd query by using AND) OR are you trying to get a row of all columns when either column_a OR column_b equal any of the values in the integer array (implied in your 1st query using OR).
Ok, I have no clue why you would need to define numlist1 and numlist2 to both equal the same array. All you need is 'numlist' => $myArray
Here's how you would do it (Returns all column info from mytable when either column_a OR column_b equals any integer values found within $myArray):
$request = $smcFunc['db_query']('', '
SELECT *
FROM mytable
WHERE (column_a IN ({array_int:numlist}) OR column_b IN ({array_int:numlist}))',
array(
'numlist' => $myArray,
)
);
Have tested this and works perfect. I think your problem is that you didn't encapsulate the entire condition within the paranthesis, which should be done. Unless I am misunderstanding what you are saying here.
Cheers :)
Quote from: JPDeni on August 24, 2009, 01:03:00 PM
Would anybody mind if I backed up a little bit and asked for some explanations of the explanation? For example:
Quote
$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.
$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'],
)
);
We're told that there is an identifier which is "used for identifying specific queries that will be handled specially." How and when would such an identifier be used? What specific queries would use one? How would they be handled?
Then there's
{int:id_msg}
which seems to relate to
'id_msg' => $user_settings['id_msg_last_visit'],
except that the int part has not been defined or explained. What does it mean? Is it always there? Are there other things that might be used instead of int?
What happens when there are multiple criteria in the "WHERE" clause? Where does an "AND" or "OR" go? Can I get a simple example?
Or....
Is there some place I can go to get this information? If I need help with php, I usually go to php.net. Is there something similar to learn whatever language this is written in?
I'm not entirely sure on all of the different types of identifiers there are. I haven't found a section here at SMF that explains this at all, perhaps there is one somewhere here on SMF. Not entirely sure on where to go to actually find all of the different types of identifiers that can be used either. So I'm with you on this 1. Could use a bit more of explanation on this.
Answering your 2nd question....
int is just a way for the function to identify the the expected type. There are currently 4 types that I know about that can be used here:
int, string, array_int, and array_string. I could be wrong, but I believe this to be all of them that can be used. Since everything is either a string or an integer in some way or another. This is one of the extra security features in SMF 2.0. Since this doesn't link it directly to the database query without giving it a specific type to link it too and can not be used by member for input so as to gain Admin Rights on your forums, thus the array() of values. Didn't quite understand this at first either, but now, I couldn't imagine coding without it.
For Example:
QuoteWHERE column_a = {int:myValue',}
array( 'myValue' => $blah, )
As you can see the bold letters here, so you can see a pattern hopefully. Basically, you define the array index by the type and then give it a value. If you do not give it a value, it will not exist and cause an error.
Old way of doing this was:
QuoteWHERE column_a = $blah
Problem with the old way of doing it, if $blah did not make it an integer this could be passed as a string and cause problems since the only values in column_a would be of integer type.
ARANTOR - BEAT ME TO IT!! How Dare you...lol, j/k ;)
well, it's also SQL :P
These are all of the valid types for {______:varname}: (found in Subs-db-mysql.php, smf_db_replacement__callback())
int
string
text
array_int
array_string
date
float
identifier
raw
it'll generally escape them correctly, and throw an error if you try to send the wrong data type (e.g. "My String" is not a float). 'raw' won't do anything to it, just plop it in place and hope you've escaped it all correctly. 'identifier'... I have no clue about.
There's also {db_prefix}, {query_see_board}, and {query_wanna_see_board} :P
Thanks for all the info. I'll see what I can digest.
What would happen if I said
WHERE id_msg = 3
IOW, what if I didn't do that whole array thing and int thing, but just put in a value like is in a MySQL query? Would I get an error?
QuoteThere's also {db_prefix}, {query_see_board}, and {query_wanna_see_board}
One confusing thing at a time! ;D
it will work, but it's best to use the {type:var} way because SMF will sanitize the data for you, and hopefully prevent any stray SQL injections.
Quote from: JPDeni on August 24, 2009, 02:56:15 PM
Thanks for all the info. I'll see what I can digest.
What would happen if I said
WHERE id_msg = 3
IOW, what if I didn't do that whole array thing and int thing, but just put in a value like is in a MySQL query? Would I get an error?
If it will always be 3 and NEVER change based on user input you can do that - and it'll work. Just don't expect to see it approved if you use it in a mod though.
If there's any element of that value coming from the user, you REALLY should use this method of parameterising the query for protection against injections.
Yes, and raw...knew that 1, lol. Just didn't click. Thanks for the float and date Frustrate. What would you use indentifier for?? For Example {identifier:myID} and than how would you link it for array( 'myID' => ????, ). Just curious.
Thanks...
QuoteJust don't expect to see it approved if you use it in a mod though.
I'm just trying to get the things that I write for myself to work! :)
Solo, as I said, I have no idea what identifier is :P I think it has something to do with `database_name`.`table_name`, since it talks about backticks not being needed, which would mean database_name.table_name, which is valid for MySQL.
Okie Dokie. Thanks :)
Quote from: Fustrate on August 24, 2009, 02:48:27 PM
well, it's also SQL :P
These are all of the valid types for {______:varname}: (found in Subs-db-mysql.php, smf_db_replacement__callback())
...
...
text
...
...
it'll generally escape them correctly, and throw an error if you try to send the wrong data type (e.g. "My String" is not a float). 'raw' won't do anything to it, just plop it in place and hope you've escaped it all correctly. 'identifier'... I have no clue about.
Didn't realize you had this in there. {text:varname} doesn't work as a valid type when querying the database. gotta use {string:varname}
That's a bug, then. If you look at the function I mentioned, both cases (text and string) result in the same code being executed.
Quote from: OutofOrder on June 12, 2009, 01:44:20 AM
I know RC1-1 is still not the final release, and I know this is something that must have been considered already, given the quality that the SMF devs always look for this piece of software, so please take this question as a humble reminder :)
Will it be allowed for column creation with the UNSIGNED attribute? Currently RC1-1 can't handle it and it's not possible for a mod to be designed with this in mind. Also, if a table has to be created having columns that replicate columns from default SMF tables (i.e. id_member), the created columns won't be true replicas from the originals.
Wow, just now seeing this, good to know. So while I create MODS that have integers with an unsigned attribute, I suppose will have to use $smcFunc['db_query'] to accomplish this than. Sad that it doesn't do this, was under the impression that it would do this by saying...
'unsigned' => true,
within the array when building it to pass to the $smcFunc['db_add_column'] function.
I don't believe it does, no, but should be fixed for RC2 if it hasn't been already (at least that was my understanding)
Currently updating my MoDs using $smcFunc['db_query'] instead of the new database functions. Only for unsigned integers. Whenever SMF RC2 Final is released I suppose I will change it back. But for now, I think unsigned integers in my mods are important so this needs to be addressed.
Cheers :)
It will be working just like you said in SMF 2.0 RC2. ;)
Ok, thanks [SiNaN], will place the code on standby mode so it's a quick copy and paste whenever RC2 is released.
Cheers :)
This Doesn't Work For Me
When I Make SmcFunc A Global
It Says Db_Query Is Undefind
And This In QueryString.php File
Please, keep that discussion to its own topic.
As I told you repeatedly in that topic the reason it doesn't work is because at the point the output buffer is being called, $smcFunc has NOT been defined thus you can make it global but you can't bring into local scope a variable that HASN'T been defined.
I Really Really Don't Understand A Single Word
i Can't Free Up My Mind To understand :(
Essentially what you're doing is:
function add1() {
global $i;
$i['var']++;
}
add1();
$i = array();
$i['var'] = 1;
$i hasn't been declared by the time add1 is called.
OOOOOOOOOO
Some Examples For Idiots Like Me :)
Yeah But Your Example Made Me Understand
Thx Chief ;)
I am about to release a mod, but before I do, I'd rather not waste aweek waiting for the Customization Team to let me know that it isn'tusing all of the new Database Functions. Reason is that this mod isfor RC1.2 and RC1.2 does not support unsigned integers. So I need theintegers to be unsigned in order for the mod to work properly as ituses the entire maximum of the unsigned integers. My question is: CanI submit this mod using the old database functions and expect to haveit approved, since unsigned integers are necessary in order for the modto function properly??
Ofcourse, I've seen this fixed in RC2already, so I would definitely have to upgrade the mod using all of thenew database functions once RC2 is out.
I'd personally wait for RC2, and let us test it on the corrected DB functions :)
Ok, so I should wait for RC2 then? No problem, will do. I'm sure it would look a lot better in RC2 anyways.
Thanks Fustrate! Big Time Saver! But how long of a wait are we talking about?? Ok Ok, I'm on a Need-To-Know basis, I know, and I don't need to know. It's all good. Just hope it's going to be sooner, rather than later...
Running into a small problem with RC2
To create a table, traditionally I would use a line like...
$smcFunc['db_create_table']('table_name', $columns, $indexes, array(), 'update_remove');
However in RC2 this puts the table in the forum database as "table_name" instead of "smf_table_name". I'm assuming that {db_prefix} needs to be inserted there someplace but what would be the proper syntax?
Quote from: Garou on October 13, 2009, 11:05:51 PM
Running into a small problem with RC2
To create a table, traditionally I would use a line like...
$smcFunc['db_create_table']('table_name', $columns, $indexes, array(), 'update_remove');
However in RC2 this puts the table in the forum database as "table_name" instead of "smf_table_name". I'm assuming that {db_prefix} needs to be inserted there someplace but what would be the proper syntax?
Yeah, this was 1 of the first things I noticed, need to use {db_prefix}. Here's an example:
$smcFunc['db_create_table']('{db_prefix}table_name', $columns, $indexes, array(), 'update_remove');
Thank you very much, kind sir.
And one more mod ready for when RC2 goes public :)
Yes, this was intentional. All of the other db functions required {db_prefix} but this didn't. Plus it also means the package manager can be used for true non SMF stuff.
This is probably a stupid question, but just to confirm before I break something, if I'm wanting to query non-SMF tables from within SMF, would these still be the correct functions to use? In case its relevant, I'm wanting to add a custom block to SimplePortal, that will reference & display a members info in my RPG which is external to SMF. All tables are in the one database, though my RPG tables don't have the smf_ prefix that my SMF tables do.
Yes, these would still be the functions to use, and specifically they have {db_prefix} in when querying them. That said, be careful between RC1.2 and RC2.
When using db_create_table (and db_insert ?), in RC1.2 you would specify just the table name and use 'no_prefix' in the parameters if you want without a prefix. In RC2 you have to be explicit.
You are recommended to leave db_prefix in generally though.
To access existing tables, just use something like
$result = $smcFunc['db_query']('', '
SELECT *
FROM table_name',
array()
);
basically, don't use {db_prefix}
Awesome, thanks yet again for the quick responses :D
Is the values argument of $smcFunc['db_query']() documented anywhere?
I had to search the source myself was curious about db query cause I never use the first argument
function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
Not sure if they are, so I just did it myself. I can put them some place better.
int - an integer. 0 included.
string/text - a string
array_int - an array of integers. Gets checked to make sure every element is an integer and can't be empty. Gets imploded to be a comma delimited list like "1, 2, 3, 4, 5, 6".
array_string - an array of strings. Gets escaped. Can't be empty. Gets imploded to be a comma delimited list like "a, b, c, d, e, f, g".
date - a date in the format of YYYY-MM-DD. If it isn't in that format, it throws an error.
float - a number.
identifier - removes backticks and periods and returns a string in the format of `string`
raw - nothing gets changed.
It defaults to an error. So, if you don't pick one of these, you're going to get errors.
Quote
$smcFunc['db_affected_rows'] (connection)
- Will return exact same results as mysql_num_fields.
Uhh If you excuse the report:
I'm afraid that's a mistake in the first post. Hope you can fix it (it should say "...same exact results as mysql_affected_rows").
:)
thank you. i was so looking for this to convert mods 1.1.x to rc2.
BlueDevil: See the giant sticky in this board entitled "Important Customization Information" ;)
burda türk biri varmı
Quote from: alevsu on March 06, 2010, 10:29:52 AM
burda türk biri varmı
alevsu, please use the Turkish (http://www.simplemachines.org/community/index.php?board=76.0) board.
Need a little advice with adding indexes to new tables.
I am trying to convert this mysql:
$name = db_name($name);
return "CREATE TABLE IF NOT EXISTS {db_prefix}`$name` (
`id` INT(11) NOT NULL auto_increment,
`ip` TEXT NOT NULL,
`user_agent` TEXT NOT NULL,
`request_entity` TEXT NOT NULL,
`key` TEXT NOT NULL,
INDEX (`ip`(15)),
INDEX (`user_agent`(10)),
PRIMARY KEY (`id`) );"; // TODO: INDEX might need tuning
To SMF 2.0 RC2 format. So far I have:
$smcFunc['db_create_table'](
'{db_prefix}' . $name,
array(
array('name' => 'id', 'type' => 'INT(11)', 'null' => 'NOT NULL', 'auto' => 'auto_increment'),
array('name' => 'ip', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'user_agent', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'request_entity', 'type' => 'TEXT', 'null' => 'NOT NULL'),
array('name' => 'key', 'type' => 'TEXT', 'null' => 'NOT NULL'),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
),
'ignore'
);
$created_tables[] = '{db_prefix}' . $name;
I am not sure if the variable $name is correct and how to add
INDEX (`ip`(15)),
INDEX (`user_agent`(10)),
.
I do not see a way to enter the name and size of the index for 'db_create_table'?
I'd say your fundamental datastructure is hugely messed up if you require 64K characters on a string of 16 characters, for one. I'd strongly encourage you to make the IP column varchar(16) with an index of 7 characters, and user agent varchar 255, index(10)
Seriously, though you already have one index - the primary one. It's just another array in there.
The data structure is in a program I am looking at bridging with SMF. I really have no intension getting into the other programs code since it is rather extensive. All I really need to do is set up access to SMF database in a DB access php file that will replace the normal mysql DB access file. I can change the structure some. This is the only function slowing me down. Here is what I am looking at after you post (this is complete - the above post was not complete):
$name = $db_prefix .'module_name'
$request = $smcFunc['db_create_table']($name,
array(
array('name' => 'id', 'type' => 'INT(11)', 'null' => 'NOT NULL', 'auto' => 'auto_increment'),
array('name' => 'ip', 'type' => 'varchar(16)', 'null' => 'NOT NULL'),
array('name' => 'date', 'type' => 'DATETIME', 'null' => 'NOT NULL', 'default' => '0000-00-00 00:00:00'),
array('name' => 'request_method', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'request_uri', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'server_protocol', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'http_headers', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'user_agent', 'type' => 'varchar 255', 'null' => 'NOT NULL'),
array('name' => 'request_entity', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
array('name' => 'key', 'type' => 'varchar(255)', 'null' => 'NOT NULL'),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
array(
'type' => 'indexes',
'ip' => array(array('ip', 7)),
'user_agent' => array(array('user_agent', 10))
)
),
'ignore'
);
It is my best guess at this moment. I am not an expert with no formal training on this stuff so please let me know if you think it will work or what changes I need to make to work with SMF 2?
You'd be best looking at some other examples, even in the OP, since there's a whole lot of stuff missing. Let me just completely rewrite it for you. Without wishing to be rude, when the example for an index states it has a 'type' and a 'columns', it is a fair bet to say that any others you make will likely have the same...
$name = $db_prefix .'module_name'
$request = $smcFunc['db_create_table']($name,
array(
array(
'name' => 'id',
'type' => 'int',
'size' => 10,
'null' => false,
'unsigned' => true,
'auto' => true
),
array(
'name' => 'ip',
'type' => 'varchar',
'size' => 16,
'null' => false,
'default' => '', // and yes before anyone whines, this won't work in non MySQL, but that's an SMF bug
),
array(
'name' => 'date',
'type' => 'datetime',
'null' => false,
'default' => '0000-00-00 00:00:00', // without going into the belly of Subs-Db-Mysql.php I can't remember if SMF supports this type, assume it does for now
),
array(
'name' => 'request_method',
'type' => 'varchar',
'size' => 4, // since you're only ever handling GET or POST, save yourself the effort
'null' => false,
'default' => '',
),
array(
'name' => 'request_uri',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'server_protocol',
'type' => 'varchar',
'size' => 15, // 5 is probably enough here, for http and https but you never know
'null' => false,
'default' => '',
),
array(
'name' => 'http_headers',
'type' => 'varchar',
'size' => 255, // depending on what you're doing, this may need to be text
'null' => false,
'default' => '',
),
array(
'name' => 'user_agent',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'request_entity',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
array(
'name' => 'key',
'type' => 'varchar',
'size' => 255,
'null' => false,
'default' => '',
),
),
array(
array(
'type' => 'primary',
'columns' => array('id')
)
array(
'type' => 'index',
'columns' => array('ip'),
'size' => 7,
),
array(
'type' => 'index',
'columns' => array('user_agent'),
'size' => 10,
)
),
'ignore'
);
I haven't actually specified a size on indexes during installation in a mod before, simply because I've never indexed anything that's actually text before in this manner, usually because it's never very pretty. Better solution would be to do some kind of processing in your application and convert it to an integer for which indexing is a LOT faster to save, to load, to reindex and to lookup/search against later.
WOW, I was off base. Thank you.
:)
In $smcFunc['db_insert']:
QuoteColumns: An array ( column_name => input_type) set that holds all column names that will be changed and their expected input type.
How would I specify an input type of boolean? Is there any reference for input types I can look at?
Hmm, why can't I run this script in a stand-alone file in the same folder as SSI.php?
<?php
if (!defined('SMF') && file_exists(dirname(__FILE__) . '/SSI.php'))
{
require_once(dirname(__FILE__) . '/SSI.php');
}
elseif (!defined('SMF'))
die('Error!');
global $smcFunc, $db_prefix;
echo 'Installing into '. $db_prefix;
if($smcFunc)
$smcFunc['db_create_table'](
$db_prefix . 'collection',
array(
array('name' => 'item', 'type' => 'INT', 'size' => 11, 'default' => ''),
array('name' => 'name', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'pop_name', 'type' => 'VARCHAR', 'size' => 30, 'default' => ''),
array('name' => 'category', 'type' => 'INT', 'size' => 11, 'default' => ''),
array('name' => 'thumb', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'official_site', 'type' => 'TEXT', 'default' => ''),
array('name' => 'other_site', 'type' => 'TEXT', 'default' => ''),
),
'ignore'
);
?>
I keep getting the error: Fatal error: Function name must be a string in xxx.
u need db_extend('packages'); just like all the mods do
But I'm not installing a package.
yes but the db_create_table function isn't available unless you call the packages extension
funnily enough like the first post says
QuoteDatabase 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_add_column']
$smcFunc['db_add_index']
$smcFunc['db_calculate_type']
$smcFunc['db_change_column']
$smcFunc['db_create_table']
$smcFunc['db_drop_table']
$smcFunc['db_table_structure']
$smcFunc['db_list_columns']
$smcFunc['db_list_indexes']
$smcFunc['db_remove_column']
$smcFunc['db_remove_index']
Hmm, thx. My ignore doesn't seem to work though.
<?php
$smcFunc['db_create_table'](
$db_prefix . 'collection',
array(
array('name' => 'item', 'type' => 'INT', 'size' => 11, 'default' => 0, 'auto' => 'true'),
array('name' => 'name', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'pop_name', 'type' => 'VARCHAR', 'size' => 30, 'default' => ''),
array('name' => 'category', 'type' => 'INT', 'size' => 11, 'default' => 0),
array('name' => 'thumb', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'official_site', 'type' => 'TEXT', 'default' => ''),
array('name' => 'other_site', 'type' => 'TEXT', 'default' => ''),
),
array(
array(
'type' => 'primary',
'columns' => array('item')
)
),
'ignore'
);
?>
It's probably placed wrong, but I compared with other $smcFunc['db_create_table'] examples, and they look the same.
it doesnt matter, please read the posted quote again
until you run db_extend('packages'), $smcFunc['db_create_table'] is NOT DECLARED. so $smcFunc['db_create_table'] doesn't exist so you get the error you did.
I do, just didn't put it in the example.
so what is it doing versus what you think it's supposed to be doing?
It creates the table as expected, but gives an error when run twice. It should just use the 'if not exists' with the 'ignore' option.
why not use 'update' ?
Ignore should work. Try adding an empty array before ignore.
),
array(),
'ignore'
);
I already tried that, and it didn't work.
The only other difference I can see to one which works is that
$db_prefix . 'collection',
should be:
'{db_prefix}collection',
But I don't think that is it since the table does get created. However... :)
What error do you get on re-running it?
this should work
<?php
$smcFunc['db_create_table']('{db_prefix}collection',
array(
array('name' => 'item', 'type' => 'INT', 'size' => 11, 'default' => 0, 'auto' => 'true'),
array('name' => 'name', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'pop_name', 'type' => 'VARCHAR', 'size' => 30, 'default' => ''),
array('name' => 'category', 'type' => 'INT', 'size' => 11, 'default' => 0),
array('name' => 'thumb', 'type' => 'VARCHAR', 'size' => 60, 'default' => ''),
array('name' => 'official_site', 'type' => 'TEXT', 'default' => ''),
array('name' => 'other_site', 'type' => 'TEXT', 'default' => ''),
),
array(
array(
'type' => 'primary',
'columns' => array('item')
),
array(),
'ignore');
?>
hi ;
db_query ( "CREATE TABLE IF NOT EXISTS `{$db_prefix}rivals_clanladders` (
`clan_id` int(1) NOT NULL,
`ladder_id` int(1) NOT NULL
)",__FILE__,__LINE__ );
Would you help translate for Smf 2x ?
@Alpay, use this:
if (!isset($smcFunc['db_create_table'])
db_extend('packages');
$smcFunc['db_create_table'] ('{db_prefix}rivals_clanladders',
array(
array(
'name' => 'clan_id',
'type' => 'tinyint',
'size' => '1',
),
array(
'name' => 'ladder_id',
'type' => 'tinyint',
'size' => '1',
),
),
array()
);
@grafitus thank you , i like it :)
Can you be more help there for translate ?
I'll re-PM you in next days, about your huge database. :P