HELP! Database ( ID System )

[Markn1]

Hello! The problem with the database, namely the preservation of the player's ID. For example, if the player has an identifier (50) and the next time the player connects to the account, he will have 50. Even if he is one on the server. Help me please!

code > server 
 

ids = {}

function displayLoadedRes ( res )
	max_players = getServerConfigSetting ( "maxplayers" )
	exports.scoreboard:scoreboardAddColumn("playerid", root, 30, "ID", 1)
	for i = 1, max_players do
		table.insert ( ids, false )
	end
	for i, v in ipairs ( getElementsByType ( 'player' )) do
		assignPlayerFreeID ( v )
	end
end
addEventHandler ( "onResourceStart", getResourceRootElement(), displayLoadedRes )

function assignPlayerFreeID ( player )
	local assigned = false
	if isElement ( player ) then
		for i, v in ipairs (ids) do
			if not isElement(v) then
				assigned = i
				ids[i] = player
				break
			end
		end
		if assigned then
			setElementData ( player, "playerid", assigned )
			return true
		else	
			outputChatBox ( 'Невозможно подобрать свободный ид для игрока '..getPlayerName ( player ))
			kickPlayer ( player )
			return false
		end
	else
		return false
	end
end

function getPlayerFromID ( id )
	for i, v in ipairs ( getElementsByType ( 'player' )) do
		local playerid = getElementData ( v, "playerid" )
		if playerid and playerid == tonumber(id) then
			return v
		end
	end
	return false
end

-- SAVE ( DB )

createDB = dbConnect( "sqlite", "save.db" ) 
if createDB then
	outputDebugString("DB ON")
else
	outputDebugString("DB Fail")
end

addEventHandler( "onResourceStart", getRootElement(), function()
	if createDB then
		local tabela = dbExec( createDB, "CREATE TABLE IF NOT EXISTS save ( login, playerid )" )
	else
	    return false
    end   
end )

addEventHandler( "onPlayerLogin", getRootElement(), function( pre, cur )
	if not isGuestAccount ( cur ) then assignPlayerFreeID ( source ) end
	local login = getAccountName (getPlayerAccount(source))
    local q = dbQuery( createDB, "SELECT * FROM save WHERE login=?", login ) 
	local result = dbPoll( q, -1 )
	if result then
		for _, row in ipairs(result) do
		setElementData ( source, "playerid", row["playerid"] )
		end
    end
end )

function onPlayerQuit ( thePlayer )
	local lp = source 
	local pl = getPlayerAccount (lp) 
	if isGuestAccount(pl) then return end 
	local login = getAccountName (pl)
    local playerid = getElementData ( lp, "playerid" );
	local q = dbQuery( createDB, "SELECT * FROM save WHERE login=?", login ) 
	local result = dbPoll( q, -1 )
	dbFree(q)
    if #result == 0 then
		dbExec( createDB, "INSERT INTO save (login, playerid) VALUES (?,?)", login, playerid ) 
	else
	dbExec( createDB, "UPDATE save SET login=?, playerid=?", login, playerid ) 
	end
end
addEventHandler( "onPlayerQuit", getRootElement(), onPlayerQuit )
--addEventHandler( "onPlayerWanted", getRootElement(), onPlayerQuit )

addEventHandler ( "onResourceStop", resourceRoot, function () 
for _,v in ipairs ( getElementsByType ( "player" ) ) do
	local pl = getPlayerAccount (v) 
	if isGuestAccount(pl) then return end 
	local login = getAccountName (pl)
    local playerid = getElementData (v, "playerid" );
	local q = dbQuery( createDB, "SELECT * FROM save WHERE login=?", login ) 
	local result = dbPoll( q, -1 )
	dbFree(q)
        if #result == 0 then 
		    dbExec( createDB, "INSERT INTO save (login, playerid) VALUES (?,?)", login, playerid ) 
	    else 
	        dbExec( createDB, "UPDATE save SET login=?, playerid=?", login, playerid ) 
	    end
    end
end )

 

[idarrr]

It's better if you set an Account ID on player register, and use primary key ID on database as account id.

You can use.

SELECT MAX(id)+1 AS lastid FROM `your_table`

To get last highest ID for account ID when player register.

 

[NanoBob]

A better idea would be to use an autoincrement column in your database. So your code won't need to asign the IDs.

But since you also have a username, which is unique per playerm why not use that as primary key?

[ShayF2]

I'd save player data to the sql and use the save's index number as the players id.