MySQL advice?

Hey, I just started teaching myself lua about a week ago to finally get around to coding an idea I had a while back. A big part of that idea revolves around a database; since I will be using that database across servers and with my website, I need to use my dedicated MySql DB box, instead of sqllite. Now here’s my question, which MySQL module should I use? I’ve seen a few of them: GDatabase, gm_mysql, tmysql. What are the differences between them, and which one should I use?

GDatabase is old and inefficient so I would strongly against using it. I would recommend either tmysql3, or if you prefer OO programming and the ability to connect to multiple databases, mysqloo.

I think I’ll use mysqloo. I tried to make a lua script following the tutorial on the gmod wiki, but I can’t get it to work at all. I might be doing it wrong though. Put the script in “database.lua” in my gamemode folder, and put "include( ‘database.lua’ ) in the init.lua file.

TMySQL for single databases, MySQLoo for multiple, either way, both are suffice.

Does this look right?




require ("mysqloo")                // Include the modules
 
local DATABASE_HOST = "192.168.1.10"   // database host (can be an ip)
local DATABASE_PORT = 3306                // port to the database, you probably wont need to change this unless you get told to
local DATABASE_NAME = "gmod"       // name of the database
local DATABASE_USERNAME = "gmod"        // username which you use to access it
local DATABASE_PASSWORD = "xxxxxxx"          // password of the username

function connectToDatabase()
	databaseObject = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
	databaseObject.onConnected = function() print("Database linked!") end
	databaseObject.onConnectionFailed = function() print("Failed to connect to the database.") end
	databaseObject:connect()
end
 
connectToDatabase()
 
function checkQuery(query)
    local playerInfo = query:getData()
    if playerInfo[1] ~= nil then
	return true
    else
	return false
    end
end
 
function FirstJoinMysql( ply )
    local query1 = databaseObject:query("SELECT * FROM players WHERE ID = '" .. ply:SteamID() .. "'")
    query1.onSuccess = function(q)
        if not checkQuery(q) then
	    local query2 = databaseObject:query("INSERT INTO players(ID, tokens) VALUES ('" .. ply:SteamID() .. "', " .. 1 .. ")")		// else create the bugger
	    query2.onSuccess = function(q)  print("Created you!") end
	    query2.onError = function(q,e) print("something went wrong") end
	    query2:start()	
        else
            print("You are already created!")
        end
    end
    query1.onError = function(q,e) print("something went wrong when checking") end
    query1:start()
end
hook.Add( "PlayerInitialSpawn", "PlayerInitialSpawn", FirstJoinMysql )


This is “database.lua” in “dev/gamemode”. “dev” is my testing gamemode. “init.lua” has “include( ‘database.lua’ )”
“libmysql.dll” is in “orangebox/bin”, and “gmcl/sv_mysqloo_i486.dll” are in “lua/includes/modules/”. This is a linux server.

I’m pretty sure mysqloo automatically returns the table with string keys, e.g.
playerInfo[“ID”], playerInfo.tokens unless a certain flag is applied to the query.

[editline]30th October 2011[/editline]
Also you can’t concat numbers like that, if the number is like that by default you can simply add it in the string.

I copied this from the “MySQL” tutorial on the gmod wiki, all I’d like to for now is add the steamid and token value into my table “players” with columns ID and tokens, I forgot to mention that it does nothing atm. I restart my dev server and check the database after every change and connect, and the database is still empty.

Oh, are you referring to the “checkQuery” function?

Can you provide quotes to what you are referring to? I literally saved my first lua file like 3 days ago, and I have no idea what you are talking about.

This line:
[lua]
local query2 = databaseObject:query(“INSERT INTO players(ID, tokens) VALUES (’” … ply:SteamID() … "’, " … 1 … “)”)
[/lua]
should be:
[lua]local query2 = databaseObject:query(“INSERT INTO players(ID, tokens) VALUES (’” … ply:SteamID() … “’, 1)”)[/lua]

Aah okay, thanks. Like I said, I just copied this from the tutorial and edited it to my needs. Other than that, it should work?

I’m still getting “empty set” when I look in the table.

Is the MySQL server hosted in the local network? (And has it allowed the server’s IP to access it?)

Its on the local network (hence the 192.168.1.11 IP) and the user gmod@192.168.1.12 (game server address) is allowed access.

Then I don’t really know what’s wrong, you could try and remove , DATABASE_PORT from your connect to the database code, because it’s not really needed if you’re using a standard mysql database that isn’t configured specifically for another port.

I removed the port, still getting Empty Set. This code would tell me, in the console, wether it connected or not, wouldn’t it?

I looked through the server startup log and saw



ERROR! Module 'zlib_b64' not found!
ERROR! Module 'mysqloo' not found!
[gamemodes/dev/gamemode/database.lua:10] attempt to index global 'mysqloo' (a nil value)


I have the modules in lua/includes/modules, but I saw a post on the Mysqloo binary thread where someone said it didn’t work until he changed the “_i486” to “_linux” so I’ll try that.

Edit: PROBLEM SOLVED! I had to rename all the .dll modules with the above scheme (_linux instead of _i486), then I had to download libmysql.so and libmysqlclient.so and place them in my orangebox/bin folder 9I had libmysql.dll in there previously). In case anyone else has this problem, and comes across this thread from a google search, here’s a link for the 32-bit linux mysql connector libraries needed: libmysql connector