x[Question]x SQL LITE

[ABoHussin]

HI All

i have made a code for save players dd score in sqllite

the code :- 

DerbyDB = dbConnect("sqlite", "DD.db")
dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")
local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( element ) , getPlayerSerial( element ) )
local results = dbPoll( check, -1 )
if ( type( results ) == 'table' and #results == 0 or not results ) then
exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(element) , getPlayerSerial(element), 0 , 0 )

Everything is good so far . but i want code to select DDscore 
by get player sieral  . and when the player does something I want Increase 1 on his DDscore any help ? 

 

Increase 1

Increase 1

 

He does something I want

He does something I want

 

an

 

 

 

 

 

 

 

 

[SycroX]

first request

function getSerialDDScore(serial)
	local result = dbPoll(dbQuery(DerbyDB, "SELECT DDscore FROM DerbyMission WHERE DDserial = ?", serial), -1)
	if type(result) == "table" and #result ~= 0 then
		return tonumber(result[1])
	end
end

ican't help you in the second request cause i don't understand what you want :-

[ABoHussin]

20 minutes ago, #x1AhMeD,-09 said:

first request

function getSerialDDScore(serial)
	local result = dbPoll(dbQuery(DerbyDB, "SELECT DDscore FROM DerbyMission WHERE DDserial = ?", serial), -1)
	if type(result) == "table" and #result ~= 0 then
		return tonumber(result[1])
	end
end

ican't help you in the second request cause i don't understand what you want :-

i mean whenever player wins the derby 

give him +1 point on his (DDscore )

I explain to you more :-

when the first join player DDscore = 0

and if he wins in derby then +1 on his DDscore in sql

to be ( 1 )

and if he wins again then +1 on his score 

to be ( 2 )

I hope you understand ..

[Rose]

You can create a function like this: 

DerbyDB = dbConnect("sqlite", "DD.db")
dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")

function setScore( element, score, ddban )
    local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( element ) , getPlayerSerial( element ))
    local results = dbPoll( check, -1 )
      if ( type( results ) == 'table' and #results == 0 or not results ) then
          exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
          dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(element) , getPlayerSerial(element), score, ddban )
      else 
          dbExec( DerbyDB, "UPDATE DerbyMission SET DDname=?, DDscore=?, DDban=? WHERE DDserial=?", getPlayerName( element ), score, ddban, getPlayerSerial( element ) )
      end 
end 

not tested

Edited August 21, 2017 by Rose

[kikos500]

local DerbyDB = dbConnect("sqlite", "DD.db")
local accounts = {}

function resourceStart()
	dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")
	for i,v in pairs(getElementsByType("player"))
		loadAccount(v)
	end
end
addEventHandler("onResourceStart", resourceRoot, resourceStart)

function resourceStop()
	for i,v in pairs(getElementsByType("player"))
		saveAccount(v)
	end	
end
addEventHandler("onResourceStop", resourceRoot, resourceStop)

function join()
	loadAccount(source)
end
addEventHandler("onPlayerJoin", getRootElement(), resourceStop)

function leave()
	saveAccount(source)
end
addEventHandler("onPlayerQuit", getRootElement(), resourceStop)

function saveAccount(player)
	if not accounts[player] then return end
	dbExec("UPDATE `DerbyMission` SET DDscore = ?, DDban = ? WHERE DDname = ? AND DDserial = ? ", accounts[player].DDscore, accounts[player].DDban, getPlayerName(player), getPlayerSerial(player))
end

function loadAccount(player)
	local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( player ) , getPlayerSerial( player ) )
	local results = dbPoll( check, -1 )
	accounts[getPlayerSerial(player)] = {}
	accounts[getPlayerSerial(player)].serial = getPlayerSerial(player)
	accounts[getPlayerSerial(player)].name = getPlayerName(player)
	if ( type( results ) == 'table' and #results == 0 or not results ) then
		exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
		dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(player) , getPlayerSerial(player), 0 , 0 )
		accounts[getPlayerSerial(player)].DDscore = 0
		accounts[getPlayerSerial(player)].DDban = 0
	else
		accounts[getPlayerSerial(player)].DDscore = results[1].DDscore
		accounts[getPlayerSerial(player)].DDban = results[1].DDban
	end
end

function setScore(p, DDscore, DDban)
	if accounts[getPlayerSerial(p)] and type(accounts[getPlayerSerial(p)]) == "table" then
		if DDscore then
			accounts[getPlayerSerial(p)].DDscore = DDscore
		end
		if DDban then
			accounts[getPlayerSerial(p)].DDban = DDban
		end
	end
end

should work not tested

[ABoHussin]

1 hour ago, kikos500 said:

local DerbyDB = dbConnect("sqlite", "DD.db")
local accounts = {}

function resourceStart()
	dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")
	for i,v in pairs(getElementsByType("player"))
		loadAccount(v)
	end
end
addEventHandler("onResourceStart", resourceRoot, resourceStart)

function resourceStop()
	for i,v in pairs(getElementsByType("player"))
		saveAccount(v)
	end	
end
addEventHandler("onResourceStop", resourceRoot, resourceStop)

function join()
	loadAccount(source)
end
addEventHandler("onPlayerJoin", getRootElement(), resourceStop)

function leave()
	saveAccount(source)
