[OK] MySQL & Variables

[=KoG=Rouche]

Hi all,

I'm working on a new Userpanel linked to a MySQL database.

(Datas will be triggered by 2 servers)

Here is my question :

Is it better to make all mysql requests at player login and create local variables used by script (with a refresh time) ?

OR

Is it better to trigger a mysql request each time the script need it ?

I don't know the best way so if someone can help me.

Thanks,

Edited April 28, 2014 by Guest

[Spajk]

Well, if both servers are using these variables then you will need to make an mysql request whenever you need the data as the other server might have changed that data to something else.

[=KoG=Rouche]

A player can not play both server at the same time !

So it's not a problem ... ?

All datas in my DB are only about a player account.

My Tables :

PLAYER (Vars : cash, token, mail, birthday, vip, ... ) (Shared datas)

DMServer (Vars : lastSeen, timePlayed, moneyUsed, moneySend, ...) (Server DM datas)

OLDServer (Vars : lastSeen, timePlayed, moneyUsed, moneySend, ...) (Server OLD Datas)

ACHIEVEMENTS (ach1, ach2, ach3, ...) (Shared Datas)

[Spajk]

Ah, then it shouldn't be a problem to use the local variables and just read/save to mysql on login and logout.

[=KoG=Rouche]

Thanks for your help.

I've another question :

How to save datas in case of "Leave" or "Timed Out" of a player (no logout action) ?

I think the variables are clientside because each player has his own variables.

[Spajk]

Don't keep variables clientside, just use tables to store them.

Example:

On top of the server script:

playtime = {} 

When player logins:

playtime[player] = x 

When player logsout:

playtime[player] = nil 

Also, onPlayerLogout event should be triggered when the player disconnects.

[=KoG=Rouche]

Thanks i'll try it after work

[=KoG=Rouche]

Hi all, i try to display a data from my database but i got some problems

  
function ClientResStart() 
handler = mysql_connect("127.0.0.1", "user", "pass", "dbname") -- Establish the connection 
    if ( not handler ) then -- The connection failed 
      outputDebugString("Unable to connect to the MySQL server") 
      outputConsole ("Unable to connect to the MySQL server") 
    else 
      outputDebugString("OK") 
      outputConsole("OK") 
      local result = mysql_query(handler, "SELECT * FROM Player WHERE nickname='rouche'") 
          if (not result) then 
            outputConsole("Error executing the query: (" .. mysql_errno(handler) .. ") " .. mysql_error(handler)) 
          else 
            outputConsole ("Pseudo : "..result.nickname.. " !") 
            mysql_free_result(result) -- Freeing the result is IMPORTANT 
          end 
      mysql_close(handler) -- Close the connection 
    end 
end 
addEventHandler("onResourceStart",rootElement,ClientResStart) 
  

I'm pretty sure i don't use the right way to display database data.

I think i need to retrieve data in a tab[] and then display the data ... True ?

If yes how to retrieve all these datas in the tab ?

Thank you for the help and keep playing.

Edited May 7, 2014 by Guest

[Karuzo]

I dunno why you use the mysql module, i prefer the mta intern db functions.

I'm not sure but you have to use smth like a dbPoll, which gets the data.

I do it like that:

  
query = dbQuery(connect, "SELECT * FROM wcf1_user WHERE username = '"..username.."'") 
result = dbPoll(query, -1) 
    datas = result[1] 
--and then i just use the datas variable. 
--like : 
  
local money = datas.money or 0  
local playtime = datas.playtime or 0 --for example. 
  

//edit:

I think its called mysql_fetch_assoc , same as i did.

https://wiki.multitheftauto.com/wiki/Mo ... etch_assoc

Im not sure how its with the mysql module.

[=KoG=Rouche]

Ok thanks it works now

With DB function there isn't need to close the connection ? something like 'dbClose' ?

Must i execute all this code clientside ? I think its better to create the "connect" object with password, username ... on serverside (Security reasons) and then send the "connect" object to clientside for queries ... true ?

  
rootElement = getRootElement() 
  
