Hey guys im kinda new in mysql,and I need to use lua in order to connect to a database and so somethings, Currently I'm using mysqloo module which can be found here: [url]http://facepunch.com/showthread.php?t=1220537[/url]
I need to make a database which will contain a user and his steamid, But it should check if it first exists, and then if it doesn't insert, and if it does, shouldn't insert.
My question is how should i do that lua side with mysql..
Thanks for all helpers :P..
Hey mate,
check out this addon, it could be modded to alter a different table other than pointshop data, atleast you wont be starting from scratch.
"https://github.com/adamdburton/pointshop-mysql"
Use the updated module: [url]http://facepunch.com/showthread.php?t=1357773[/url]
ok.
By the way, I got 1 question regarding the system.
If i try and find a value with a query, and I cant, will it return false?
First you should [B]SELECT * FROM accounts_table WHERE steamid=SQLStr( Player:SteamID( ) ) LIMIT 1[/B] -- It doesn't matter what you select, * for "all", or individual columns, we just use it to check if it exists.
then, in the resulting output; if the amount of rows is > 0 then the account exists; either do nothing or increment times connected, or whatever.
else, this is where the account doesn't exist. There are several accepted ways in the SQL that you can write an [B]INSERT INTO accounts_table ( name, steamid ), VALUES( SQLStr( Player:Nick( ) ), SQLStr( Player:SteamID( ) ) )[/B] which is the format [I]INSERT INTO <table> ( <columns_comma_delimited> ), VALUES( < values_in_order_of_columns_listed_comma_delimited > )[/I]
Or, you can use the SET method
[B]INSERT INTO accounts_table SET name=SQLStr( Player:Nick( ) ), steamid=SQLStr( Player:SteamID( ) )[/B] which is the format [I]INSERT INTO <table> SET <column_name>=<column_value>[,<column_name>=<column_value>]?*[/I] - the square brackets denotes the pattern to repeat, the ? means it is optional and * means repeat 0 to many times. In the example I gave, I showed it using 2 insert values.
Some prefer one method over the other.
Slightly revised as HTML: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]
-- marks SQL or pseudo code you'd implement you'd run, // marks comments.
Additional info: NEVER trust client-information. NEVER. Always ESCAPE user-input and sanitize input. SQLStr will escape magic characters and attempts to break-out in order to execute malicious code, the second argument ( omitted in example ) will determine whether or not ""s are put around the value.
[QUOTE=Acecool;44675923]First you should [B]SELECT * FROM accounts_table WHERE steamid=SQLStr( Player:SteamID( ) ) LIMIT 1[/B] -- It doesn't matter what you select, * for "all", or individual columns, we just use it to check if it exists.
then, in the resulting output; if the amount of rows is > 0 then the account exists; either do nothing or increment times connected, or whatever.
else, this is where the account doesn't exist. There are several accepted ways in the SQL that you can write an [B]INSERT INTO accounts_table ( name, steamid ), VALUES( SQLStr( Player:Nick( ) ), SQLStr( Player:SteamID( ) ) )[/B] which is the format [I]INSERT INTO <table> ( <columns_comma_delimited> ), VALUES( < values_in_order_of_columns_listed_comma_delimited > )[/I]
Or, you can use the SET method
[B]INSERT INTO accounts_table SET name=SQLStr( Player:Nick( ) ), steamid=SQLStr( Player:SteamID( ) )[/B] which is the format [I]INSERT INTO <table> SET <column_name>=<column_value>[,<column_name>=<column_value>]?*[/I] - the square brackets denotes the pattern to repeat, the ? means it is optional and * means repeat 0 to many times. In the example I gave, I showed it using 2 insert values.
Some prefer one method over the other.
Slightly revised as HTML: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]
-- marks SQL or pseudo code you'd implement you'd run, // marks comments.
Additional info: NEVER trust client-information. NEVER. Always ESCAPE user-input and sanitize input. SQLStr will escape magic characters and attempts to break-out in order to execute malicious code, the second argument ( omitted in example ) will determine whether or not ""s are put around the value.[/QUOTE]
first of all thank you very much!
Second of all, I need to grab a value called points from that table, and add to it, its an integer.
How would i do that with Insert and Select?
And if i'll use the #rows, will it automatically work? or will i need a returning function?
In place of * you'd use column1, column_name2, steamid, etc...
If you leave it as *, it'll be returned one of two ways. Either as a numerical key value, or string key value.
so, using *: rows[ 1 ] returns the first result of the query. If you have multiple, 1 would be i in for i = 0, #rows - 1, or for i = 1, #rows depending how your particular module does it.
MySQLOO and TMySQL should both use starting index at 1, going forward. If you are NOT certain, use for k, v in pairs( rows ) do...
Now, within the loop, the v is what rows[ 1 ], rows[ 2 ], etc would contain. As described above, it is a table containing each element you selected in the query. In MySQLOO, it uses strings, so you'd use v.column1, v.column_name2, v.steamid, etc... to retrieve the results. You can also use v[ "column1" ], etc for the same thing...
TMySQL by default returns each column as a number index unless you specify in the query ( the argument after the on success callback function ) that it should return strings...
so using the same loops above, instead of v.column1, etc... it'll be v[ 1 ], v[ 2 ], v[ 3 ], etc.. based on the order you selected. If it's *, then it is order defined by the table structure.
Hopefully that helps.
Sorry, you need to Log In to post a reply to this thread.
