Difference between revisions of "Db query" From Online Manual

Jump to: navigation, search
(Created page with "{{smcfunc |function=db_query |usage=$smcFunc['db_query'] (identifier, query, values, connection) |description=Works Similar to how db_query worked in 1.x versions. |parameters={{...")
 
 
(12 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
{{smcfunc
 
{{smcfunc
 
|function=db_query
 
|function=db_query
 +
|order_position=1
 
|usage=$smcFunc['db_query'] (identifier, query, values, connection)
 
|usage=$smcFunc['db_query'] (identifier, query, values, connection)
|description=Works Similar to how db_query worked in 1.x versions.
+
|description=Executes a database query. Works similar to how db_query worked in SMF 1.x.
 
|parameters={{parmdesc
 
|parameters={{parmdesc
 
   |identifier|d1=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.{{parmdesc
 
   |identifier|d1=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.{{parmdesc
 
     |substring|d1=for the SQLite database, '''SUBSTRING''' will be replaced by '''SUBSTR'''
 
     |substring|d1=for the SQLite database, '''SUBSTRING''' will be replaced by '''SUBSTR'''
     |See the function smf_db_query|d2= in Subs-DB-{dbms}.php for the full list of replacements.
+
     |See the function smf_db_query|d2=in Subs-Db-<abbr title="mysql, postgresql, sqlite">{dbms}</abbr>.php for the full list of replacements.
 
   }}
 
   }}
   |values|d2=is an array of values you are intending to use in the query.
+
  |query|d2=the query to execute.
 +
   |values|d3=is an array of values you are intending to use in the query.
 +
  |connection|d4=the database connection.
 
}}
 
}}
 
|return=a query resource or '''false''' on error.
 
|return=a query resource or '''false''' on error.
|example=<nowiki></nowiki> $result = $smcFunc['db_query'](''', '
+
|example=<nowiki>$result = $smcFunc['db_query']('', '
SELECT poster_time
+
SELECT poster_time
FROM {db_prefix}messages
+
FROM {db_prefix}messages
WHERE id_msg = {int:id_msg}
+
WHERE id_msg = {int:id_msg}
LIMIT 1',
+
LIMIT 1',
array(
+
array(
'id_msg' => $user_settings['id_msg_last_visit'],
+
'id_msg' => $user_settings['id_msg_last_visit'],
)
+
)
);
+
);</nowiki>
 
|Example with identifier 'substring'|d1=
 
|Example with identifier 'substring'|d1=
 
{{code|1=<nowiki>$result = $smcFunc['db_query']('substring', '
 
{{code|1=<nowiki>$result = $smcFunc['db_query']('substring', '
Line 31: Line 34:
 
);</nowiki>}}
 
);</nowiki>}}
 
}}
 
}}
 
+
 
<noinclude>[[category:smcfunc]]
+
<noinclude>
[[Category:2.0]]
+
[[Category:Database Functions]]</noinclude>
[[Category:Customizing SMF]]
 
[[Category:Developing SMF]]</noinclude>
 

Latest revision as of 11:53, 23 May 2013

Usage:

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


Description: Executes a database query. Works similar to how db_query worked in SMF 1.x.

Parameters:

  • 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.
  • query the query to execute.
  • values is an array of values you are intending to use in the query.
  • connection the database connection.

Return: a query resource or false on error.

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'],
		)
	);