Login system with mysql please help

[2013martin1212]

Hello all i have a login system and i want to save all my data in a mysql form

mysql sourceS:

local addressS = "127.0.0.1"            
local userName = "SH24161_default"          
local passWord = "eRi1UuWbkbMW9z"            
local dbName = "SH24161_default"              
local port = 3306 
local connection = dbConnect("mysql", "dbname=" .. dbName .. ";host=" .. addressS, userName, passWord ) 
     
if not connection then 
    outputDebugString("Nem sikerült a MySQL csatlakozás.", 3, 255, 0, 0) 
else 
    outputDebugString("Sikerült a MySQL csatlakozás.", 3, 255, 0, 0) 
end 
  

My login sourceS data:

local mysql = exports.cG_MySQL 
  
function logThePlayer(thePlayer, userName, passWord) 
    if thePlayer and userName and passWord then 
        if getElementType(thePlayer) == "player" then 
            local account = mysql:db_Query("SELECT * FROM accounts WHERE userName='" .. userName .. "'") 
            for i, sor in ipairs(account) do 
                if sor["passWord"] == passWord then 
                    local lastLogin = getRealTime() 
                    if tostring(lastLogin.month+1):len() < 2 then 
                        lastLogin.month = "0" .. tostring(lastLogin.month)+1 
                    end 
                    if tostring(lastLogin.monthday):len() < 2 then 
                        lastLogin.monthday = "0" .. tostring(lastLogin.monthday) 
                    end 
                    local lastLoginText = tostring(lastLogin.year+1900 .. "-" .. lastLogin.month .. "-" .. lastLogin.monthday) 
                    mysql:db_Exec("UPDATE accounts SET pLastLogin='" .. lastLoginText .. "' WHERE userName='" .. userName .. "'") 
                    mysql:db_Exec("UPDATE accounts SET pIP='" .. getPlayerIP(thePlayer) .. "' WHERE userName='" .. userName .. "'") 
                    mysql:db_Exec("UPDATE accounts SET pSerial='" .. getPlayerSerial(thePlayer) .. "' WHERE userName='" .. userName .. "'") 
                    local character = mysql:db_Query("SELECT * FROM characters WHERE accountName ='" .. userName .. "'") 
                    setElementData(thePlayer, "acc:adminLevel", sor["adminLevel"]) 
                    outputChatBox("a") 
                    if character then 
                        outputChatBox("a") 
                        for i, sor in ipairs(character) do 
                            setElementData(thePlayer, "acc:userName", userName) 
                            setPlayerMoney(thePlayer, sor["pMoney"]) 
                            spawnPlayer(thePlayer, sor["pX"], sor["pY"], sor["pZ"], sor["pRot"], sor["pSkin"], sor["pInt"], sor["pDim"]) 
                            setElementData(thePlayer, "acc:legalChange", true) 
                            setPlayerName(thePlayer, sor["characterName"]) 
                            setCameraTarget(thePlayer, thePlayer) 
                            --setElementData(thePlayer "acc:legalChange", false) 
                            triggerClientEvent(thePlayer, "hideLGP", getRootElement()) 
                            local itemTable = sor["pItems"]:gsub(",", "") 
                            outputChatBox(itemTable:gsub(itemTable:sub(-1), "")) 
                            for i, k in ipairs(itemTable) do 
                                setElementData(thePlayer, "acc:pItem" .. i, k) 
                                outputChatBox(i .. " slot iteme: " .. k) 
                            end 
                        end 
                    else 
                        triggerClientEvent(thePlayer, "createChar", getRootElement()) 
                        triggerClientEvent(thePlayer, "hideLGP", getRootElement()) 
                    end 
                end 
            end 
        end 
    end 
end 
addEvent("logIn", true) 
addEventHandler("logIn", getRootElement(), logThePlayer) 

What else i need to ad to save my data and read the data and log in to the server please help i am new in the mysql and i want to learn but the wiki is doesnt help me to many

[xXMADEXx]

Does debugscript show any errors? I didn't check you're entire script, but from what I see there's no errors.

[iAxel]

Example

MySQL resource

Server.lua

  
local MySQL = nil 
  
function Database(host, user, pass, database) 
    MySQL = dbConnect('mysql', ('dbname=%s;host=%s;port=%d'):format(database, host, (tonumber(port) or 3306)), user, pass) 
    if (not MySQL) then 
        outputServerLog('Couldnt connect to MySQL Server!') 
        stopResource(getThisResource()) 
    end 
end 
  
function deDatabase() 
    if (MySQL) then 
        destroyElement(MySQL) 
    end 
end 
  
function Query(query, ...) 
    local queryHandle = dbQuery(MySQL, query, ...) 
    local r1, r2, r3 = dbPoll(queryHandle, -1) 
    if (not r1) then 
        outputDebugString(('Query failed: errCode: %d - errMsg: %s'):format(r2, r3)) 
        return false 
    end 
    return r1, r2, r3 
end 
  
function Exec(query, ...) 
    return dbExec(MySQL, query, ...) 
end 
  
addEventHandler('onResourceStart', resourceRoot, 
    function () 
        Database(..., ..., ..., ...) 
    end 
) 
  
