MySql Needing help understanding!

[xrayhunter1]

Hello coder's im trying to learn a bit of MySQL data-basing.

First Question: Is the MySQL Database.lua supposed to be in init kind of files or client side?
Second Question: Where is some good tutorials that i can learn besides [url]http://facepunch.com/showthread.php?t=1220537&highlight=sqloo[/url] ?

here a bit code in put in my database.lua

require( "mysqloo" )

local queue = {} 
local db = mysqloo.connect( "123.456.789.0", "drake", "abc123", "database_name", 3306 )
 
function db:onConnected()

	for k, v in pairs( queue ) do

		query( v[ 1 ], v[ 2 ] )

	end
	queue = {}

end
 
function db:onConnectionFailed( err )
 
    print( "Connection to database failed!" )
    print( "Error:", err )
 
end
 
db:connect()

function query( sql, callback, ply )

local steamID = ply:SteamID()
local Cname = ply:Nick()
local tokens = ply:GetNWInt("tokens")
local Pname = ply:Name()
local Whitelist = ply:Team()

	local q = db:query( sql )
	function q:onSuccess( data )

		callback( data )
			sql.Query("CREATE INTO Hl2rp VALUES(" .. steamID ..", ".. Cname ..", ".. tokens ..", ".. Pname ..", ".. Whitelist ..")")
	end

	function q:onError( err )

		if db:status() == mysqloo.DATABASE_NOT_CONNECTED then

			table.insert( queue, { sql, callback } )
			db:connect()
			return
		end

		print( "Query Errored, error:", err, " sql: ", sql )

	end

	q:start()

end

I was trying to make the Steam id and a cname(Character Name) then a tokens then a pname(Which is your Steam Name) and the Whitelist(Cp/citizen/whatever team)

sql.Query("CREATE INTO Hl2rp VALUES(" .. steamID ..", ".. Cname ..", ".. tokens ..", ".. Pname ..", ".. Whitelist ..")")

[Acecool]

I'm working on creating database tutorials:

