ExecuteSQLSelect: Difference between revisions

Revision as of 15:23, 23 September 2006

This function retrieves rows from a table in the database, if they exist. If you pass the table name, along with the columns you want to retrieve (and any conditions for the row) this function will return a table containing the corresponding values.

The SQLite database contains globally stored data and can be used by scripts to store and retrieve data in a structured manner.

The executed SQL query is the following:

[sql]SELECT <fields> FROM <tables> WHERE <conditions> LIMIT <limit>

Syntax

table executeSQLSelect ( string table, string fields, [ string conditions ], [ int limit ] )

Required Arguments

table: The table you want to query. No spaces allowed.
fields: The fields you want to query. No spaced allowed. Multiple fields should be separated by a comma (,). Wildcard (*) allowed to query all fields in the table. If you use wildcards, please pay attention to the order in which you'll have to retrieve your values from the table.

Optional Arguments

conditions: The conditions for the query. Multiple conditions should be separated by logical operators (AND, OR).
limit: Maximum amount of rows to return.

Returns

On success:

table: The 2-dimensional table where the results are stored in: table [row_index] [column_index].

On failure:

boolean: False, when no rows are found or an error occured.

Example

function onMapLoad ()
	executeSQLCreateTable ( "players", "clothes_head_texture TEXT, clothes_head_model TEXT, player TEXT" )	-- create our table, if it doesn't already exist
end

function onPlayerSpawn ( spawnpoint, team )	
	sourcename = getClientName ( source )	-- get the player's name
	
	-- try to retrieve the player data from the db
	result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
	if ( result == false ) then	-- see if any data was found at all
		outputChatBox ( "This is your first time here! Welcome " .. sourcename .. "!", source )
		executeSQLInsert ( "players", "'none', 'none', '" .. sourcename .. "'" )
	else
		outputChatBox ( "Welcome back " .. sourcename .. "!", source )
		executeSQLUpdate ( "players", "clothes_head_texture = 'hairgreen', clothes_head_model = 'somehead'",
		"player = '" .. sourcename .. "'" )
	end	
	
	-- get the clothes data for the player
    result = executeSQLSelect ( "players", "clothes_head_texture, clothes_head_model", "player = '" .. sourcename .. "'" )
	outputChatBox ( "Your head texture is " .. result[1][1] )
	outputChatBox ( "Your head model is " .. result[1][2] )	
end

@@ Line 1: / Line 1: @@
 __NOTOC__
-This function retrieves one or more rows from the registry. Since the registry is powered by a SQLite database, this function automatically executes a ''SELECT'' query and returns the result as an object that can only be read by the [[GetRegistryResultValue]] and [[GetRegistryResultColumn]] functions.
+This function retrieves rows from a table in the database, if they exist. If you pass the table name, along with the columns you want to retrieve (and any conditions for the row) this function will return a table containing the corresponding values.
-The registry is stored globally and can be read from and written to by all scripts in sessions. This is useful if you want to store data for your script.
+The SQLite database contains globally stored data and can be used by scripts to store and retrieve data in a structured manner.
-The actual SQL query that is executed will be the following:
+The executed SQL query is the following:
 <syntaxhighlight lang="lua">[sql]SELECT <fields> FROM <tables> WHERE <conditions> LIMIT <limit></syntaxhighlight>
@@ Line 10: / Line 10: @@
 ==Syntax==
 <syntaxhighlight lang="lua">
-result getRegistryValue ( string tables, string fields, [ string conditions ], [ int limit ] )
+table executeSQLSelect ( string table, string fields, [ string conditions ], [ int limit ] )
 </syntaxhighlight>
 ===Required Arguments===
-*'''tables:''' The tables you want to query. No spaces allowed. Multiple tables should be separated by a comma (,).
+*'''table:''' The table you want to query. No spaces allowed.
-*'''fields:''' The fields you want to query. No spaced allowed. Wildcard (*) allowed to query all fields in the table. Multiple fields should be separated by a comma (,).
+*'''fields:''' The fields you want to query. No spaced allowed. Multiple fields should be separated by a comma (,). Wildcard (*) allowed to query all fields in the table. If you use wildcards, please pay attention to the order in which you'll have to retrieve your values from the table.
 ===Optional Arguments===
@@ Line 22: / Line 22: @@
 ===Returns===
-*'''r:''' The result-object where the results are stored in.
+On success:
+*'''table:''' The 2-dimensional table where the results are stored in: table [row_index] [column_index].
+On failure:
+*'''boolean:''' False, when no rows are found or an error occured.
+==Example==
+<syntaxhighlight lang="lua">
+function onMapLoad ()
+	executeSQLCreateTable ( "players", "clothes_head_texture TEXT, clothes_head_model TEXT, player TEXT" )	-- create our table, if it doesn't already exist
+end
+function onPlayerSpawn ( spawnpoint, team )
+	sourcename = getClientName ( source )	-- get the player's name
+	-- try to retrieve the player data from the db
+	result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
+	if ( result == false ) then	-- see if any data was found at all
+		outputChatBox ( "This is your first time here! Welcome " .. sourcename .. "!", source )
+		executeSQLInsert ( "players", "'none', 'none', '" .. sourcename .. "'" )
+	else
+		outputChatBox ( "Welcome back " .. sourcename .. "!", source )
+		executeSQLUpdate ( "players", "clothes_head_texture = 'hairgreen', clothes_head_model = 'somehead'",
+		"player = '" .. sourcename .. "'" )
+	end
+	-- get the clothes data for the player
+    result = executeSQLSelect ( "players", "clothes_head_texture, clothes_head_model", "player = '" .. sourcename .. "'" )
+	outputChatBox ( "Your head texture is " .. result[1][1] )
+	outputChatBox ( "Your head model is " .. result[1][2] )
+end
+</syntaxhighlight>
 ==See Also==
 {{Registry_functions}}