ExecuteSQLSelect: Difference between revisions

From Multi Theft Auto: Wiki
Jump to navigation Jump to search
No edit summary
m (Changed "DeprecatedWithAlt" template to "Deprecated")
 
(22 intermediate revisions by 10 users not shown)
Line 1: Line 1:
__NOTOC__  
__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 [[GetRegistryRowValue]] and [[GetRegistryRowColumn]] functions.
{{Server function}}
{{Deprecated|executeSQLQuery|See the examples at executeSQLQuery for equivalent SELECT usage.}}


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.
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 actual SQL query that is executed will be the following:
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:


<syntaxhighlight lang="lua">[sql]SELECT <fields> FROM <tables> WHERE <conditions> LIMIT <limit></syntaxhighlight>
<syntaxhighlight lang="lua">[sql]SELECT <fields> FROM <tables> WHERE <conditions> LIMIT <limit></syntaxhighlight>
Line 10: Line 13:
==Syntax==  
==Syntax==  
<syntaxhighlight lang="lua">
<syntaxhighlight lang="lua">
int getRegistryValue ( object r, string tables, string fields, [ string conditions ], [ int limit ] )
table executeSQLSelect ( string tableName, string fields, [ string conditions, int limit ] )
</syntaxhighlight>  
</syntaxhighlight>


===Required Arguments===  
===Required Arguments===
*'''r:''' The result-object where the results are stored in.
*'''tableName:''' The table you want to query. No spaces allowed.
*'''tables:''' The tables you want to query. No spaces allowed. Multiple tables should be separated by a comma (,).
*'''fields:''' The fields you want to query. No spaces 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.
*'''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 (,).


===Optional Arguments===
===Optional Arguments===
Line 23: Line 25:


===Returns===
===Returns===
Returns the amount of retrieved rows.
Returns a 2-dimensional table where the results are stored as table [row_index] [column_name]. Please note that table may be empty.


==Example==  
If invalid arguments were passed, returns ''false''.
This example keeps track of the largest number of players playing at once on the server and announces each time the record has been broken.
 
==Example==
This example creates a SQL table when a map loads, and stores info about a player to that database when he spawns.
<syntaxhighlight lang="lua">
<syntaxhighlight lang="lua">
-- Trigger our function every time a player joins
function onMapLoad ()
addEventHandler ( "onPlayerJoin", getRootElement(), "maxPlayerCounter" )
-- create our table, if it doesn't already exist
function maxPlayerCounter ( )
executeSQLCreateTable ( "players", "clothes_head_texture TEXT, clothes_head_model TEXT, player TEXT" )
    getRegistryValue ( res, "myscript_stats", "mostplayers", "", 1 ) -- get the mostPlayers result from our global stats row
end
    mostPlayers = getRegistryRowValue ( res, 0 ) -- get the actual mostPlayers value from the result-object
addEventHandler ( "onGamemodeMapStart", getRootElement(), onMapLoad )
    playerCount = getPlayerCount() -- get the number of players now
 
    if ( mostPlayers == false or playerCount > mostPlayers ) -- see if there was a previous record and if there was, see if we've broken it
function addInfoToSQL( theSpawnpoint, theTeam )
        outputChatBox ( "New player count record: " .. playerCount .. " players!" ) -- display a message in the chat box
sourcename = getPlayerName ( source ) -- get the player's name
        setRegistryValue ( "myscript_stats", "mostplayers = " .. playerCount ) -- store our new record
    end
-- try to retrieve the player data from the db
result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
if ( type( result ) == "table" and #result == 0 ) or not result 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].clothes_head_texture )
outputChatBox ( "Your head model is " .. result[1]['clothes_head_model'] )
end
end
addEventHandler ( "onPlayerSpawn", getRootElement(), addInfoToSQL )
</syntaxhighlight>
</syntaxhighlight>


==See Also==
==See Also==
{{Registry_functions}}
{{Registry_functions}}
[[ru:executeSQLSelect]]

Latest revision as of 16:20, 13 February 2015

Emblem-important.png This function is deprecated. This means that its use is discouraged and that it might not exist in future versions.

Please use executeSQLQuery instead. See the examples at executeSQLQuery for equivalent SELECT usage.


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 tableName, string fields, [ string conditions, int limit ] )

Required Arguments

  • tableName: The table you want to query. No spaces allowed.
  • fields: The fields you want to query. No spaces 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

Returns a 2-dimensional table where the results are stored as table [row_index] [column_name]. Please note that table may be empty.

If invalid arguments were passed, returns false.

Example

This example creates a SQL table when a map loads, and stores info about a player to that database when he spawns.

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

function addInfoToSQL( theSpawnpoint, theTeam )	
	sourcename = getPlayerName ( source )	-- get the player's name
	
	-- try to retrieve the player data from the db
	result = executeSQLSelect ( "players", "player", "player = '" .. sourcename .. "'" )
	if ( type( result ) == "table" and #result == 0 ) or not result 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].clothes_head_texture )
	outputChatBox ( "Your head model is " .. result[1]['clothes_head_model'] )	
end
addEventHandler ( "onPlayerSpawn", getRootElement(), addInfoToSQL )

See Also