end
addEventHandler("onPlayerQuit", getRootElement(), resourceStop)

function saveAccount(player)
	if not accounts[player] then return end
	dbExec("UPDATE `DerbyMission` SET DDscore = ?, DDban = ? WHERE DDname = ? AND DDserial = ? ", accounts[player].DDscore, accounts[player].DDban, getPlayerName(player), getPlayerSerial(player))
end

function loadAccount(player)
	local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( player ) , getPlayerSerial( player ) )
	local results = dbPoll( check, -1 )
	accounts[getPlayerSerial(player)] = {}
	accounts[getPlayerSerial(player)].serial = getPlayerSerial(player)
	accounts[getPlayerSerial(player)].name = getPlayerName(player)
	if ( type( results ) == 'table' and #results == 0 or not results ) then
		exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
		dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(player) , getPlayerSerial(player), 0 , 0 )
		accounts[getPlayerSerial(player)].DDscore = 0
		accounts[getPlayerSerial(player)].DDban = 0
	else
		accounts[getPlayerSerial(player)].DDscore = results[1].DDscore
		accounts[getPlayerSerial(player)].DDban = results[1].DDban
	end
end

function setScore(p, DDscore, DDban)
	if accounts[getPlayerSerial(p)] and type(accounts[getPlayerSerial(p)]) == "table" then
		if DDscore then
			accounts[getPlayerSerial(p)].DDscore = DDscore
		end
		if DDban then
			accounts[getPlayerSerial(p)].DDban = DDban
		end
	end
end

should work not tested

Man, this code is full of problems

6 hours ago, Rose said:

You can create a function like this: 

DerbyDB = dbConnect("sqlite", "DD.db")
dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")

function setScore( element, score, ddban )
    local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( element ) , getPlayerSerial( element ))
    local results = dbPoll( check, -1 )
      if ( type( results ) == 'table' and #results == 0 or not results ) then
          exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
          dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(element) , getPlayerSerial(element), score, ddban )
      else 
          dbExec( DerbyDB, "UPDATE DerbyMission SET DDname=?, DDscore=?, DDban=? WHERE DDserial=?", getPlayerName( element ), score, ddban, getPlayerSerial( element ) )
      end 
end 

not tested

ty ..

What I do not understand is how to increase 1 on his DDscore
 

Edited August 21, 2017 by ABoHussin

[kikos500]

local accounts = {}
local DerbyDB = dbConnect("sqlite", "DD.db")

function resourceStart()
	dbExec(DerbyDB, "CREATE TABLE IF NOT EXISTS DerbyMission (DDname, DDserial, DDscore INT , DDban )")
	for i,v in pairs(getElementsByType("player")) do
		loadAccount(v)
	end
end
addEventHandler("onResourceStart", resourceRoot, resourceStart)

function resourceStop()
	for i,v in pairs(getElementsByType("player")) do
		saveAccount(v)
	end	
end
addEventHandler("onResourceStop", resourceRoot, resourceStop)

function join()
	loadAccount(source)
end
addEventHandler("onPlayerJoin", getRootElement(), join)

function leave()
	saveAccount(source)
end
addEventHandler("onPlayerQuit", getRootElement(), leave)

function saveAccount(player)
	if not accounts[getPlayerSerial(player)] then return end
	dbExec(DerbyDB, "UPDATE `DerbyMission` SET DDscore = ?, DDban = ? WHERE DDname = ? AND DDserial = ? ", accounts[getPlayerSerial(player)].DDscore, accounts[getPlayerSerial(player)].DDban, getPlayerName(player), getPlayerSerial(player))
end

function loadAccount(player)
	local check = dbQuery( DerbyDB, ' SELECT * FROM `DerbyMission` WHERE DDname = ? AND DDserial = ? ', getPlayerName( player ) , getPlayerSerial( player ) )
	local results = dbPoll( check, -1 )
	accounts[getPlayerSerial(player)] = {}
	accounts[getPlayerSerial(player)].serial = getPlayerSerial(player)
	accounts[getPlayerSerial(player)].name = getPlayerName(player)
	if ( type( results ) == 'table' and #results == 0 or not results ) then
		exports ["guimessages"] : outputServer (element, "* [ Derby ]: This is the first join" , 204, 51, 255) 
		dbExec( DerbyDB, "INSERT INTO DerbyMission VALUES (?,?,?,?)", getPlayerName(player) , getPlayerSerial(player), 0 , 0 )
		accounts[getPlayerSerial(player)].DDscore = 0
		accounts[getPlayerSerial(player)].DDban = 0
		return
	end
	accounts[getPlayerSerial(player)].DDscore = results[1].DDscore
	accounts[getPlayerSerial(player)].DDban = results[1].DDban
end

function getScore(p)
	return accounts[getPlayerSerial(p)].DDscore
end

function setScore(p, amount)
	accounts[getPlayerSerial(p)].DDscore = tonumber(amount)
end


function getBan(p)
	return accounts[getPlayerSerial(p)].DDban
end

function setBan(p, amount)
	accounts[getPlayerSerial(p)].DDban = tonumber(amount)
end

tested and works

Functions:

setScore(player, amount) to edit the score to a certain amount

getScore(player) to get the score

setBan(player, amount) to edit the ban to a certain amount

getBan(player) to get the ban