How to intergrate databases and read write create tables in them

DriFtyZ · May 21, 2017

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

Anubhav · May 21, 2017

When a player log ins, get his acc, then his acc name.

Proceed for a SELECT query towards a poll

If there the poll returns 0 results with that acc name -> INSERT one statement, and then put in default settings.

Otherwise load it .

DriFtyZ · May 21, 2017

i understand what you mean but can you be a bit more specific about the select, insert

Anubhav · May 21, 2017

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.

DriFtyZ · May 21, 2017

wikies dbExec and etc they just explain how to use that specific function but INSERT SELECT commands are something related to sql that wiki doens't explain about i tihnk
(ill send you pm)

Anubhav · May 21, 2017

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

DriFtyZ · May 21, 2017

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)

Anubhav · May 21, 2017

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

DriFtyZ · May 21, 2017

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)

Ayush Rathore · May 21, 2017

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 May 21, 2017 by Ayush Rathore

DriFtyZ · May 21, 2017

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)

Ayush Rathore · May 21, 2017

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)

DriFtyZ · May 21, 2017

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 May 21, 2017 by DriFtyZ

Ayush Rathore · May 21, 2017

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)

DriFtyZ · May 21, 2017

if i try that how would the script add the account name in the first index?

Ayush Rathore · May 21, 2017

1 minute ago, DriFtyZ said:

if i try that how would the script add the account name in the first index?

your first column is of type integer so how could you write a text in it ?

Edited May 21, 2017 by Ayush Rathore

DriFtyZ · May 21, 2017

forget the first column i mean the playerName column

Ayush Rathore · May 21, 2017

 
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

DriFtyZ · May 21, 2017

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

Ayush Rathore · May 21, 2017

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

DriFtyZ · May 21, 2017

i did logged in and out many times everything works except the chatbox

How to intergrate databases and read write create tables in them

Recommended Posts

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Link to comment

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members