Sign in to follow this  
DriFtyZ

How to intergrate databases and read write create tables in them

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

Share this post


Link to post

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 .

Share this post


Link to post

read:

wiki.mtasa.com/dbExec

wiki.mtasa.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.

Share this post


Link to post

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)

Share this post


Link to post

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 

Share this post


Link to post
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)

Share this post


Link to post

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 

 

Share this post


Link to post

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)

 

Share this post


Link to post
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

Share this post


Link to post

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)

 

Share this post


Link to post

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)

:)

 

Share this post


Link to post

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

Share this post


Link to post
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)

 

Share this post


Link to post
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 by Ayush Rathore

Share this post


Link to post
 
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

Share this post


Link to post

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

Share this post


Link to post

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

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.