MySQL Help

[joyenusi]

for some reason the function sql_value_skills in this code :

function sql_value_stats ( ply )    unique_id = sql.QueryValue("SELECT unique_id FROM player_info WHERE unique_id = '"..steamID.."'")
    money = sql.QueryValue("SELECT money FROM player_info WHERE unique_id = '"..steamID.."'")
    ply:SetNWString("unique_id", unique_id)
    ply:SetNWInt("money", money)
end
 
function sql_value_skills ( ply )
    unique_id = sql.QueryValue("SELECT unique_id FROM player_skills WHERE unique_id = '"..steamID.."'")
    speech = sql.QueryValue("SELECT speech FROM player_skills WHERE unique_id = '"..steamID.."'")
    fish = sql.QueryValue("SELECT fish FROM player_skills WHERE unique_id = '"..steamID.."'")
    farm = sql.QueryValue("SELECT farm FROM player_skills WHERE unique_id = '"..steamID.."'")
    ply:SetNWString("unique_id", unique_id)
    ply:SetNWInt("speech", speech)
    ply:SetNWInt("fish", fish)
    ply:SetNWInt("farm", farm)
    
    Msg(tostring(unique_id)..tostring(speech)..tostring(fish)..tostring(farm))
end
 
function saveStat ( ply )
    money = ply:GetNWInt("money")
    unique_id = ply:GetNWString ("SteamID")
    speech = ply:GetNWInt("speech")
    fish = ply:GetNWInt("fish")
    farm = ply:GetNWInt("farm")
    sql.Query("UPDATE player_skills SET speech = "..speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'")
    sql.Query("UPDATE player_info SET money = "..money.." WHERE unique_id = '"..unique_id.."'")
    ply:ChatPrint("Stats updated !")
end
 
function tables_exist()
 
    if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
        Msg("Both tables already exist !")
    else
        if (!sql.TableExists("player_info")) then
            query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
            result = sql.Query(query)
            if (sql.TableExists("player_info")) then
                Msg("Succes ! table 1 created \n")
            else
                Msg("Somthing went wrong with the player_info query ! \n")
                Msg( sql.LastError( result ) .. "\n" )
            end    
        end
        if (!sql.TableExists("player_skills")) then
            query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
            result = sql.Query(query)
            if (sql.TableExists("player_skills")) then
                Msg("Succes ! table 2 created \n")
            else
                Msg("Somthing went wrong with the player_skills query ! \n")
                Msg( sql.LastError( result ) .. "\n" )
            end    
        end
    end
 
end
 
function new_player( SteamID, ply )
 
        steamID = SteamID
        sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
        result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" )
        if (result) then
 
            sql.Query( "INSERT INTO player_skills (`unique_id`, `speech`, `fish`, `farm`)VALUES ('"..steamID.."', '1', '1', '1')" )
            result = sql.Query( "SELECT unique_id, speech, fish, farm FROM player_skills WHERE unique_id = '"..steamID.."'" )
            if (result) then
                Msg("Player account created !\n")
                sql_value_stats( ply )
                sql_value_skills( ply )
            else
                Msg("Something went wrong with creating a players skills !\n")
            end
 
        else
            Msg("Something went wrong with creating a players info !\n")
        end
end
 
function player_exists( ply )
 
    steamID = ply:GetNWString("SteamID")
 
    result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")
    if (result) then
            sql_value_stats( ply ) // We will call this to retrieve the stats
            sql_value_skills( ply ) // We will call this to retrieve the skills
    else
        new_player( steamID, ply ) // Create a new player :D
    end
end
 
function Initialize()
    tables_exist()
end
 
function PlayerInitialize(ply)
 
    timer.Create("Steam_id_delay", 1, 1, function()
        SteamID = ply:SteamID()
        ply:SetNWString("SteamID", SteamID)
        timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
        player_exists( ply ) 
    end)
 
end
 

produces nilfalsefalsefalse...

Why aren't my values being assigned :S

[Mrkrabz]

I've never been good with MYSQL in LUA but, how does your database look like? As in the structure.

[joyenusi]

I'm not sure. I'm just having an issue either setting or retrieving values. Wondering if my syntax or types are wrong anywhere.

[Freze]

Why would you do sql.QueryValue 4 times? Why not do one and just get all 4 fields?

[joyenusi]

[QUOTE=Freze;34818469]Why would you do sql.QueryValue 4 times? Why not do one and just get all 4 fields?[/QUOTE]

Only just gone through the MySQL tutorial.

[Freze]

Alright then.
Let me just get your code into sublime and look at it there.

[editline]22nd February 2012[/editline]

[lua]

function sql_value_stats ( ply )
steamID = ply:SteamID()
local unique_id = sql.QueryValue("SELECT unique_id FROM player_info WHERE unique_id = '".. steamID.."'")
local money = sql.QueryValue("SELECT money FROM player_info WHERE unique_id = '"..steamID.."'")
ply:SetNWString("unique_id", unique_id)
ply:SetNWInt("money", money)
end

function sql_value_skills ( ply )
steamID = ply:SteamID()
local unique_id = sql.QueryValue("SELECT unique_id FROM player_skills WHERE unique_id = '"..steamID.."'")
local speech = sql.QueryValue("SELECT speech FROM player_skills WHERE unique_id = '"..steamID.."'")
local fish = sql.QueryValue("SELECT fish FROM player_skills WHERE unique_id = '"..steamID.."'")
local farm = sql.QueryValue("SELECT farm FROM player_skills WHERE unique_id = '"..steamID.."'")
ply:SetNWString("unique_id", unique_id)
ply:SetNWInt("speech", speech)
ply:SetNWInt("fish", fish)
ply:SetNWInt("farm", farm)

Msg(tostring(unique_id)..tostring(speech)..tostring(fish)..tostring(farm))
end

function saveStat ( ply )
local money = ply:GetNWInt("money")
local unique_id = ply:SteamID()
local speech = ply:GetNWInt("speech")
local fish = ply:GetNWInt("fish")
local farm = ply:GetNWInt("farm")
sql.Query("UPDATE player_skills SET speech = "..speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'")
sql.Query("UPDATE player_info SET money = "..money.." WHERE unique_id = '"..unique_id.."'")
ply:ChatPrint("Stats updated !")
end

function tables_exist()

if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
result = sql.Query(query)
if (sql.TableExists("player_info")) then
Msg("Succes ! table 1 created \n")
else
Msg("Somthing went wrong with the player_info query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
if (!sql.TableExists("player_skills")) then
query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
result = sql.Query(query)
if (sql.TableExists("player_skills")) then
Msg("Succes ! table 2 created \n")
else
Msg("Somthing went wrong with the player_skills query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
end

end

function new_player( ply )

steamID = ply:SteamID()
sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" )
if (result) then

sql.Query( "INSERT INTO player_skills (`unique_id`, `speech`, `fish`, `farm`)VALUES ('"..steamID.."', '1', '1', '1')" )
result = sql.Query( "SELECT unique_id, speech, fish, farm FROM player_skills WHERE unique_id = '"..steamID.."'" )
if (result) then
Msg("Player account created !\n")
sql_value_stats( ply )
sql_value_skills( ply )
else
Msg("Something went wrong with creating a players skills !\n")
end

else
Msg("Something went wrong with creating a players info !\n")
end
end

function player_exists( ply )

steamID = ply:SteamID()

result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")
if (result) then
sql_value_stats( ply ) // We will call this to retrieve the stats
sql_value_skills( ply ) // We will call this to retrieve the skills
else
new_player( ply ) // Create a new player :D
end
end

function Initialize()
tables_exist()
end

function PlayerInitialize(ply)

timer.Create("Steam_id_delay", 1, 1, function()
SteamID = ply:SteamID()
ply:SetNWString("SteamID", SteamID)
timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
player_exists( ply )
end)

end[/lua]

You need to define steamID when doing the query.
I've done so getting it from ply each time.
Also localized your vars.

[joyenusi]

Even with your code I'm still getting nilfalsefalsefalse

[Freze]

Your database is built up wrong then, I believe.

[editline]22nd February 2012[/editline]

Try adding:
print(sql.LastError())
under your debugging Msg

[joyenusi]

nilnilnilnilnilnilnilnil now

[Mrkrabz]

As I said, what's the structure of your Database?

player_info and player_skills or w/e are 2 separate tables.

[joyenusi]

I don't know what I did, but for some reason it works now. Stand by for more errors soon. Cheers for all the help.

[Freze]

Maybe you restarted after using my code?
Mine should work.