addEventHandler('onResourceStop', resourceRoot, 
    function () 
        deDatabase() 
    end 
) 
  

Meta.xml

  
<export function="Query" type="server" /> 
<export function="Exec" type="server" /> 
  

Login Resource

  
local mysql = exports.MySQL --MySQL Resource 
  
function PlayerJoin() 
    local name = getPlayerName(source) 
    local serial = getPlayerSerial(source) 
    local query, result = mysql:Query('SELECT `serial` FROM `accounts` WHERE `serial` = ?', serial) 
    if (query) then 
        if (result > 0) then 
            --Player LogIn 
        else 
            local pass = '1234567890' 
            local query = mysql:Exec('INSERT INTO `accounts` (`name`, `pass`, `serial`) VALUES (?, ?, ?)', name, pass, serial) 
            if (query) then 
                 --Player Register 
            end 
        end 
    end 
end 
  

Login Resource check player serial and if serial is in database then autologin or autoregister

[2013martin1212]

Thanks iAxel its works fine now

[iAxel]

You're welcome

[jingzhi]

Hello all i have a login system and i want to save all my data in a mysql form

mysql sourceS:

local addressS = "127.0.0.1"            
local userName = "SH24161_default"          
local passWord = "eRi1UuWbkbMW9z"            
local dbName = "SH24161_default"              
local port = 3306 
local connection = dbConnect("mysql", "dbname=" .. dbName .. ";host=" .. addressS, userName, passWord ) 
     
if not connection then 
    outputDebugString("Nem sikerült a MySQL csatlakozás.", 3, 255, 0, 0) 
else 
    outputDebugString("Sikerült a MySQL csatlakozás.", 3, 255, 0, 0) 
end 
  

My login sourceS data:

local mysql = exports.cG_MySQL 
  
function logThePlayer(thePlayer, userName, passWord) 
    if thePlayer and userName and passWord then 
        if getElementType(thePlayer) == "player" then 
            local account = mysql:db_Query("SELECT * FROM accounts WHERE userName='" .. userName .. "'") 
            for i, sor in ipairs(account) do 
                if sor["passWord"] == passWord then 
                    local lastLogin = getRealTime() 
                    if tostring(lastLogin.month+1):len() < 2 then 
                        lastLogin.month = "0" .. tostring(lastLogin.month)+1 
                    end 
                    if tostring(lastLogin.monthday):len() < 2 then 
                        lastLogin.monthday = "0" .. tostring(lastLogin.monthday) 
                    end 
                    local lastLoginText = tostring(lastLogin.year+1900 .. "-" .. lastLogin.month .. "-" .. lastLogin.monthday) 
                    mysql:db_Exec("UPDATE accounts SET pLastLogin='" .. lastLoginText .. "' WHERE userName='" .. userName .. "'") 
                    mysql:db_Exec("UPDATE accounts SET pIP='" .. getPlayerIP(thePlayer) .. "' WHERE userName='" .. userName .. "'") 
                    mysql:db_Exec("UPDATE accounts SET pSerial='" .. getPlayerSerial(thePlayer) .. "' WHERE userName='" .. userName .. "'") 
                    local character = mysql:db_Query("SELECT * FROM characters WHERE accountName ='" .. userName .. "'") 
                    setElementData(thePlayer, "acc:adminLevel", sor["adminLevel"]) 
                    outputChatBox("a") 
                    if character then 
                        outputChatBox("a") 
                        for i, sor in ipairs(character) do 
                            setElementData(thePlayer, "acc:userName", userName) 
                            setPlayerMoney(thePlayer, sor["pMoney"]) 
                            spawnPlayer(thePlayer, sor["pX"], sor["pY"], sor["pZ"], sor["pRot"], sor["pSkin"], sor["pInt"], sor["pDim"]) 
                            setElementData(thePlayer, "acc:legalChange", true) 
                            setPlayerName(thePlayer, sor["characterName"]) 
                            setCameraTarget(thePlayer, thePlayer) 
                            --setElementData(thePlayer "acc:legalChange", false) 
                            triggerClientEvent(thePlayer, "hideLGP", getRootElement()) 
                            local itemTable = sor["pItems"]:gsub(",", "") 
                            outputChatBox(itemTable:gsub(itemTable:sub(-1), "")) 
                            for i, k in ipairs(itemTable) do 
                                setElementData(thePlayer, "acc:pItem" .. i, k) 
                                outputChatBox(i .. " slot iteme: " .. k) 
                            end 
                        end 
                    else 
                        triggerClientEvent(thePlayer, "createChar", getRootElement()) 
                        triggerClientEvent(thePlayer, "hideLGP", getRootElement()) 
                    end 
                end 
            end 
        end 
    end 
end 
addEvent("logIn", true) 
addEventHandler("logIn", getRootElement(), logThePlayer) 

What else i need to ad to save my data and read the data and log in to the server please help i am new in the mysql and i want to learn but the wiki is doesnt help me to many

Marked

[2013martin1212]

now i figure out the script works fine if i have a charakter on the database, but if i dont have the doesnt load up the other reource which is a character creator , i give this lua to that but it doesnt work

triggerClientEvent(thePlayer, "createChar", getRootElement()) 
                        triggerClientEvent(thePlayer, "hideLGP", getRootElement())