[HELP] problem with k/d saving to sqlite

Dziugasc · December 16, 2017

so i tried to make saving system that saves kills and deaths

but it doesn't save

full script

local root = getRootElement()
local scoresRoot = getResourceRootElement(getThisResource())

local scoreColumns = {"Kills", "Deaths", "ratio", "status"}
local isColumnActive = {}
local KDR_DECIMAL_PLACES = 2

--http://lua-users.org/wiki/SimpleRound
local function round(num, idp)
	local mult = 10^(idp or 0)
	return math.floor(num * mult + 0.5) / mult
end

local function setScoreData (element, column, data)
	if isColumnActive[column] then
		setElementData(element, column, data)
	end
end

local dbConnection = dbConnect("sqlite", "backupexprank.db")
local qh = dbQuery( dbConnection,"CREATE TABLE IF NOT EXISTS scores (name text,Kills text,Deaths text)")
dbFree( qh )
    


function saveScore(sourcePlayer)
    local account = getPlayerAccount(sourcePlayer)
    local name = getAccountName(account)
    local Kills = getAccountData(account, "Kills")
    local Deaths = getAccountData(account, "Deaths") 
    local qh = dbQuery( dbConnection, "SELECT * FROM scores where name=?",name)
    local res = dbPoll(qh,-1) 
    dbFree( qh )
    if #res > 0 then
    dbExec( dbConnection, "UPDATE scores SET Deaths=? where name=? ", Deaths,name )
    dbExec( dbConnection, "UPDATE scores SET Kills=? where name=? ", Kills,name )
	outputChatBox ( "Saved account " .. name .. " with the Kills " .. Kills .. " with the Deaths" .. Deaths .. " to our database")
    else
    dbExec(dbConnection, "INSERT INTO scores VALUES(?, ?, ?)", name, Kills, Deaths)
	outputChatBox ( "Saved account " .. name .. " with the Kills " .. Kills .. " with the Deaths" .. Deaths .. " to our database")
    end
end
addCommandHandler("savestats", saveScore)

local function resetScores (element)
	setScoreData(element, "Kills", 0)
	setScoreData(element, "Deaths", 0)
	local status = ""
	if isPedDead(element) then
		status = "Dead"
	end
	setScoreData(element, "status", status)
end

local function updateRatio (element)
	local Deaths = getElementData(element, "Deaths")
	if Deaths == 0 then
		setScoreData(element, "ratio", "-")
	else
		local kdr = round(getElementData(element, "Kills") / Deaths, KDR_DECIMAL_PLACES)
		setScoreData(element, "ratio", tostring(kdr))
	end
end

function updateActiveColumns ()
	for i, column in ipairs(scoreColumns) do
		if get(column) then
			isColumnActive[column] = true
			exports.scoreboard:addScoreboardColumn(column)
		elseif isColumnActive[column] then
			isColumnActive[column] = false
			exports.scoreboard:removeScoreboardColumn(column)
		end
	end
end

addEventHandler("onResourceStart", scoresRoot,
	function ()
		updateActiveColumns()
		for i, player in ipairs(getElementsByType("player")) do
			resetScores(player)
		end
	end
)

addEventHandler("onResourceStop", scoresRoot,
	function ()
		for i, column in ipairs(scoreColumns) do
			if isColumnActive[column] then
				exports.scoreboard:removeScoreboardColumn(column)
			end
		end
	end
)

addEventHandler("onPlayerJoin", root,
	function ()
		resetScores(source)
	end
)

addEventHandler("onPlayerWasted", root,
	function (ammo, killer, weapon)
		if killer then
			if killer ~= source then
				-- killer killed victim
				setScoreData(killer, "Kills", getElementData(killer, "Kills") + 1)
				setScoreData(source, "Deaths", getElementData(source, "Deaths") + 1)
				if isColumnActive["ratio"] then
					updateRatio(killer)
					updateRatio(source)
				end
			else
				-- victim killed himself
				setScoreData(source, "self", getElementData(source, "self") + 1)
			end
		else
			-- victim died
			setScoreData(source, "Deaths", getElementData(source, "Deaths") + 1)
			if isColumnActive["ratio"] then
				updateRatio(source)
			end
		end
		
		setScoreData(source, "status", "Dead")
	end
)

