MySQL loading data

[Greensleeves]

I started learn MySQL in gmod and wanted to make store karma value into database, so it save there. I made storing, and now it value saves well, but i have problem, even if in database my Steam ID have for example 100 karma, when i rejoin i have 0 karma. The question is: How to load data from MySQL when playerspawn? Thanks all!

[MrRalgoman]

GM/PlayerInitialSpawn
SQL WHERE Clause

[Greensleeves]

Im sorry, but i don't really understand, how can i call data from mysql?

[leeroy___]

You run a query.
"SELECT * FROM tableName WHERE id='someIDidk'"

[MrRalgoman]

Here is a link to the sql library, that is how lua talks to the sqlite databases in your server. https://wiki.garrysmod.com/page/Category:sql

[Greensleeves]

i have to look, thanks

[Greensleeves]

Okay, so i add function to my playerinitialspawn hook, it successfully select data, here it is, in console i get message, that Data Loaded
hook.Add( "PlayerInitialSpawn", "AddPlayerToDB", function( ply )
    AddPlayer( ply:SteamID(), ply:GetNWFloat("EpicBible.HolyKarma") )

    local query = DB_KARMA:query( "SELECT * FROM karmadate WHERE `steamID` = '".. ply:SteamID() .. "' AND `karmavalue` = '" .. ply:GetNWFloat("EpicBible.HolyKarma") .. "';")
query:start()
query.onSuccess = function()
        print( "[SQL] Data Loaded!" )
    end 
    query.onError = function(db, err)
        print("[SQL] (Loading Data) - Error: ", err )
        end 
end )
and now the question is: The karma addon have function - SetKarma, which is work same, that LocalPlyaer():SetNWFloat - ply:SetKarma(value), so what i need to write instead of "value" so it set the number from database?

[leeroy___]

The "onSuccess" function is using two variables, in this case the first one is the query and the second the data.
As seen on the documentation:
Query.onSuccess( q, data )
Called when the query is successful, [Table] data is the data the query returned.

The MySQLoo v9 Documentation

So you could write your "onSuccess" function like this:
function query.onSuccess(q, data) PrintTable(data) end
Do remember that "data" variable is a table. So you will need to index it in order to get something out of it.

Here is a database I wrote for a stupid level system. You could probably learn some things from it even if it is a spaghetti-code-clusterfuck. I wrote it when I started learning mysql and how to use it in gmod lua. A tip is to check out other peoples addons and see how they work etc. You can learn a lot from that.

[Greensleeves]

I think you don't really understand my problem, let me show it on video

https://www.youtube.com/watch?v=CdmDAyjwy2A&feature=youtu.be

[leeroy___]

Yeah but you aren't doing anything in the "onSuccess" function. You aren't setting the "EpicBible.HolyKarma" NW float to what ever the query returns.

How you could do it:
query.onSuccess = function(q, data) -- use the variables
if( data[1] ~= nil ) then -- check if the query returned anything. If not then just dont do anything
ply.money = data[1].money -- set the players money to whatever the query returned
end
end

[Romn]

forget this thing..

[Greensleeves]

So yeah, i trying your way and i know i close to the solution, i also removed checking for test, have a look, am i doing something wrong? I getting table in console and printmessage, so query is ok, but float ain't setting
hook.Add( "PlayerInitialSpawn", "AddPlayerToDB", function( ply )
    AddPlayer( ply:SteamID(), ply:GetNWFloat("EpicBible.HolyKarma") )

    local query = DB_KARMA:query( "SELECT karmavalue FROM karmadate WHERE `steamID` = '" .. ply:SteamID() .. "';")
query:start()
query.onSuccess = function(q, data)
PrintTable(data)
        print( "[SQL] Data Loaded!" )
ply:SetNWFloat("EpicBible.HolyKarma", tonumber(data[1]))
    end 
    query.onError = function(db, err)
        print("[SQL] (Loading Data) - Error: ", err )
        end 
end )

and also console:
Dropped Greensleeves from server (Disconnect by user.)
Client "Greensleeves" connected (ip).
1:
karmavalue = 1000
[SQL] Data Loaded!
Greensleeves: 0.00% Karma

[leeroy___]

As I have said before, the query returns a table of all the data that I got. Since SQL is returning a table you will have to index it. Like this:
data[1].karmavalue
You are attempting to convert "data[1]" to a number which wont work since you can't convert tables to numbers and it will return nil

[Greensleeves]

Ah, it's work, you're my savior, i really love your tolerance to my maybe dumbness, thank you!

https://i.imgur.com/lswRzrR.jpg

-that's how i glad to your help

[MrRalgoman]

For queries that you are inserting values into, i.e SteamID/karma value you should be using prepared queries to combat against sql injection. In my opinion using prepared queries with mysqloo is much cleaner looking anyway.

local db -- assume valid connected db

-- the '?' is a placeholder which will be changed
local q = db:prepare("SELECT * FROM mytable WHERE SteamID='?'") -- prepared query object
-- 1 is the index of '?', if you have multiple queries, or multiple '?'
-- which are supposed to be strings, their indexes are in the same order
-- you called/declared them.
q:setString(1, ply:SteamID())

function q.onSuccess(q, data)
-- do stuff
end
function q.onError(q, err, data)
-- do stuff
end
q:start()

[Greensleeves]

hm, gonna try to recode it

[MrRalgoman]

I mean, unless you plan on releasing this as an addon you don't need to worry about people trying to fuck with your database. But in the future, it's better practice to use prepared queries.

[Greensleeves]

okay, i changed that, nothing changed, but i trust, that it's better way, thank you. I making this for my future dream project oriented on Deus Ex and Blade Runner universes but with own history etc