Jump to content

How to intergrate databases and read write create tables in them


DriFtyZ

Recommended Posts

Hello fellows how are you ?
Today i want to use database connections Into a simple script that im gonna make about user settings

The script is simple a gui opens up and the user selects if he wants to enable disable some graphical settings (car reflection, water, detail, contrast, show fps counter etc blah blah)

Script is made and works perfect but i want to save each user settings to database(sqlite for now since i don't want to have remote control of database for now so mysql is not needed) so when he logs on to server the settings he set before will be applied, and also if hes new user server will make a new list in database for that user with default settings (1(enable) for each setting))

anyway i don't need you to tell me how exaclty im gonna code that cause you will get through much work but i want to give me the idea how the hell am i gonna do this because the wiki is helpless in these things 

if you want some example how bad i am in scripting sql see below:

playerSettings = {}

function playerJoin()

	local settingsQuery = dbQuerry(connectionDb,"select * from settings")
	local settingsQueryResult = dbPoll(settingsQuery, -1)
	for i, user in pairs(settingsQueryResult) do
		local playerName = getAccountName(source)
		table.insert(playerSettings,playerName) 
	end
end
addEventHandler("onPlayerJoin", resourceRoot, playerJoin)

thanks in advance

Link to comment

read:

wiki.multitheftauto.com/dbExec

wiki.multitheftauto.com/dbQuery

 

The normal and exec queries, SELECT * tableName WHERE acc=?, acc..etc.

It is practice that will get you used to this.

Do not forget to connect to a database through dbConnect and then create it with dbExec

dbExec
dbQuery
dbConnect
onPlayerLogin
getPlayerAccount
getAccountName
-- These are all the functions you'll need mandatorily!

PM me for help, I'll send my skype.

Link to comment

You can connect to SQLite database (it will automatically make the file, if it it doesn't exist) through dbConnect.

dbQuery examples show how to use SELECT query on SQLite database, and dbExec UPDATE, INSERT, CREATE queries on database.

dbConnect basically links you to the database so you can fiddle with it 

Link to comment
function playerLogin(source)
	local playerAcc = getPlayerAccount(source)
	local accName = getAccountName(source)
	local playerQuery = dbQuerry(connectionDb,"SELECT * from settings WHERE playerName=?", ..accName..)
	local playerQueryResult = dbPoll(playerQuery, -1)
	if playerQueryResult == nil then
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings VALUES (playerName,carReflect,water,contrast,detail,sky,showFps)", ""..accName"" , 1, 1, 1, 1, 1, 1 )
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

it doesn't make any sense to me, i know its wrong but i can't figure this out (from sql part side)

Link to comment

Replace 6th line with:
if #playerQueryResult == 0

dbPoll returns a result, in a table. If the table is empty, there is nothing.

Anyways, can you show the full script, where you add the table? Make sure that is done.

I see nothing wrong here
Use a else on that if to load your settings 

 

Link to comment

full script is this
 

addEventHandler("onResourceStart", resourceRoot,
	function (resource)
		connectionDb = dbConnect("sqlite", "user_settings.db")
		if connectionDb then
			outputDebugString( "Connection with database was successfully established." )
			dbExec(connectionDb,"create table if not exists 'settings'(id INTEGER PRIMARY KEY, playerName TEXT, carReflect NUMBER, water NUMBER, contrast NUMBER, detail NUMBER, sky NUMBER, showFps NUMBER)")
		else
			outputDebugString( "Connection with database couldn't be established." )
		end
	end
)


function playerLogin(source)
	local playerAcc = getPlayerAccount(source)
	local accName = getAccountName(source)
	local playerQuery = dbQuerry(connectionDb,"SELECT * FROM settings WHERE playerName='..accName..'")
	local playerQueryResult = dbPoll(playerQuery, -1)
	if playerQueryResult == nil then
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES ('..accName..', 1, 1, 1, 1, 1, 1")
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

 

Link to comment
29 minutes ago, DriFtyZ said:

full script is this
 


addEventHandler("onResourceStart", resourceRoot,
	function (resource)
		connectionDb = dbConnect("sqlite", "user_settings.db")
		if connectionDb then
			outputDebugString( "Connection with database was successfully established." )
			dbExec(connectionDb,"create table if not exists 'settings'(id INTEGER PRIMARY KEY, playerName TEXT, carReflect NUMBER, water NUMBER, contrast NUMBER, detail NUMBER, sky NUMBER, showFps NUMBER)")
		else
			outputDebugString( "Connection with database couldn't be established." )
		end
	end
)


function playerLogin(source)
	local playerAcc = getPlayerAccount(source)
	local accName = getAccountName(source)
	local playerQuery = dbQuerry(connectionDb,"SELECT * FROM settings WHERE playerName='..accName..'")
	local playerQueryResult = dbPoll(playerQuery, -1)
	if playerQueryResult == nil then
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES ('..accName..', 1, 1, 1, 1, 1, 1")
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

 

Selling mistake see 

function playerLogin(source)
	local playerAcc = getPlayerAccount(source)
	local accName = getAccountName(source)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName='..accName..'")
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES ('..accName..', 1, 1, 1, 1, 1, 1")
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

its dbQuery not dbQuerry :)

 

Edited by Ayush Rathore
Link to comment

well i made some changes and still still doesn't work(to add values in database if acc does not exist)
new script as follow:
 

function playerLogin(player)
	local playerAcc = getPlayerAccount(player)
	local accName = getAccountName(player)
	local playerQuerry = dbQuerry(connectionDb,"SELECT * from settings WHERE playerName=?", accName)
	local playerQuerryResult = dbPoll(playerQuerry, -1)
	if #playerQuerryResult == 0 then
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES (?,?,?,?,?,?,?)", accName, 1, 1, 1, 1, 1, 1 )
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

 

Link to comment

also u should use some debugging statement like this 

function playerLogin(source)
	local playerAcc = getPlayerAccount(source)
	local accName = getAccountName(source)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName='..accName..'")
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then
        outputDebugString(getPlayerName(source).." Creating new settings")
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES ('..accName..', 1, 1, 1, 1, 1, 1")
    if createPlayerIndex then
        outputDebugString(getPlayerName(source).." Creating new settings went successful.")      
    else
        outputDebugString(getPlayerName(source).." Creating new settings failed check your db connection.")
    end
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

:)

 

Link to comment

okay this :~ starts confuses me its not adding things to database and its not debuging anything at all ffs 
 

function playerLogin(player)
	local playerAcc = getPlayerAccount(player)
	local accName = getAccountName(player)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName=?", accName)
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then
        outputDebugString(getPlayerName(player).." Creating new settings")
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES (?,?,?,?,?,?,?)", accName, 1, 1, 1, 1, 1, 1)
    if createPlayerIndex then
        outputDebugString(getPlayerName(player).." Creating new settings went successful.")      
    else
        outputDebugString(getPlayerName(player).." Creating new settings failed check your db connection.")
    end
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

i fixed some things and still..

Edited by DriFtyZ
Link to comment
14 minutes ago, DriFtyZ said:

okay this :~ starts confuses me its not adding things to database and its not debuging anything at all ffs 
 


function playerLogin(player)
	local playerAcc = getPlayerAccount(player)
	local accName = getAccountName(player)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName=?", accName)
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then
        outputDebugString(getPlayerName(player).." Creating new settings")
		local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings (playerName,carReflect,water,contrast,detail,sky,showFps) VALUES (?,?,?,?,?,?,?)", accName, 1, 1, 1, 1, 1, 1)
    if createPlayerIndex then
        outputDebugString(getPlayerName(player).." Creating new settings went successful.")      
    else
        outputDebugString(getPlayerName(player).." Creating new settings failed check your db connection.")
    end
	end
end
addEventHandler("onPlayerLogin", resourceRoot, playerLogin)

i fixed some things and still..

Try this 

 
addEventHandler("onResourceStart", resourceRoot,
	function (resource)
		connectionDb = dbConnect("sqlite", "user_settings.db")
		if connectionDb then
			outputDebugString( "Connection with database was successfully established." )
			dbExec(connectionDb,"create table if not exists settings(id INTEGER PRIMARY KEY, playerName TEXT, carReflect NUMBER, water NUMBER, contrast NUMBER, detail NUMBER, sky NUMBER, showFps NUMBER)")
		else
			outputDebugString( "Connection with database couldn't be established." )
		end
	end
)

function playerLogin(_,acc)
	local accName = getAccountName(acc)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName=?",accName)
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then
    local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings VALUES(?,?,?,?,?,?,?,?)",1,1,1,1,1,1,1,1)
	end
end
addEventHandler("onPlayerLogin", getRootElement(), playerLogin)

 

Link to comment
 
addEventHandler("onResourceStart", resourceRoot,
	function (resource)
		connectionDb = dbConnect("sqlite", "user_settings.db")
		if connectionDb then
			outputDebugString( "Connection with database was successfully established." )
			dbExec(connectionDb,"create table if not exists settings(playerName TEXT, carReflect NUMBER, water NUMBER, contrast NUMBER, detail NUMBER, sky NUMBER, showFps NUMBER)")
		else
			outputDebugString( "Connection with database couldn't be established." )
		end
	end
)

function playerLogin(_,acc)
	local accName = getAccountName(acc)
	local playerQuery = dbQuery(connectionDb,"SELECT * FROM settings WHERE playerName=?",accName)
	local playerQueryResult = dbPoll(playerQuery, -1)
	if #playerQueryResult == 0 then -- if he is new user
    local createPlayerIndex = dbExec(connectionDb, "INSERT INTO settings VALUES(?,?,?,?,?,?,?)",accName,1,1,1,1,1,1)
	else -- if he is old user then load here 
 	-- do your stuff	
   for rid, row in pairs (playerQueryResult) do -- row represents the tables that are in 'playerQueryResult', which represent the rows
    for column, value in pairs (row) do -- column represents column name, value represents column value
        outputChatBox(column..', '..value)
    end
   end
	end
end
addEventHandler("onPlayerLogin", getRootElement(), playerLogin)


run this thing :) you will get to know everything 

btw delete your old user_settings.db

@DriFtyZ 

 

@DriFtyZ login logout or reconnect and login to check it  

  • Like 1
Link to comment

it is working but i have some questions
1. what are these _,acc function parameters used for?

2. these row,column tables lines, you made them so it will show in the chatbox the tables but chatbox doesn't show up anything

Link to comment

https://wiki.multitheftauto.com/wiki/OnPlayerLogin  go there you will get to know why i used this _,acc thingy and also to see that table thingy you need to logout and login again

Just now, Ayush Rathore said:

https://wiki.multitheftauto.com/wiki/OnPlayerLogin  go there you will get to know why i used this _,acc thingy and also to see that table thingy you need to logout and login again

 

29 minutes ago, DriFtyZ said:

it is working but i have some questions
1. what are these _,acc function parameters used for?

2. these row,column tables lines, you made them so it will show in the chatbox the tables but chatbox doesn't show up anything

 

  • Like 1
Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...