MYSQL Help

[tzahush]

Hey guys, I started using mysql, and im not that good with it..
I want to make a mysql system which all it does is saves the user groups of the players.

I am using the mysqloo of course, and i got a few questions.

After i made this query:

Database:query("SELECT * FROM `users` WHERE `steamid`='" .. id .. "'")

What do i do next? i mean, i want to insert a the name of the usergroup, to a table where the steam id is found.
not sure how to do that :P.

[Acecool]

Basic database query formats: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]

Hopefully that helps. Anything with [ ] around it is how the pattern continues.

[tzahush]

[QUOTE=Acecool;45436313]Basic database query formats: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]

Hopefully that helps. Anything with [ ] around it is how the pattern continues.[/QUOTE]

what is the " if ( #rows > 0 ) then"
And how do i actully do it, i am guessing that #rows wont do anything

[Acecool]

Basically the number of returned elements; if there is returned column data, then proceed.

This is an example of how to convert sv.db playerpdata to MySQL insert queries: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua.html[/url]

So, for each result do something. In that example I use i, and _row for the specific row the iteration is on.

[tzahush]

[QUOTE=Acecool;45436423]Basically the number of returned elements; if there is returned column data, then proceed.

This is an example of how to convert sv.db playerpdata to MySQL insert queries: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/converting_sv_db_to_mysql.lua.html[/url]

So, for each result do something. In that example I use i, and _row for the specific row the iteration is on.[/QUOTE]

So how would i do it with MYSQLOO, cuz i didn't quite understand how to do it.

[Acecool]

Same way.. Depending on if you use tMySQL or OO, you'd either set up onSuccess, onFailure functions. The OnSuccess should have an argument called rows, just do a for k, v in pairs( rows ) do, and grab the data.

Here's what I do with tMySQL:

function DB.UpdateConnects( Player )
	local _query = DB.SelectQuery( DATABASE_USER_ACCOUNTS_TABLE, { "user_connects", "uniqueid" }, DB.GenerateString( true, "steamid", Player:SteamID( ) ), 1 )
	DATABASE:Query( _query, function( row )
		local _uidstring = DB.GenerateString( false, "uniqueid", Player:UniqueID( ) );

		if ( #row > 0 ) then
			local _cons = DB.GetValue( row, 1, 1, "user_connects" );
			DATABASE:Query( "UPDATE " .. DATABASE_USER_ACCOUNTS_TABLE .. " SET lastlogin='" .. DB.Escape( os.time( ), true ) .. "', user_connects = '" .. DB.Escape( _cons + 1, true ) .. "'" .. _uidstring .. " WHERE steamid='" .. DB.Escape( Player:SteamID( ), true ) .. "' LIMIT 1" )
		else
			DATABASE:Query( "INSERT INTO " .. DATABASE_USER_ACCOUNTS_TABLE .. " SET steamid='" .. DB.Escape( Player:SteamID( ), true ) .. "', username='" .. DB.Escape( Player:Nick( ), true ) .. "', accesslevel='1', lastlogin='" .. DB.Escape( os.time( ), true ) .. "', user_connects='1'" .. _uidstring )
		end
	end, QUERY_FLAG_ASSOC )
end

DB.SelectQuery is a custom function which builds a query string; DB.GenerateString creates the string such as uniqueid='" .. SQLStr( uniqueid ) .. "' for use in selecting, inserting, etc.

The above does DatabaseObject:Query( query_string, onsuccess, return_type )

return_type is whether you want numbered or named columns, onsuccess with the rows argument, etc. It only checks to see if an account exists, if it does it increments the connects number, if it doesn't it inserts an entry into the accounts table.

MySQLOO is essentially the same, but it takes a table ( if I recall correctly ), so DatabaseObject:Query( { onSuccess = function( rows ) ... end; onFailure = function( error ) ... end } );

They aren't too dissimilar but they can be. Here are examples on how to establish a connection with either ( and shows how to set up a query for MySQLOO )

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html[/url]

Hopefully this helps.