Need help with MySQL

[Enkanet]

Hello there!

I have a DB and a table in it called accounts. And in the structure I have those:

id(AUTO_INCREMENT), username, password, etc.... and also "job". I want to do like, when you click to a button it should set that Job value in db to 3. Here's my server side:

mysql = exports.mysql 
function policeTeam() 
    setElementData(source,"Job","3") 
    setPlayerTeam(source, getTeamFromName("Law")) 
    giveWeapon(source, 24, 100) 
end 
addEvent("setPoliceTeam", true) 
addEventHandler("setPoliceTeam", getRootElement(), policeTeam) 
  
function getMysql() 
    result = "SELECT * FROM accounts WHERE id=?",accountID 
    setElementData (source,"Job",result[1].Job) 
end 
addEventHandler("onPlayerJoin", getRootElement(), getMysql) 
  
function setMysql() 
    result = mysql:query_free("UPDATE accounts SET job=? WHERE id=?",getElementData (source,"Job"),accountID) 
end 
addEventHandler("onPlayerQuit", getRootElement(), setMysql) 

in client side I only trigger the setPoliceTeam event with the code. So, where's the error and what shall I do? There're no errors in debugscript but it doesn't change the job value in db.

[botder]

result = "SELECT * FROM accounts WHERE id=?",accountID 
setElementData (source,"Job",result[1].Job) 

You didn't even use 'mysql_query' to grab the result.

[Enkanet]

result = "SELECT * FROM accounts WHERE id=?",accountID 
setElementData (source,"Job",result[1].Job) 

You didn't even use 'mysql_query' to grab the result.

yes I did notice it and I changed my code. still now working. Here's the code:

function policeTeam() 
    setElementData(source,"Job","3") 
    setPlayerTeam(source, getTeamFromName("Law")) 
    giveWeapon(source, 24, 100) 
end 
addEvent("setPoliceTeam", true) 
addEventHandler("setPoliceTeam", getRootElement(), policeTeam) 
  
function getMysql() 
    query = dbQuery(connection, "SELECT * FROM accounts WHERE id='"..accountID.."' ") 
    result, affectedrows, lastid = dbPoll(query, -1) 
    setElementData (source,"Job",result[1].Job) 
end 
addEventHandler("onPlayerJoin", getRootElement(), getMysql) 
  
function setMysql(accountID) 
    query = dbQuery(connection, "UPDATE accounts SET job = '"..getElementData(source, "Job").."' WHERE id = '"..accountID.."' ") 
    result, afectedrows, lastid = dbPoll(query, -1) 
end 
addEventHandler("onPlayerQuit", getRootElement(), setMysql) 
  
function connectToDB() 
    connection = dbConnect("mysql", "dbname=cloudrpg;host=127.0.0.1;port=3306", "root")  
end 
addEventHandler("onPlayerJoin", getRootElement(), connectToDB) 

in my opinion the problem is with my loginpanel. I am rewriting it because debugscript shows no error and still the script is not working.

[Enkanet]

Now I changed the script a lot and I started using mta_mysql.dll and libmysql.dll modules. Here's my new code which is not working:

local mysql = exports.mysql 
function policeTeam(accountID) 
    local accountID = tonumber(getElementData(source, "account:id")) 
    setElementData(source,"Job", 3) 
    setPlayerTeam(source, getTeamFromName("Law")) 
    giveWeapon(source, 24, 100) 
end 
addEvent("setPoliceTeam", true) 
addEventHandler("setPoliceTeam", getRootElement(), policeTeam) 
  
function getMysql(accountID) 
    local accountID = tonumber(getElementData(source, "account:id")) 
    local result = mysql:query("SELECT job FROM accounts WHERE id='" .. accountID .. "'") 
    setElementData (source,"Job",result[1].Job) 
end 
addEventHandler("onPlayerJoin", getRootElement(), getMysql) 
  
function setMysql(accountID) 
    local jobID = getElementData(source, "Job") 
    local accountID = tonumber(getElementData(source, "account:id")) 
    local result = mysql:query_free("UPDATE `accounts` SET `job`='".. jobID .."' WHERE `id`='".. accountID .."'") 
end 
--addEventHandler("onPlayerQuit", getRootElement(), setMysql) 
addCommandHandler("mysqlm", setMysql) 

[botder]

WHERE id='5' 

You query is probably comparing a number with a string, example:

WHERE 5='5' 

[Enkanet]

WHERE id='5' 

You query is probably comparing a number with a string, example:

WHERE 5='5' 

So here's my code:

local mysql = exports.mysql 
local accountID = getElementData(source, "account:id") 
function policeTeam() 
    setElementData(source,"Job", "3") 
    setPlayerTeam(source, getTeamFromName("Law")) 
    giveWeapon(source, 24, 100) 
end 
addEvent("setPoliceTeam", true) 
addEventHandler("setPoliceTeam", getRootElement(), policeTeam) 
  
function getMysql(accountID) 
    local result = mysql:query("SELECT job FROM accounts WHERE id='" .. accountID .. "'") 
    setElementData(source,"Job",result[i]) 
end 
--addEventHandler("onPlayerJoin", getRootElement(), getMysql) 
addCommandHandler("getmysql", getMysql) 
  
function setMysql(accountID) 
    local jobID = getElementData(source, "Job") 
    local result = mysql:query_free("UPDATE `accounts` SET `job`='".. jobID .."' WHERE `id`='".. accountID .."'") 
end 
--addEventHandler("onPlayerQuit", getRootElement(), setMysql) 
addCommandHandler("mysqlm", setMysql) 

Note: I added a command instead of using eventhandler to test without reconnecting. Also, do you have skype? So we can talk better than here.

[botder]

It should be pretty easy to find my skype.

Anyway, here is your fail, maybe:

local result = mysql:query("SELECT job FROM accounts WHERE id='" .. accountID .. "'") 

SELECT job FROM accounts WHERE id='5' 

Your `id` column should be an INTEGER type and you compare it with a STRING type. You have to remove the single quotation mark '.

SELECT job FROM accounts WHERE id=5 

[Enkanet]

I forgot to say,

local accountID = getElementData(source, "account:id") in this code debugscript says warning: bad argument @ getElementData Expected element at argument 1, got nil

and

local result = mysql:query_free("UPDATE `accounts` SET `job`='".. jobID .."' WHERE `id`=".. accountID .."")

here it says error: attempt to concatenate local 'accountID' (a userdata value)

[botder]

local accountID = getElementData(source, "account:id") 

Variable source does not exists in that scope (= nil).

Expected element at argument 1, got nil

function getMysql(accountID) 
    -- ... 
end 
addCommandHandler("mysqlm", setMysql) 

The command handler function, getMysql, has always 2 parameters at the front:

function command_handler( element player, string commandName, ... args ) 

That means, you have to replace the parameter accountID with player and

add a line in that function, which creates the accountID variable with the accountID from the player.

function getMysql(player) 
    local accountID = getElementData(player, "account:id") 
    if not accountID then 
        return outputChatBox("You have to login to use this command", player) 
    end 
    -- ... 
end 

[Enkanet]

thanks mate! Worked now! Also I added you on skype.