[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]
[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html[/url]

I'm creating new tutorials when I can; additionally the MySQL DB Lua should be 100% SERVERside.

[xrayhunter1]

Ok Awesome can you also probably one day make a youtube video about it explaining all of it
Thanks!

[Acecool]

Sure; once I get a new microphone I'll do it.

[xrayhunter1]

First you need to fix your tutorial yours was

//
// Connecting to a MySQL database using TMySQL, with the ability to use MySQLOO as backup - Josh 'Acecool' Moser
//
//
// MySQLOO: http://facepunch.com/showthread.php?t=1357773
// MySQLOO: http://facepunch.com/showthread.php?t=1220537

//
// Globals...
//
DATABASE = nil;
ERR = nil;

DATABASE_HOST 			= "127.0.0.1";			// IP / Hostname
DATABASE_USER 			= "root";				// Username
DATABASE_PASS 			= "-snip-";				// Password
DATABASE_PORT 			= 3306;					// 3306 is default port
DATABASE_NAME 			= "my_database_name";	// DB you want to connect to

hook.Add( "Initialize", "Database:Init", function( )
	// Include first-try...
	require( "tmysql" );

	//
	// If TMySQL was successfully included
	//
	if ( tmysql )
		// Start the connection process
		DATABASE, ERR = tmysql.initialize( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME, DATABASE_PORT, 2, 2 );
	else
		ERR = "Unable to include TMySQL!\n";

		// Include second attempt
		require( "mysqloo" );

		//
		// If MySQLOO was successfully included
		//
		if ( mysqloo )
			// Start the connection process
			DATABASE = mysqloo.connect( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME, DATABASE_PORT );


			// Callback for if it connects properly
			function DATABASE:onConnected( )
				ERR = nil;
			end


			// Callback for if it fails connection
			function DATABASE:onConnectionFailed( err )
				ERR = err;
			end


			// Connect
			DATABASE:connect( );


			// Backwards Compatibility with TMySQL calls:
			DATABASE.Query = DATABASE.query;

			//... May need more...

		else
			ERR = ERR .. "Unable to include MySQLOO!\n";
		end	
	end

	// Call hooks...
	if ( DATABASE ) then
		hook.Call( "DatabaseConnected", GAMEMODE, DATABASE );
	else
		hook.Call( "DatabaseFailed", GAMEMODE, DATABASE, ERR );
	end
end );


//
// Hook for when db connection succeeds
//
hook.Add( "DatabaseConnected", "Database:Connected", function( )
	MsgC( Color( 0, 255, 0, 255 ), "[" .. GAMEMODE.Name .. "] Database connection " );
	MsgC( Color( 0, 255, 0, 255 ), "succeeded!\n" );
end );


//
// Hook for when db connection fails
//
hook.Add( "DatabaseFailed", "Database:Failed", function( _db, _err )
	MsgC( Color( 0, 255, 0, 255 ), "[" .. GAMEMODE.Name .. "] Database connection " );
	MsgC( Color( 255, 0, 0, 255 ), "failed!\nError: " .. _err .. "\n" );

	// Stop the server?
	MsgC( Color( 255, 0, 0, 255 ), "HALTING SERVER!\n" );
	RunConsoleCommand( "disconnect" );
end );

then mine was

//
// Connecting to a MySQL database using TMySQL, with the ability to use MySQLOO as backup
//
//
// MySQLOO: http://facepunch.com/showthread.php?t=1357773
// MySQLOO: http://facepunch.com/showthread.php?t=1220537

//
// Globals...
//
DATABASE = nil;
ERR = nil;

DATABASE_HOST 			= "localhost";			// IP / Hostname
DATABASE_USER 			= "root";				// Username
DATABASE_PASS 			= "";				// Password
DATABASE_PORT 			= 3306;					// 3306 is default port
DATABASE_NAME 			= "Hl2rp";	// DB you want to connect to

hook.Add( "Initialize", "Database:Init", function( )
		// Include second attempt
		require( "mysqloo" );

		//
		// If MySQLOO was successfully included
		//
		if ( mysqloo ) then
			// Start the connection process
			DATABASE = mysqloo.connect( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME, DATABASE_PORT );
			

			// Callback for if it connects properly
			function DATABASE:onConnected( )
			--DATABASE.query("CREATE TABLE IF NOT EXISTS Players(SteamID VARCHAR(25) NOT NULL PRIMARY KEY, Nick VARCHAR(40), Tokens INT(999999))")
				ERR = nil;
			end

				
			// Callback for if it fails connection
			function DATABASE:onConnectionFailed( err )
				ERR = err;
			end


			// Connect
			DATABASE:connect( );


			// Backwards Compatibility with TMySQL calls:
			DATABASE.Query = DATABASE.query;

			//... May need more...

		else
			ERR = ERR .. "Unable to include MySQLOO!\n";
		end	


	// Call hooks...
	if ( DATABASE ) then
		hook.Call( "DatabaseConnected", GAMEMODE, DATABASE );
	else
		hook.Call( "DatabaseFailed", GAMEMODE, DATABASE, ERR );
	end
end );


//
// Hook for when db connection succeeds
//
hook.Add( "DatabaseConnected", "Database:Connected", function( ply, steamID )
	print( Color( 0, 255, 0, 255 ), "[" .. GAMEMODE.Name .. "] Database connection " );
	print( Color( 0, 255, 0, 255 ), "succeeded!\n" );
	
end );


//
// Hook for when db connection fails
//
hook.Add( "DatabaseFailed", "Database:Failed", function( _db, _err )
	print( Color( 0, 255, 0, 255 ), "[" .. GAMEMODE.Name .. "] Database connection " );
	print( Color( 255, 0, 0, 255 ), "failed!\nError: " .. _err .. "\n" );

	// Stop the server?
	print( Color( 255, 0, 0, 255 ), "HALTING SERVER!\n" );
	RunConsoleCommand( "disconnect" );
end );

I was fixing the errors you forgot to put then's at the first two if's and stand of MsgC put print
it works better and doesn't cause errors.
also i got rid of

 // Include first-try...
	require( "tmysql" );

	//
	// If TMySQL was successfully included
	//
	if ( tmysql )
		// Start the connection process
		DATABASE, ERR = tmysql.initialize( DATABASE_HOST, DATABASE_USER, DATABASE_PASS, DATABASE_NAME, DATABASE_PORT, 2, 2 );
	else
		ERR = "Unable to include TMySQL!\n";

Now where do i put this line of code

sql.Query("CREATE INTO Hl2rp VALUES(" .. steamID ..", ".. Cname ..", ".. tokens ..", ".. Pname ..", ".. Whitelist ..")")

[Acecool]

That tutorial is for setting up a database system with a fallback, where'd my name go? I'll fix the errors now, thanks!

[editline]8th April 2014[/editline]

Oh, the sql.Query is for SQLite database, which is for sv.db or cl.db as seen garrysmod/lua/

garrysmod/lua/includes/extensions/player.lua
[lua]--[[---------------------------------------------------------
GetPData
- Saves persist data for this player
-----------------------------------------------------------]]
function meta:GetPData( name, default )

name = Format( "%s[%s]", self:UniqueID(), name )
local val = sql.QueryValue( "SELECT value FROM playerpdata WHERE infoid = " .. SQLStr(name) .. " LIMIT 1" )
if ( val == nil ) then return default end

return val

end

--[[---------------------------------------------------------
SetPData
- Set persistant data
-----------------------------------------------------------]]
function meta:SetPData( name, value )

name = Format( "%s[%s]", self:UniqueID(), name )
sql.Query( "REPLACE INTO playerpdata ( infoid, value ) VALUES ( "..SQLStr(name)..", "..SQLStr(value).." )" )

end

--[[---------------------------------------------------------
RemovePData
- Remove persistant data
-----------------------------------------------------------]]
function meta:RemovePData( name )

name = Format( "%s[%s]", self:UniqueID(), name )
sql.Query( "DELETE FROM playerpdata WHERE infoid = "..SQLStr(name) )

end[/lua]

And in: garrysmod/lua/includes/extensions/util.lua
[lua]--[[---------------------------------------------------------
Name: GetPData( steamid, name, default )
Desc: Gets the persistant data from a player by steamid
-----------------------------------------------------------]]
function util.GetPData( steamid, name, default )

name = Format( "%s[%s]", GetUniqueID( steamid ), name )
local val = sql.QueryValue( "SELECT value FROM playerpdata WHERE infoid = " .. SQLStr(name) .. " LIMIT 1" )
if ( val == nil ) then return default end

return val

end

--[[---------------------------------------------------------
Name: SetPData( steamid, name, value )
Desc: Sets the persistant data of a player by steamid
-----------------------------------------------------------]]
function util.SetPData( steamid, name, value )

name = Format( "%s[%s]", GetUniqueID( steamid ), name )
sql.Query( "REPLACE INTO playerpdata ( infoid, value ) VALUES ( "..SQLStr(name)..", "..SQLStr(value).." )" )

end

--[[---------------------------------------------------------
Name: RemovePData( steamid, name )
Desc: Removes the persistant data from a player by steamid
-----------------------------------------------------------]]
function util.RemovePData( steamid, name )

name = Format( "%s[%s]", GetUniqueID( steamid ), name )
sql.Query( "DELETE FROM playerpdata WHERE infoid = "..SQLStr(name) )

end[/lua]

Changing MsgC to print removes the colored output meaning you don't need to print the color...

For SQLite, you don't need MySQLOO or TMySQL

[xrayhunter1]

No problem.

Where would i put this in my gamemode?
and what do i put in the init/cl_init/shared.lua's

[Acecool]

You can use the PData as is in client or server files.

If you use it on client files it'll create a cl.db on the client pc. If you use it in server files it'll create sv.db on the server.

You use it like:

[lua]// Save
Player:SetPData( "inventory", util.TableToJSON( { "inventoryitems....." } ) )

// Retrieve
local _inv = Player:GetPData( "inventory", util.JSONToTable( "inventory", { } ) )[/lua]

It is a helper function to simplify using SQL. If you use the Player attached one ( meta... ) then it'll store to that user table with different types of data.

If you want to do your own queries, then use sql.Query; client or serverside depending whether or not you want the client to know about it.

General rule of thumb, SQL should be SERVER-side ONLY if it's important data. The only time you should ever even consider a client-side db, is if it only holds client configuration such as key-bindings, convar settings, selected theme, selected language, etc.. Nothing that can be exploited.

[xrayhunter1]

True

I setup my database in there is it suppose to be a cl_database.lua or just leave whats in there the code in ^^^^^^ -- Up
gamemode/module/database/database.lua

[Acecool]

If you're storing data such as inventory, etc. Then no, it should only be in a SERVER file. The client should have NO access to it. When a player fully connects, you should sync the data to the client; never give client access to the server-db and never trust a client to tell you what items are in their inventory via client-side script...

If it's for simple stuff; I'd recommend just using Player:SetPData( name_of_data, data )
and Player:GetPData( name_of_data, default_data )

[xrayhunter1]

For the

// Retrieve
local _inv = Player:GetPData( "inventory", util.JSONToTable( "inventory", { } ) )

could i just put in

sql.Query("CREATE TABLE IF NOT EXISTS Players(SteamID VARCHAR(25) NOT NULL PRIMARY KEY, Nick VARCHAR(40), Tokens INT(999999))")

[Shriio]

I made a ATM banksystem based on SqLite, download and have a look, it might help you, it's made simple.