Jump to content

SQL Problem

Dimos7

By Dimos7
in Scripting

Recommended Posts

Dimos7 Loc

Dimos7

Posted
Posted (edited) 
local connection =dbConnect("sqllitle", "dbsql/database.db") 
  
function passwordHandler(player, oldpassword, newpassword) 
    local query = dbQuery(connection, "SELECT * FROM accoutns WHERE password=?", oldpassword) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getPlayerAccount(player) 
    if (account) then 
        if (isGuestAccount(account)) then 
            outputChatBox("You must be logged in to change your password.", player) 
            return 
        end 
        local playerName = getPlayerName(player) 
        local password_check = getAccount(playerName, oldpassword) 
        if (password_check ~= false) and num_affected_rows < 1 then 
            if (string.len(newpassword) >= 5) then 
                setAccountPassword(account, newpassword) 
                triggerClientEvent(player, "hidePasswordWindow", getRootElement()) 
                dbExec(connection, "UPDATE accoutns SET password=?", newpassword) 
            else 
                outputChatBox("Your new password must be at least 5 characters long!", player) 
            end 
        else 
            outputChatBox("Old password invalid.", player) 
        end 
    end 
end 
  
  
  
function loginHandler(player, username, password, thePlayer) 
    local query = dbQuery (connection, "SELECT * FROM accoutns WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false) and num_affected_rows < 1 then 
        if (logIn(player, account, password) == true) then 
            triggerClientEvent (player, "hideLoginWindow", getRootElement()) 
        else 
            triggerClientEvent (player, "unknownError", getRootElement()) 
        end 
    else 
        triggerClientEvent (player, "loginWrong", getRootElement()) 
    end 
end 
  
function registerHandler(player, username, password) 
    local query = dbQuery(connection, "SELECT * FROM accoutns WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false)  and num_affected_rows > 1 then 
  
        triggerClientEvent(player, "registerTaken", getRootElement()) 
    else 
        account = addAccount(username, password) 
        dbExec(connection,"INSERT INTO accounts VALUES(?, ?)", username, password) 
        if (logIn(player, account, password) == true) then 
            
            triggerClientEvent(player, "hideLoginWindow", getRootElement()) 
      
    else 
            triggerClientEvent(player, "unknownError", getRootElement()) 
        end 
    end 
end 
  
  
local camtable = { 
{3074.04468, -3041.53442, 69.20741, 3099.33130, -3110.32227, 42.89851}, 
{-305.43381, 1349.10864, 84.20972, -329.04495, 1535.28113, 75.56250}, 
{1266.68579, -2064.46631, 85.27428, 1190.04834, -2036.79968, 69.00781}, 
{1543.23889, -2382.14258, 56.26112, 1497.80872, -2309.76709, 25.14184}, 
{-1590.68567, 592.59760, 52.45333, -1522.07080, 674.48999, 52.22255} 
} 
  
  
function setCameraOnPlayerJoin() 
     fadeCamera(source, true, 5) 
setTimer ( fadeCamera, 5000, 1, source, true, 0.5 )     
  
joincampos = math.random(#camtable) 
lx, ly, lz, wx, wy, wz = camtable[joincampos][1], camtable[joincampos][2], camtable[joincampos][3], camtable[joincampos][4], camtable[joincampos][5], camtable[joincampos][6] 
setCameraMatrix(source, lx, ly, lz, wx, wy, wz) 
  
  
end 
addEventHandler("onPlayerJoin", getRootElement(), setCameraOnPlayerJoin) 
  
  
  
  
  
  
  
  
function setCameraRegister(player, thePlayer) 
setTimer( setCameraTarget,1200,1, source ) 
setElementFrozen ( source, false ) 
end 
  
  
function onJoin() 
showChat(source, false) 
setPlayerHudComponentVisible(source, "all", false) 
end 
addEventHandler("onPlayerJoin", root, onJoin) 
  
  
function onSpawn() 
setPlayerHudComponentVisible(source, "all", true) 
showChat(source, true) 
showCursor(source, false) 
end 
addEventHandler("onPlayerSpawn", root, onSpawn) 
  
  
  
addEvent("setCameraRegister", true) 
addEvent("submitChangepw", true) 
addEvent("submitLogin", true) 
addEvent("submitRegister", true) 
addEventHandler("submitChangepw", root, passwordHandler) 
addEventHandler("submitLogin", root, loginHandler) 
addEventHandler("submitRegister", root, registerHandler) 
addEventHandler("setCameraRegister", root, setCameraRegister)

Edited by Guest
Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted 
local connection =dbConnect("sqllitle", "dbsql/database.db") 
  
function passwordHandler(player, oldpassword, newpassword) 
    local query = dbQuery(connection, "SELECT * FROM accoutns WHERE password=?", oldpassword) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getPlayerAccount(player) 
    if (account) then 
        if (isGuestAccount(account)) then 
            outputChatBox("You must be logged in to change your password.", player) 
            return 
        end 
        local playerName = getPlayerName(player) 
        local password_check = getAccount(playerName, oldpassword) 
        if (password_check ~= false) and num_affected_rows < 1 then 
            if (string.len(newpassword) >= 5) then 
                setAccountPassword(account, newpassword) 
                triggerClientEvent(player, "hidePasswordWindow", getRootElement()) 
                dbExec(connection, "UPDATE accoutns SET password=?", newpassword) 
            else 
                outputChatBox("Your new password must be at least 5 characters long!", player) 
            end 
        else 
            outputChatBox("Old password invalid.", player) 
        end 
    end 
end 
  
  
  
function loginHandler(player, username, password, thePlayer) 
    local query = dbQuery (connection, "SELECT * FROM accoutns WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false) and num_affected_rows < 1 then 
        if (logIn(player, account, password) == true) then 
            triggerClientEvent (player, "hideLoginWindow", getRootElement()) 
        else 
            triggerClientEvent (player, "unknownError", getRootElement()) 
        end 
    else 
        triggerClientEvent (player, "loginWrong", getRootElement()) 
    end 
end 
  
function registerHandler(player, username, password) 
    local query = dbQuery(connection, "SELECT * FROM accoutns WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false)  and num_affected_rows > 1 then 
  
        triggerClientEvent(player, "registerTaken", getRootElement()) 
    else 
        account = addAccount(username, password) 
        dbExec(connection,"INSERT INTO accounts VALUES(?, ?)", username, password) 
        if (logIn(player, account, password) == true) then 
            
            triggerClientEvent(player, "hideLoginWindow", getRootElement()) 
      
    else 
            triggerClientEvent(player, "unknownError", getRootElement()) 
        end 
    end 
end 
  
  
local camtable = { 
{3074.04468, -3041.53442, 69.20741, 3099.33130, -3110.32227, 42.89851}, 
{-305.43381, 1349.10864, 84.20972, -329.04495, 1535.28113, 75.56250}, 
{1266.68579, -2064.46631, 85.27428, 1190.04834, -2036.79968, 69.00781}, 
{1543.23889, -2382.14258, 56.26112, 1497.80872, -2309.76709, 25.14184}, 
{-1590.68567, 592.59760, 52.45333, -1522.07080, 674.48999, 52.22255} 
} 
  
  
function setCameraOnPlayerJoin() 
     fadeCamera(source, true, 5) 
setTimer ( fadeCamera, 5000, 1, source, true, 0.5 )     
  
joincampos = math.random(#camtable) 
lx, ly, lz, wx, wy, wz = camtable[joincampos][1], camtable[joincampos][2], camtable[joincampos][3], camtable[joincampos][4], camtable[joincampos][5], camtable[joincampos][6] 
setCameraMatrix(source, lx, ly, lz, wx, wy, wz) 
  
  
end 
addEventHandler("onPlayerJoin", getRootElement(), setCameraOnPlayerJoin) 
  
  
  
  
  
  
  
  
function setCameraRegister(player, thePlayer) 
setTimer( setCameraTarget,1200,1, source ) 
setElementFrozen ( source, false ) 
end 
  
  
function onJoin() 
showChat(source, false) 
setPlayerHudComponentVisible(source, "all", false) 
end 
addEventHandler("onPlayerJoin", root, onJoin) 
  
  
function onSpawn() 
setPlayerHudComponentVisible(source, "all", true) 
showChat(source, true) 
showCursor(source, false) 
end 
addEventHandler("onPlayerSpawn", root, onSpawn) 
  
  
  
addEvent("setCameraRegister", true) 
addEvent("submitChangepw", true) 
addEvent("submitLogin", true) 
addEvent("submitRegister", true) 
addEventHandler("submitChangepw", root, passwordHandler) 
addEventHandler("submitLogin", root, loginHandler) 
addEventHandler("submitRegister", root, registerHandler) 
addEventHandler("setCameraRegister", root, setCameraRegister)

still same error

Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted 
local connection = dbConnect("sqlite", "database.db", "127.0.0.1", "root", "921994") 
  
function createTable() 
    if connection == true then 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `accounts`('usernames', 'password', 'kills', 'deaths', 'headshots', 'money', 'rank', 'class', 'timeplay', 'vip', 'adminlevel')") 
    end 
end 
addEventHandler("onResourceStart", resourceRoot, createTable)

I want create that table not created and use it to another resource use it ty

Link to comment
ozulus Poot-butt

ozulus

Posted
Posted

Seems you didn't get my point. You cant use "127.0.0.1", "root", "921994" in sql. There is no host, no username and no password in SQL. Try that.

  
local connection = false 
  
function createTable() 
    connection = dbConnect("sqlite", "database.db") 
    if connection then 
       dbQuery(connection, "CREATE TABLE IF NOT EXISTS `accounts`('usernames', 'password', 'kills', 'deaths', 'headshots', 'money', 'rank', 'class', 'timeplay', 'vip', 'adminlevel')") 
    end 
end 
addEventHandler("onResourceStart", resourceRoot, createTable)

If you want to use that connection in other resource, create a export for getting connection in this resource like that:

function getSQLConnection() 
      return connection 
end

We must add this export function to the meta.xml

function="getSQLConnection" type="server"/>

Now, we can use this function in other resource.

  
connection = exports.RESOURCENAME:getSQLConnection() 
if connection then 
    dbExec(connection, ...)  
    dbQuery(connection, ...) 
    -- and so on... 
end 
 

Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted

guy i want ask a question when i want insert the table two values for example username password need like this?

local connection = exports.database:getSQLConnection() 
  
function registerHandler(thePlayer, username, password) 
    local query = dbQuery(connection, "SELECT * FROM accounts WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false) and num_affected_rows == 1 then 
        triggerClientEvent(thePlayer, "registerTaken", root) 
   else 
        account = addAccount(username, password) 
        dbExec(connection, "INSERT INTO accounts VALUES(?, ?) (username, password)", username, password)

Link to comment
KariiiM Lil' G

KariiiM

Posted
Posted 
local connection = exports.database:getSQLConnection() 
  
function registerHandler(thePlayer, username, password) 
    local query = dbQuery(connection, "SELECT * FROM accounts WHERE username=? and password=?", username, password) 
    local result, num_affected_rows = dbPoll(query, -1) 
    local account = getAccount(username, password) 
    if (account ~= false) and num_affected_rows == 1 then 
        triggerClientEvent(thePlayer, "registerTaken", root) 
   else 
        account = addAccount(username, password) 
        dbExec(connection, "INSERT INTO accounts (username, password) VALUES ('"..tostring(username).."','"..tostring(password).."')") 
     

Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted 
local connection = false 
  
function createTable() 
    connection = dbConnect("sqlite", "database.db") 
    if connection then 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `accounts`('username', 'password', 'name', 'serial', 'kills', 'deaths', 'headshots', 'money', 'rank', 'exp', 'class', 'playtime', 'vip', 'adminlevel')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `punishments`('warn', 'playerSource', 'by', 'reason', 'jail', 'jailtime', 'playerSource', 'by', 'reason', 'kick', 'playerSource', 'by', 'reason', 'ban', 'bantime', 'username', 'serial', 'by', 'reason', 'mute', 'mutetime', 'playerSource', 'by', 'reason')") 
       dbExec(connection, "INSERT INTO accounts (username, password, kills) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO accounts (name, serial) VALUES ('"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO accounts (deaths, headshots, money) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO accounts (rank, exp, class) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO accounts (playtime, vip, adminlevel) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (warn, playerSource, by) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (reason, jail, jailtime) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (playerSource, by, reason) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (kick, playerSource, by) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (reason, ban, bantime) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (username, serial, by) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (reason, mute, mutetime) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
       dbExec(connection, "INSERT INTO punishments (playerSource, by, reason) VALUES ('"..nil.."','"..nil.."','"..nil.."')") 
    end 
end 
addEventHandler("onResourceStart", replayerSourceRoot, createTable) 
  
function getSQLConnection() 
    return connection 
end

error line 8 attempt to concatenate a nil value

Link to comment
Addlibs Hard-@ss

Addlibs

Posted
Posted

You can't concatenate a nil with a string (e.g.

"Some string" .. nil
).

If you want to insert a nil value into a table, use the NULL as value (don't escape it or place it in quotation marks within the actual SQL statement or it'll be interpreted as TEXT "NULL" not a NULL value)

E.g 

dbExec(connection, "INSERT INTO accounts (username, password, kills) VALUES (NULL, NULL, NULL)")

Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted 
local connection = false 
  
function createTable() 
    connection = dbConnect("sqlite", "database.db") 
    if connection then 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `accounts`('username', 'password', 'name', 'serial', 'kills', 'deaths', 'headshots', 'money', 'rank', 'exp', 'class', 'playtime', 'vip', 'adminlevel')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `punishments`('warn', 'playername', 'by', 'reason', 'jail', 'playername', 'jailtime', 'by', 'reason', 'kick', 'playername', 'by', 'reason', 'ban', 'userplayername', 'serial', 'bantime',  'by', 'reason', 'mute', 'playername', 'mutetime', 'by', 'reason')") 
       dbExec(connection, "INSERT INTO accounts (username, password, kills) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO accounts (name, serial) VALUES (NULL, NULL)") 
       dbExec(connection, "INSERT INTO accounts (deaths, headshots, money) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO accounts (rank, exp, class) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO accounts (playtime, vip, adminlevel) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (warn, playername, by) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (reason, jail, jailtime) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (playername, by, reason) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (kick, playername, by) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (reason, ban, bantime) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (userplayername, serial, by) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (reason, mute, mutetime) VALUES (NULL, NULL, NULL)") 
       dbExec(connection, "INSERT INTO punishments (playername, by, reason) VALUES (NULL, NULL, NULL)") 
    end 
end 
addEventHandler("onResourceStart", resourceRoot, createTable) 
  
function getSQLConnection() 
    return connection 
end

Error line 7 dupilicate column name : playername

Link to comment
  • Moderators
Citizen Jacker

Citizen

Posted
  • Moderators
Posted

You don't really now what you are doing right ?

The error message can't be clear enough ... At line 7 you are trying to create a MySQL table with multiple "username" columns. Same with the following columns "by" and "reason".

You can't create 2 columns with the exact same name.

You have 2 solutions:

The first one is to use different tables for each punishment type:

function createTable() 
    connection = dbConnect("sqlite", "database.db") 
    if connection then 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `accounts`('username', 'password', 'name', 'serial', 'kills', 'deaths', 'headshots', 'money', 'rank', 'exp', 'class', 'playtime', 'vip', 'adminlevel')") 
        
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `warns`('playername', 'by', 'reason')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `jails`('playername', 'jailtime', 'by', 'reason')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `kicks`('playername', 'by', 'reason')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `bans`('playername', 'serial', 'bantime',  'by', 'reason')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `mutes`('playername', 'mutetime', 'by', 'reason')") 
    end 
end 
addEventHandler("onResourceStart", resourceRoot, createTable)

The second one would be to use a column named "type" to know what kind of punishement it is so you can share the same columns:

function createTable() 
    connection = dbConnect("sqlite", "database.db") 
    if connection then 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `accounts`('username', 'password', 'name', 'serial', 'kills', 'deaths', 'headshots', 'money', 'rank', 'exp', 'class', 'playtime', 'vip', 'adminlevel')") 
       dbExec(connection, "CREATE TABLE IF NOT EXISTS `punishments`('type', 'playername', 'serial', 'by', 'reason', 'starttime', 'finishtime')") 
    end 
end 
addEventHandler("onResourceStart", resourceRoot, createTable)

So in the "type" column, you will use the following values:

  • For a warn => 1
  • For a mute => 2
  • For a jail => 3
  • For a kick => 4
  • For a ban => 5

The "starttime" column will contain the timestamp when the pusnishment occured ( getRealTime().timestamp to get the current timestamp)

and the "finishtime" will contain the timestamp when the punishment will be over ( getRealTime().timestamp + 7 * 24 * 3600 to calculate the timestamp it will be in 7 days from now)

Here is how your table can look like if you apply some punishments:

typeplayernameserialbyreasonstarttimefinishtime

1

Citizen

123E456C4

Dimos7

Do not spam

1457714436

NULL

2

Citizen

123E456C4

Dimos7

Spamming too much

1457714436

1457714456

3

Citizen

123E456C4

Dimos7

Killing for no reason

1457714436

1457714456

4

Citizen

123E456C4

Dimos7

Respect others

1457714436

NULL

5

Citizen

123E456C4

Dimos7

MultiAccount is forbidden

1457714436

1457714456

5

Citizen

123E456C4

Dimos7

Aimbot

1457714436

NULL

Hope it helps.

Link to comment
Dimos7 Loc

Dimos7

Posted
  • Author
Posted 
local connection = exports.databse:getSQLConnection() 
  
antispam= { } 
  
function unmutePlayer(thePlayer) 
      setPlayerMuted(thePlayer, false) 
end 
  
function playerSpamChat() 
   local muted = getElementData(source, "muted") or 0  
   if antispam[source] == getTickCount() == 3000  and antispam[source] == getTickCount <= 3000 then 
      setPlayerMuted(source, true) 
      theTimer = setTimer(unmutePlayer, 10000, 1, source) 
      if isTimer(theTimer) then 
        killTimer(theTimer) 
      end 
      outputChatBox(getPlayerName(source).." has been muted for 1 minute by antispam for spam", root, 255, 0, 0) 
      outputServerLog(getPlayerName(source).." has been muted for 1 minute by antispam for spam") 
      setElementData(source, "muted", muted + 1) 
      dbExec(connection, "UPDATE punishments SET type=? and name=? and serial=? and by=? and reason=? and starttime=? and finishtime=?","mute", getPlayerName(source), getPlayerSerial(source), "antispam", "spam", getRealTime().timestamp, getRealTime().timestamp +1*60) 
   end 
end 
addEventHandler("onPlayerChat", root, playerSpamChat)

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
Go to topic listing

  • Recently Browsing   0 members

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