addEventHandler("onPlayerSpawn", root,
	function ()
		setScoreData(source, "status", "")
	end
)

i need that it would save always without typing command like player died save to db

Edited December 16, 2017 by Dziugasc

Dimos7 · December 16, 2017

You can use the even onElementDataChange for that i think

Melbourne · December 16, 2017

And if you save the data when the player leaves the server?

Dziugasc · December 16, 2017

3 minutes ago, Melbourne said:

And if you save the data when the player leaves the server?

Good idea but can someone tell where i made mistake because it doesnt save kills or deaths maybe examples?

Dziugasc · December 16, 2017

I found kills but now i need deaths

local SQL = Connection("sqlite", "kills.db");
SQL:exec("CREATE TABLE IF NOT EXISTS player_kills (account_name varchar(255), account_kills INT)");


exports.scoreboard:scoreboardAddColumn("Kills", root, 120, "Kills");


local function getDatabaseAccountData(accName)
	if accName then

		local query = SQL:query("SELECT * FROM player_kills WHERE account_name=?", accName);
		local result = query:poll(-1);

		if result and type(result) == "table" then
			if (#result) > 0 then
				return result[1]["account_kills"];
			end
		end

	end

	return false;

end

local function setDatabaseAccountData(accName, kills)
	if accName and kills then

		local data = getDatabaseAccountData(accName)

		if data then
			SQL:exec("UPDATE player_kills SET account_kills=? WHERE account_name=?", kills, accName);
			return true;
		else
			SQL:exec("INSERT INTO player_kills (account_name, account_kills) VALUES (?,?)", accName, kills);
			return true;
		end

	end

	return false;

end


addEventHandler("onPlayerWasted", root,
	function(_, killer)
		if killer and killer ~= source then
			
			-- Incase a killer is a vehicle, check if there's a killer driver of it
			if killer:getType() == "vehicle" then
				if killer:getController() then
					killer = killer:getController();
				end
			end

			if killer:getType() ~= "player" then return end;

			if not killer.account:isGuest() then

				local currentStreak = tonumber(killer:getData("Kills")) or 0;

				killer:setData("Kills", currentStreak + 1);
				
			end


		end
	end
);

addEventHandler("onResourceStart", resourceRoot,
	function()

		for _, player in pairs(getElementsByType("player")) do

			local acc = player:getAccount()

			if not acc:isGuest() then

				local accName = acc:getName();
				local data = getDatabaseAccountData(accName);

				if data then
					player:setData("Kills", data);
				else
					player:setData("Kills", 0);
				end

			else
				player:setData("Kills", "Guest");
			end

		end

	end
);

addEventHandler("onPlayerJoin", root,
	function()
		source:setData("Kills", "Guest");
	end
);

addEventHandler("onPlayerLogin", root,
	function(_, acc)
		if not acc:isGuest() then
			
			local accName = acc:getName();

			local data = getDatabaseAccountData(accName);

			if data then
				source:setData("Kills", data);
			else
				source:setData("Kills", 0);
			end

		end
	end
);

addEventHandler("onPlayerLogout", root,
	function(acc)
		if not acc:isGuest() then
			
			local currentStreak = tonumber(source:getData("Kills")) or 0;
			local accName = acc:getName();

			setDatabaseAccountData(accName, currentStreak);

			source:setData("Kills", 0);

		end
	end
);

addEventHandler("onPlayerQuit", root,
	function()
		
		local acc = source:getAccount()
		
		if acc and not acc:isGuest() then

			local currentStreak = tonumber(source:getData("Kills")) or 0;
			local accName = acc:getName();

			setDatabaseAccountData(accName, currentStreak);

		end

	end
);

Melbourne · December 16, 2017

Try this, it should work:

local SQL = Connection("sqlite", "kills.db");
SQL:exec("CREATE TABLE IF NOT EXISTS player_stats (account_name varchar(255), account_kills INT, account_deaths INT)");

exports.scoreboard:scoreboardAddColumn("Kills", root, 120, "Kills");
exports.scoreboard:scoreboardAddColumn("Deaths", root, 120, "Deaths");

local function getDatabaseAccountData(accName)
	if accName then
		local query = SQL:query("SELECT * FROM player_stats WHERE account_name=?", accName);
		local result = query:poll(-1);
		if result and type(result) == "table" then
			if (#result) > 0 then
				return result[1]["account_kills"], result[1]['account_deaths'];
			end
		end
	end
	return false;
end
local function setDatabaseAccountData(accName, kills, deaths)
	if accName and kills and deaths then
		local data = getDatabaseAccountData(accName)
		if data then
			SQL:exec("UPDATE player_stats SET account_kills=?, account_deaths=? WHERE account_name=?", kills, deaths, accName);
			return true;
		else
			SQL:exec("INSERT INTO player_stats (account_name, account_kills, account_deaths) VALUES (?,?,?)", accName, kills, deaths);
			return true;
		end
	end
	return false;
end
addEventHandler("onPlayerWasted", root,
	function(_, killer)
		if killer and killer ~= source then
			
			-- Incase a killer is a vehicle, check if there's a killer driver of it
			if killer:getType() == "vehicle" then
				if killer:getController() then
					killer = killer:getController();
				end
			end
			if killer:getType() ~= "player" then return end;
			if not killer.account:isGuest() then
				local currentStreak = tonumber(killer:getData("Kills")) or 0;
				killer:setData("Kills", currentStreak + 1);
			end
		end
		local currentStreak2 = tonumber(source:getData("Deaths")) or 0;
		source:setData("Deaths", currentStreak2 + 1);
		
	end
);

addEventHandler("onResourceStop", resourceRoot,
	function()
		for _, player in pairs(getElementsByType("player")) do
			local acc = player:getAccount()
			if not acc:isGuest() then
				local currentStreak = tonumber(player:getData("Kills")) or 0;
				local currentStreak2 = tonumber(player:getData("Deaths")) or 0;
				local accName = acc:getName();
				setDatabaseAccountData(accName, currentStreak, currentStreak2);
				player:setData("Kills", 0);
				player:setData("Deaths", 0);
			end
		end
	end
);

addEventHandler("onResourceStart", resourceRoot,
	function()
		for _, player in pairs(getElementsByType("player")) do
			local acc = player:getAccount()
			if not acc:isGuest() then
				local accName = acc:getName();
				local dataKills, dataDeaths = getDatabaseAccountData(accName);
				if dataKills and dataDeaths then
					player:setData("Kills", dataKills);
					player:setData("Deaths", dataDeaths);
				else
					player:setData("Kills", 0);
					player:setData("Deaths", 0);
				end
			else
				player:setData("Deaths", "Guest");
			end
		end
	end
);

addEventHandler("onPlayerJoin", root,
	function()
		source:setData("Kills", "Guest");
		source:setData("Deaths", "Guest");
	end
);
addEventHandler("onPlayerLogin", root,
	function(_, acc)
		if not acc:isGuest() then
			
			local accName = acc:getName();
			local dataKills, dataDeaths = getDatabaseAccountData(accName);
			if dataKills and dataDeaths then
				source:setData("Kills", dataKills);
				source:setData("Deaths", dataDeaths);
			else
				source:setData("Kills", 0);
				source:setData("Deaths", 0);
			end
		end
	end
);
addEventHandler("onPlayerLogout", root,
	function(acc)
		if not acc:isGuest() then
			
			local currentStreak = tonumber(source:getData("Kills")) or 0;
			local currentStreak2 = tonumber(source:getData("Deaths")) or 0;
			local accName = acc:getName();
			setDatabaseAccountData(accName, currentStreak, currentStreak2);
			source:setData("Kills", 0);
			source:setData("Deaths", 0);
		end
	end
);
addEventHandler("onPlayerQuit", root,
	function()
		
		local acc = source:getAccount()
		
		if acc and not acc:isGuest() then
			local currentStreak = tonumber(source:getData("Kills")) or 0;
			local currentStreak22 = tonumber(source:getData("Deaths")) or 0;
			local accName = acc:getName();
			setDatabaseAccountData(accName, currentStreak, currentStreak2);
		end
	end
);

Dziugasc · December 16, 2017

1 hour ago, Melbourne said:

Try this, it should work:


local SQL = Connection("sqlite", "kills.db");SQL:exec("CREATE TABLE IF NOT EXISTS player_stats (account_name varchar(255), account_kills INT, account_deaths INT)");exports.scoreboard:scoreboardAddColumn("Kills", root, 120, "Kills");exports.scoreboard:scoreboardAddColumn("Deaths", root, 120, "Deaths");local function getDatabaseAccountData(accName)	if accName then		local query = SQL:query("SELECT * FROM player_stats WHERE account_name=?", accName);		local result = query:poll(-1);		if result and type(result) == "table" then			if (#result) > 0 then				return result[1]["account_kills"], result[1]['account_deaths'];			end		end	end	return false;endlocal function setDatabaseAccountData(accName, kills, deaths)	if accName and kills and deaths then		local data = getDatabaseAccountData(accName)		if data then			SQL:exec("UPDATE player_stats SET account_kills=?, account_deaths=? WHERE account_name=?", kills, deaths, accName);			return true;		else			SQL:exec("INSERT INTO player_stats (account_name, account_kills, account_deaths) VALUES (?,?,?)", accName, kills, deaths);			return true;		end	end	return false;endaddEventHandler("onPlayerWasted", root,	function(_, killer)		if killer and killer ~= source then						-- Incase a killer is a vehicle, check if there's a killer driver of it			if killer:getType() == "vehicle" then				if killer:getController() then					killer = killer:getController();				end			end			if killer:getType() ~= "player" then return end;			if not killer.account:isGuest() then				local currentStreak = tonumber(killer:getData("Kills")) or 0;				killer:setData("Kills", currentStreak + 1);			end		end		local currentStreak2 = tonumber(source:getData("Deaths")) or 0;		source:setData("Deaths", currentStreak2 + 1);			end);addEventHandler("onResourceStop", resourceRoot,	function()		for _, player in pairs(getElementsByType("player")) do			local acc = player:getAccount()			if not acc:isGuest() then				local currentStreak = tonumber(player:getData("Kills")) or 0;				local currentStreak2 = tonumber(player:getData("Deaths")) or 0;				local accName = acc:getName();				setDatabaseAccountData(accName, currentStreak, currentStreak2);				player:setData("Kills", 0);				player:setData("Deaths", 0);			end		end	end);addEventHandler("onResourceStart", resourceRoot,	function()		for _, player in pairs(getElementsByType("player")) do			local acc = player:getAccount()			if not acc:isGuest() then				local accName = acc:getName();				local dataKills, dataDeaths = getDatabaseAccountData(accName);				if dataKills and dataDeaths then					player:setData("Kills", dataKills);					player:setData("Deaths", dataDeaths);				else					player:setData("Kills", 0);					player:setData("Deaths", 0);				end			else				player:setData("Deaths", "Guest");			end		end	end);addEventHandler("onPlayerJoin", root,	function()		source:setData("Kills", "Guest");		source:setData("Deaths", "Guest");	end);addEventHandler("onPlayerLogin", root,	function(_, acc)		if not acc:isGuest() then						local accName = acc:getName();			local dataKills, dataDeaths = getDatabaseAccountData(accName);			if dataKills and dataDeaths then				source:setData("Kills", dataKills);				source:setData("Deaths", dataDeaths);			else				source:setData("Kills", 0);				source:setData("Deaths", 0);			end		end	end);addEventHandler("onPlayerLogout", root,	function(acc)		if not acc:isGuest() then						local currentStreak = tonumber(source:getData("Kills")) or 0;			local currentStreak2 = tonumber(source:getData("Deaths")) or 0;			local accName = acc:getName();			setDatabaseAccountData(accName, currentStreak, currentStreak2);			source:setData("Kills", 0);			source:setData("Deaths", 0);		end	end);addEventHandler("onPlayerQuit", root,	function()				local acc = source:getAccount()				if acc and not acc:isGuest() then			local currentStreak = tonumber(source:getData("Kills")) or 0;			local currentStreak22 = tonumber(source:getData("Deaths")) or 0;			local accName = acc:getName();			setDatabaseAccountData(accName, currentStreak, currentStreak2);		end	end);

Thanks you helped me

Sign In

[HELP] problem with k/d saving to sqlite

Recommended Posts

Dziugasc

Link to comment

Dimos7

Link to comment

Melbourne

Link to comment

Dziugasc

Link to comment

Dziugasc

Link to comment

Melbourne

Link to comment

Dziugasc

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

Activity

MTA Network

Social Media