function ClientResStart() 
connect = dbConnect("mysql","dbname=NAME;host=127.0.0.1", "USER", "PASS", "share=1") -- Establish the connection 
    if ( not connect ) then -- The connection failed 
      outputDebugString("Unable to connect to the MySQL server") 
      outputConsole("Unable to connect to the MySQL server") 
    else 
      outputDebugString("OK") 
      outputConsole("OK") 
      query = dbQuery(connect, "SELECT * FROM Player WHERE nickname='rouche'") 
      result = dbPoll(query, -1) 
      datas = result[1] 
      local nickname = datas.nickname 
      outputDebugString("Pseudo : "..nickname.." !") 
      dbClose(connect) -- Close the connection 
    end 
end 
addEventHandler("onResourceStart",rootElement,ClientResStart) 
  

Edited April 17, 2014 by Guest

[Karuzo]

You can make a gui edit box,

get the input via guiGetText

and trigger it to the server

[=KoG=Rouche]

Ok thanks for your help

[=KoG=Rouche]

Can someone give me advice on my script ?

Is it a good way to start with Mysql ?

(i'll try the script on my server later cuz i'm at work, i only access console for the moment so i can see the outpubDebusgString message serverside / I'll edit this topic after work in ~6 hours)

Serverside

  
-- AddUser Server Part -- 
-- Create an connection object for the Database 
function connectDB() 
    con = dbConnect("mysql","dbname=DBNAME;host=127.0.0.1", "USER", "PASSWORD", "share=1") 
    if (con) then 
        outputDebugString("Connection OK !") 
    else 
        con=0 
        outputDebugString("Connection to Database failled !") 
    end 
    return con 
end 
  
------- VARIABLES ------- 
rootElement = getRootElement() 
local connect = connectDB() 
--------- CODE ---------- 
  
-- STEP 1 | Called when a player register and logged in on =KoG= Server | 
function regAndLogin() 
    outputDebugString("Check if in regAndLogin function - OK") 
    triggerClientEvent("onRegAndLogin",rootElement,connect) 
end 
addEvent("onPlayerLogin",true) 
addEventHandler("onPlayerLogin",rootElement,regAndLogin) 
  

Clientside

  
-- AddUser Client Part -- 
------- VARIABLES ------- 
rootElement = getRootElement() 
thePlayer = getPlayerFromName() 
-- Client Answer from a GUI form (Not yet coded) 
local account="toto" 
local password="passtoto" 
local mail="[email protected]" 
local day=15 
local month=5 
local year=1998 
  
-- STEP 2 | Called by Step 1 (Serverside) When a player use login action from my panel | 
function loginActionOneTime(connect) 
    outputDebugString("Check if in loginActionOneTime Function - OK") 
    checkIfExist(theplayer,connect) 
end 
addEvent("onRegAndLogin",true) 
addEventHandler("onRegAndLogin",rootElement,loginActionOneTime) 
  
-- STEP 4 | Called by Step 2 (Clientside) addUser function (Add datas to the database) | 
function addUser(connect) 
    outputDebugString("Check if in addUser - OK !") 
    if(connect) then 
        outputDebugString("Check just before dbQuery INSERT TO player - OK !") 
        dbQuery(connect,"INSERT INTO player (nickname,password,mail,bDay,bMonth,bYear,level,VIP,welcomeText) VALUES ("..account..","..password..","..mail..","..day..","..month..","..year..",'0','0','none')") 
        outputDebugString("Creating a new profile for "..theplayer.." with account : "..account.."!") 
    else 
        outputDebugString("No Connection Object Here ! - KO") 
    end 
end 
  
-- STEP 3 | Called by Step 2 (Clientside) Check if the profile already exist in database | 
function checkIfExist(theplayer,connect) 
    outputDebugString("Check if in checkIfExist Function - OK") 
    query = dbQuery(connect, "SELECT * FROM Player WHERE nickname='"..account.."'") 
    if(query) then 
        outputDebugString("Check if the checkIfExist query exist - OK") 
        outputDebugString("Database already have an account with this name : "..account.."!") 
    else 
        outputDebugString("checkIfExist call addUser function to create a new profile - OK") 
        addUser(connect) 
    end 
end 
  

[Karuzo]

You cant use dbQuery Clientside.

and for an INSERT use dbExec

and try to use one query.

[=KoG=Rouche]

Here is the new version of my script.

Is it better now ?

And is the dbExec line ok with insert > i mean is it correct -> ... ,"..account..", ... ?

Serverside

  
-- AddUser Server Part -- 
-- Create an connection object for the Database 
function connectDB() 
    con = dbConnect("mysql","dbname=DBNAME;host=127.0.0.1", "USER", "PASSWORD", "share=1") 
    if (con) then 
        outputDebugString("Connection OK !") 
    else 
        outputDebugString("Connection to Database failled !") 
    end 
    return con 
end 
  
------- VARIABLES ------- 
rootElement = getRootElement() 
local connect = connectDB() 
--------- CODE ---------- 
  
-- STEP 1 | Called when a player register and logged in on =KoG= Server | 
function regAndLogin() 
    outputDebugString("Check if in regAndLogin function - OK") 
    triggerClientEvent("onRegAndLogin",rootElement) 
end 
addEvent("onPlayerLogin",true) 
addEventHandler("onPlayerLogin",rootElement,regAndLogin) 
  
-- STEP 3 | Called by Step 2 (Clientside) Check if the profile already exist in database | 
function addUser(account,password,mail,day,month,year) 
    outputDebugString("Check if in checkIfExist Function - OK") 
    query = dbQuery(connect, "SELECT nickname FROM Player WHERE nickname='"..account.."'") 
    if(query) then 
        outputDebugString("Database already have an account with this name : "..account.."!") 
    else 
        outputDebugString("checkIfExist will now execute dbExec to add create a new profile in DB - OK") 
        -- INSERT TO (Add an entity in player table) 
        dbExec(connect,"INSERT INTO player (nickname,password,mail,bDay,bMonth,bYear,level,VIP,welcomeText) VALUES ("..account..","..password..","..mail..","..day..","..month..","..year..",'0','0','none')") 
        outputDebugString("Profile Added in DB - OK") 
    end 
end 
addEvent("onAddUser",true) 
addEventHandler("onAddUser",rootElement,addUser) 
  

Clientside

  
-- AddUser Client Part -- 
------- VARIABLES ------- 
rootElement = getRootElement() 
thePlayer = getPlayerFromName() 
-- Client Answer from a GUI form (Not yet coded) 
local account="toto" 
local password="passtoto" 
local mail="[email protected]" 
local day=15 
local month=5 
local year=1998 
  
-- STEP 2 | Called by Step 1 (Serverside) When a player use login action from my panel | 
function loginAction() 
    outputDebugString("Check if in loginAction Function - OK") 
    triggerServerEvent("onAddUser",account,password,mail,day,month,year) 
end 
addEvent("onRegAndLogin",true) 
addEventHandler("onRegAndLogin",rootElement,loginAction) 
  

Thanks for help

Edited April 17, 2014 by Guest

[Karuzo]

dbQuery doesn't return datas, you have to use dbPoll for that.

So you can check.

You could make it like this if you want to check it :

  
function login_func ( username, password ) 
query = dbQuery(connect, "SELECT * FROM Player WHERE nickname= '"..account.."'") 
result = dbPoll(query, -1) 
  

Btw how do you trigger the regandLogin function?

and the local connect = connectDB() is useless.

Since you didn't made your connection local in the function you can just use dbQuery(con,.....)

[WhoAmI]

query = dbQuery(connect, "SELECT * FROM Player WHERE nickname= '"..account.."'") 

This is wrong. With this code it's easy to do SQL inject. Use this instead

query = dbQuery(connect, "SELECT * FROM Player WHERE nickname= ?", account) 

[=KoG=Rouche]

About regAndLogin function :

Btw how do you trigger the regandLogin function?

the regAndLogin function is triggered when a player use login function on the server

  
function regAndLogin() 
    outputDebugString("Check if in regAndLogin function - OK") 
    triggerClientEvent("onRegAndLogin",rootElement) 
end 
addEvent("onPlayerLogin",true) 
addEventHandler("onPlayerLogin",rootElement,regAndLogin) -- HERE, When a player join it 'll trigger the function 
  

Here is the last version of server.lua with your advices.

Is the anti SQL injection important to in dbExec function with an insert ?

Serverside

  
-- || =KoG= Userpanel By Rouche || -- 
--------- MYSQL --------- 
-- Create an connection object for the Database 
function connectDB() 
    local con = dbConnect("mysql","dbname=DBNAME;host=127.0.0.1", "USER", "PASSWORD", "share=1") 
    if (con) then 
        outputDebugString("Connection OK !") 
    else 
        outputDebugString("Connection to Database failled !") 
    end 
    return con 
end 
  
------- VARIABLES ------- 
rootElement = getRootElement() 
local connect = connectDB() 
  
--------- CODE ---------- 
  
-- Player Login / Register -- 
-- STEP 1 | Called when a player register and logged in on =KoG= Server | 
function regAndLogin() 
    outputDebugString("Check if in regAndLogin function - OK") 
    triggerClientEvent("onRegAndLogin",rootElement) 
end 
addEvent("onPlayerLogin",true) 
addEventHandler("onPlayerLogin",rootElement,regAndLogin) 
  
-- STEP 3 | Called by Step 2 (Clientside) Check if the profile already exist in database | 
function addUser(account,password,mail,day,month,year) 
    outputDebugString("Check if in checkIfExist Function - OK") 
    query = dbQuery(connect, "SELECT nickname FROM Player WHERE nickname= ?", account) 
    result = dbPoll(query, -1) 
    if(result) then 
        outputDebugString("Database already have an account with this name : "..account.."!") 
        -- Call a function that retrieves DATA in Variables (To BE CODED) 
    else 
        outputDebugString("checkIfExist will now execute dbExec to add create a new profile in DB - OK") 
        -- INSERT TO (Add an entity in player table) 
        dbExec(connect,"INSERT INTO player (nickname,password,mail,bDay,bMonth,bYear,level,VIP,welcomeText) VALUES ("..account..","..password..","..mail..","..day..","..month..","..year..",'0','0','none')") 
        outputDebugString("An entity in player Table has been Created - OK") 
        dbExec(connect,"INSERT INTO Events (toPlayer,eRegister,eLevel1,eLevel6,eLevel10,eLevel13,eLevel15,eLevel17,eLevel20,eChangeServ) VALUES ("..account..",'0','0','0','0','0','0','0','0','0')") 
        outputDebugString("An entity in Events Table has been Created - OK") 
        dbExec(connect,"INSERT INTO DM (lastSeen,timePlayed,cash,moneyUse,moneyWon,timeJoin,toPlayer) VALUES ('0','0','0','0','0','0','0',"..account..")") 
        outputDebugString("An entity in DM Table has been Created - OK") 
        dbExec(connect,"INSERT INTO OLD (lastSeen,timePlayed,cash,moneyUse,moneyWon,timeJoin,toPlayer) VALUES ('0','0','0','0','0','0','0',"..account..")") 
        outputDebugString("An entity in OLD Table has been Created - OK") 
    end 
end 
addEvent("onAddUser",true) 
addEventHandler("onAddUser",rootElement,addUser) 
  

Thanks

[Karuzo]

the local connect = connectDB() is useless.

Since you didn't made your connection local in the function you can just use dbQuery(con,.....)

regandLogin won't work.

Since that will be triggered only if the player uses /login command.

So better use onClientResourceStart on the client-side and trigger to the server.

[=KoG=Rouche]

About local connect. I edited the script "con" is now a local variable.

  
local con = dbConnect("mysql","dbname=DBNAME;host=127.0.0.1", "USER", "PASSWORD", "share=1") 
  

About the function triggered on login. It is what i want to do.

I would like to create the profile in database only if a player logged in to the server. (not when he joins the server)

[=KoG=Rouche]

It works now

dbExec(connect,"INSERT INTO Player (nickname,password,mail,bDay,bMonth,bYear,level,VIP,welcomeText) VALUES ('"..account.."','"..password.."','"..mail.."','"..day.."','"..month.."','"..year.."','0','0','none')") 
  

Thanks,